データベース索引の再構築
BigFix Inventory が円滑に動作するためには、データベース・インデックスの再構築および統計の更新が必須です。UI 応答やデータのインポートで遅延が発生する場合は、ハード・ディスクのデフラグを実行し、統計を最新の状態にしてください。
このタスクについて
バージョン 10.0.4 以降、新規インストールおよびアップグレードの場合、「<InventoryDB> 完全なデータベース・インデックス再編成」という名前の MS SQL データベースにジョブが含まれています。このジョブは、必要なインデックス保守を行います。ジョブは以下の基準に従います。
手順
- スクリプトは毎日、現地時間の午前 4 時に実行されます。
- データ・インポート (スケジュールされたインポートまたは手動インポート) が実行されている場合、ジョブはインポートが完了するのを待ってから、インデックスの再構築を試行します。ジョブは、30 分間隔で 16 回実行を試みます。待機の合計時間は 8 時間です。インポートが 8 時間後に完了しない場合、ジョブは状況ログにエラー・メッセージを残し、キューを退出します。その場合、インデックスは翌日のスケジュールされた時刻に再構築されます。
- 「<InventoryDB> データベース・インデックスの完全再編成」という名前のインデックス再構築ジョブが既に存在する場合、スクリプトはデータを上書きせず、既存の構成が保持されます。
- BigFix Inventory インデックス再構築ジョブを実行するには、SQLサーバー・エージェントを構成して実行する必要があります。エージェントは、オフになっている場合があります。手動でオンにする必要があります。SQL サーバー・エージェントの構成方法と始動方法については、「SQL サーバー・エージェントの構成」と「SQL サーバー・エージェント・サービスの開始、停止、一時停止」をそれぞれ参照してください。
- 障害が発生した場合は SQL サーバー・エージェントを自動的に再始動するように設定することをお勧めします。詳しくは、「SQL サーバー・エージェントの自動再始動」を参照してください。
次のタスク
以下の例は、インデックスを再構築して統計を更新する簡単な方法を示しています。ジョブによって、フラグメント化に関係なく、すべてのインデックスが再構築されます。このスクリプトでは、標準のデータベース名を TEMADB として使用しています。
USE TEMADB
GO
IF EXISTS (SELECT
*
FROM dbo.imports
WHERE success IS NULL)
BEGIN
PRINT N'CANNOT RUN index rebuild. BFI import is running!'
PRINT N'Wait until BFI import finishes'
END
ELSE
BEGIN
DECLARE table_cursor CURSOR FOR
SELECT
table_schema,
table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
OPEN table_cursor
DECLARE @tableName sysname
DECLARE @tableSchema sysname
FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
WHILE @@fetch_status != -1
BEGIN
PRINT N'START alter index all on ' + @tableSchema
+ N'.' + @tableName + N' rebuild';
EXECUTE (N'alter index all on ' + @tableSchema + N'.'
+ @tableName
+
N' rebuild')
PRINT N'END alter index all on ' + @tableSchema
+ N'.' + @tableName + N' rebuild';
FETCH NEXT FROM table_cursor INTO @tableSchema,
@tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
PRINT N'START sp_updatestats';
EXECUTE sp_updatestats
PRINT N'END sp_updatestats';
END
GOフル・データベース・インデックス再編成ジョブの作成
このタスクについて
2 つの異なるシナリオでフル・データベース・インデックス再編成ジョブを作成する手順を以下に示します。
-
- 既存のフル・データベース・インデックス再編成ジョブを削除する
-
- DB に接続します。
- 以下の SQL 照会を実行します。
- 'DATABASE_NAME_TO_REPLACE' を実際の BigFix Inventory データベース名に置き換えます。
- 照会を実行します。
EXEC msdb.dbo.sp_delete_job @job_name=N'DATABASE_NAME_TO_REPLACE Full Database Index Reorganization', @delete_unused_schedule=1
- SQL Server Agent に移動し、 Jobs セクションを展開して、Database Name Full Database Index Reorganization ジョブが存在しないことを確認します。
-
- ジョブが存在しない場合は、「フル・データベース・インデックス再編成」ジョブを作成します
-
- DB に接続します。
- 以下の SQL 照会を実行します。
- 'DATABASE_NAME_TO_REPLACE' を実際の BigFix Inventory データベース名に置き換えます。
- 照会を実行します。
BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DATABASE_NAME_TO_REPLACE Full Database Index Reorganization', @enabled=1, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check the BFI Import', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=16, @retry_interval=30, @os_run_priority=0, @subsystem=N'TSQL', @command=N' IF EXISTS (SELECT top(1) 1 FROM dbo.imports WHERE success IS NULL) BEGIN RAISERROR(''BFI Data import is in progress, aborting query'',11,1); END ELSE PRINT(''Import has completed, moving to next step'') GO', @database_name=N'DATABASE_NAME_TO_REPLACE', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reorganize all indexes', @step_id=2, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' ----------Common procedure to print Error details------ create procedure #printErrorDetails as begin Declare @ErrorMessage nvarchar(4000) Declare @ErrorSeverity int Declare @ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() PRINT ''--------Error state: '' + CONVERT( VARCHAR(12), @ErrorState, 121) + ''. Error severity: '' + CONVERT( VARCHAR(12), @ErrorSeverity, 121) + ''. Error message: '' + @ErrorMessage end GO set nocount on -----------Global variable declarations-------------------- Declare @MIN_REORG_FRAGMENTATION_LEVEL INT = 5 Declare @MIN_REBUILD_FRAGMENTATION_LEVEL INT = 30 Declare @PAGE_COUNT_THRESHOLD INT = 9 Declare @PAGE_COUNT_REORG_THRESHOLD INT = 99 Declare @REBUILD_FILL_FACTOR INT = 90 Declare @REBUILD_MAX_DOP INT = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' Begin reindexing script for '' + DB_NAME() ----------Table to Hold Fragmented Objects---- create table #Reorganize (SchemaName varchar(50),tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer) create table #RebuildOffline (SchemaName varchar(50), tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer) create table #RebuildOnline (SchemaName varchar(50), tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer) ----------Table to hold the table names where AllowPageLocks is disabled---- create table #Allow (tablen varchar(100)) -----------For SQL Server Enterprise Edition online indexing is supported--------------------------------------------------------------- if CHARINDEX(''Enterprise'', convert(nvarchar(64),(select serverproperty(''Edition''))) ) > 0 begin -----------Inserting All fragmented table where fragmentation level is between @MIN_REORG_FRAGMENTATION_LEVEL to @MIN_REBUILD_FRAGMENTATION_LEVEL and having ALLOW_PAGE_LOCKS = ON in temptable---- insert into #Reorganize(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and i.index_id > 0 and allow_page_locks = 1 and avg_fragmentation_in_percent between @MIN_REORG_FRAGMENTATION_LEVEL and @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD -----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (online for non clustered indexes)----- insert into #RebuildOnline(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and i.index_id > 1 and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) ) -----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (online for clustered indexes NOT containing text, ntext or image fields)--- insert into #RebuildOnline(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and i.index_id = 1 and o.name not in ( select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where data_type in (''TEXT'', ''NTEXT'',''IMAGE'') ) and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) ) -----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (offline for clustered indexes containing text, ntext or image fields)-------- insert into #RebuildOffline(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and i.index_id = 1 and o.name in ( select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where data_type in (''TEXT'', ''NTEXT'',''IMAGE'') ) and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) ) end else begin -----------For SQL Server Enterprise editions other than Enterprise the online indexing is not supported, so rebuild all indexes offline------------- ----------Inserting into Reorganize temp table----------- insert into #Reorganize(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and i.index_id > 0 and avg_fragmentation_in_percent between @MIN_REORG_FRAGMENTATION_LEVEL and @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD -----------Inserting into Rebuildoffline temp table----------- insert into #RebuildOffline(SchemaName,tablename,Indexname,Fragmentation,Pages) select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on o.schema_id= s.schema_id left outer join sys.indexes i on o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips on i.object_id=ips.object_id and i.index_id=ips.index_id where o.type=''U'' and avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ------------Determining the tablenames where AllowPageLocks is disabled--------- insert into #Allow(tablen) select distinct t.name from sys.tables t join sys.indexes i on t.object_id = i.object_id where allow_page_locks =0 end -----------Variable declarations-------------------- Declare @cmd varchar(1000) Declare @Iname varchar(250) Declare @Jname varchar(250) Declare @sname varchar(150) Declare @tname varchar(150) Declare @frag float Declare @pgs integer Declare @index varchar(1000) Declare @managePageLocks bit Declare @bypassReorg bit Declare @EXIT_CODE bit = 0 -----------Manage Locking----------- set LOCK_TIMEOUT 5000 set DEADLOCK_PRIORITY LOW -----------Cursor for reorganize--------------------- Declare db_reindex CURSOR for select Indexname,SchemaName,tablename, Fragmentation, Pages from #Reorganize OPEN db_reindex FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs WHILE @@FETCH_STATUS = 0 BEGIN set @managePageLocks = 0 set @bypassReorg = 0 set @Jname= @sname + ''.''+ @tname if exists (select 1 from #Allow where tablen = @tname) begin set @managePageLocks = 1 set @index = @Jname + ''.'' + @Iname PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Allowing page locks for '' + @index set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' SET (ALLOW_PAGE_LOCKS = ON)'' BEGIN TRY execute (@cmd) END TRY BEGIN CATCH set @bypassReorg = 1 set @EXIT_CODE = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during ALTER INDEX for allowing page locks. By-passing reorg for '' + @index EXEC #printErrorDetails END CATCH end if ( @bypassReorg = 0 ) begin set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' reorganize with (LOB_COMPACTION = ON)'' PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' Reorganize index '' + @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages'' BEGIN TRY execute (@cmd) END TRY BEGIN CATCH set @EXIT_CODE = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during reorganize. Continuing.'' EXEC #printErrorDetails END CATCH if ( @managePageLocks = 1 ) begin PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Disallowing page locks for '' + @Jname + ''.'' + @Iname set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' SET (ALLOW_PAGE_LOCKS = OFF)'' BEGIN TRY execute (@cmd) END TRY BEGIN CATCH set @EXIT_CODE = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during ALTER INDEX for disallowing page locks. Continuing.'' EXEC #printErrorDetails END CATCH end end FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs END CLOSE db_reindex DEALLOCATE db_reindex ------------Cursor For Rebuild Online------------ Declare db_reindex CURSOR for select Indexname,SchemaName,tablename, Fragmentation, Pages from #RebuildOnline OPEN db_reindex FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs WHILE @@FETCH_STATUS = 0 BEGIN set @Jname= @sname + ''.''+ @tname set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' rebuild with (ONLINE = ON, MAXDOP = '' + CONVERT( VARCHAR(4), @REBUILD_MAX_DOP, 121) + '', PAD_INDEX = ON, FILLFACTOR = '' + CONVERT( VARCHAR(4), @REBUILD_FILL_FACTOR, 121) + '')'' PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' Rebuild index online (with MAXDOP = '' + CONVERT( VARCHAR(4), @REBUILD_MAX_DOP, 121) + '') '' + @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages'' BEGIN TRY execute (@cmd) END TRY BEGIN CATCH set @EXIT_CODE = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during rebuild. Continuing.'' EXEC #printErrorDetails END CATCH FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs END CLOSE db_reindex DEALLOCATE db_reindex ------------Cursor For Rebuild Offline------------ Declare db_reindex CURSOR for select Indexname,SchemaName,tablename, Fragmentation, Pages from #RebuildOffline OPEN db_reindex FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs WHILE @@FETCH_STATUS = 0 BEGIN set @Jname= @sname + ''.''+ @tname set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' rebuild with (PAD_INDEX = ON, FILLFACTOR = '' + CONVERT( VARCHAR(4), @REBUILD_FILL_FACTOR, 121) + '')'' PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' Rebuild index offline '' + @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages'' BEGIN TRY execute (@cmd) END TRY BEGIN CATCH set @EXIT_CODE = 1 PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during rebuild. Continuing.'' EXEC #printErrorDetails END CATCH FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs END CLOSE db_reindex DEALLOCATE db_reindex PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' End reindexing script for '' + DB_NAME() if ( @EXIT_CODE = 1 ) begin PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' Reindexing script completed but an error occurred and one of the steps failed!'' end ', @database_name=N'DATABASE_NAME_TO_REPLACE', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Runs daily at 04:00 AM', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20210601, @active_end_date=99991231, @active_start_time=40000, @active_end_time=235959, @schedule_uid=N'4e4d7b59-fd7f-4772-a7c5-a5565443f9d4' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
- SQL Server Agent に移動し、 Jobs セクションを展開して、Database Name Full Database Index Reorganization ジョブが存在することを確認します。