このような統計を収集するために、完了した作業に関する情報を収集し、最も詳細な説明も提供する小さなスクリプトを作成しました
プロシージャの前後のインデックスの状態について。
しかし、単純なものから始めて、このデータを保存するテーブルを作成します（サーバーデータベースを処理するときに使用するテーブルを置く別のデータベースを作成しました）。
| コラム | 種類 | 解説 |
|---|---|---|
| proc_id | int | 識別のための手順のシーケンス番号 |
| start_time | 日時 | ALTER INDEXクエリの開始 |
| end_time | 日時 | ALTER INDEXリクエストの完了 |
| database_id | 小さい | DB ID |
| object_id | Int | テーブルID |
| テーブル名 | varchar（50） | テーブル名 |
| index_id | Int | インデックスID |
| index_name | varchar（50） | インデックス名 |
| avg_frag_percent_before | 浮く | ALTER INDEXの前のインデックスの断片化率 |
| fragment_count_before | ビッグ | 最適化前のフラグメントの数 |
| pages_count_before | ビッグ | 最適化前のインデックスページの数 |
| fill_factor | tinyint | インデックスページの塗りつぶしレベル |
| partition_num | int | セクション番号 |
| avg_frag_percent_after | 浮く | ALTER INDEX実行後のインデックスの断片化率 |
| fragment_count_after | ビッグ | デフラグ後のフラグメントの数 |
| pages_count_after | ビッグ | 最適化後のインデックスページの数 |
| 行動 | varchar（10） | 実行されたアクション |
最適化手順全体がこのテーブルからデータを取得するため、データを入力する必要があります。
DECLARE @currentProcID INT –- –- , SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic -- INSERT INTO dba_tasks.dbo.index_defrag_statistic ( proc_id, database_id, [object_id], table_name, index_id, index_name, avg_frag_percent_before, fragment_count_before, pages_count_before, fill_factor, partition_num) SELECT @currentProcID, dm.database_id, dm.[object_id], tbl.name, dm.index_id, idx.name, dm.avg_fragmentation_in_percent, dm.fragment_count, dm.page_count, idx.fill_factor, dm.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id WHERE page_count > 8 AND avg_fragmentation_in_percent > 10 AND dm.index_id > 0
サンプリング条件：
page_count> 8-少数のページでインデックスを再構築することは意味がないと思います。 それは改善されず、手順に費やされる時間は非常に貴重です。特にベースが24時間体制で動作し、常に高い負荷がかかっている場合はそうです。 （発言が未記入の場合 、バーを8ページに上げました）
avg_fragmentation_in_percent> 10-これは非常に主観的な数値でもあります。事実上、断片化が10％以下の場合はインデックスに触れないことをお勧めするすべてのドキュメントで、状況に応じて変更します。
dm.index_id> 0-0は束です
テーブルがいっぱいになると、どのインデックスを処理する必要があるかがわかります。
対処しましょう：
-- DECLARE @partitioncount INT -- DECLARE @action VARCHAR(10) --, DECLARE @start_time DATETIME -- ALTER INDEX DECLARE @end_time DATETIME -- ALTER INDEX -- DECLARE @object_id INT DECLARE @index_id INT DECLARE @tableName VARCHAR(250) DECLARE @indexName VARCHAR(250) DECLARE @defrag FLOAT DECLARE @partition_num INT DECLARE @fill_factor INT -- , , MAX, , , . DECLARE @sql NVARCHAR(MAX) -- DECLARE defragCur CURSOR FOR SELECT [object_id], index_id, table_name, index_name, avg_frag_percent_before, fill_factor, partition_num FROM dba_tasks.dbo.index_defrag_statistic WHERE proc_id = @currentProcID ORDER BY [object_id], index_id DESC -- OPEN defragCur FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num WHILE @@FETCH_STATUS=0 BEGIN SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @object_id AND index_id = @index_id; -- , , , , IF (@fill_factor != 80) BEGIN @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)' SET @action = 'rebuild80' END ELSE BEGIN -- , MS IF (@defrag > 30) -- 30%, REBUILD BEGIN SET @sql = @sql + N' REBUILD' SET @action = 'rebuild' END ELSE -- REORGINIZE BEGIN SET @sql = @sql + N' REORGANIZE' SET @action = 'reorginize' END END -- IF @partitioncount > 1 SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5)) print @sql -- -- SET @start_time = GETDATE() EXEC sp_executesql @sql -- SET @end_time = GETDATE() -- UPDATE dba_tasks.dbo.index_defrag_statistic SET start_time = @start_time, end_time = @end_time, [action] = @action WHERE proc_id = @currentProcID AND [object_id] = @object_id AND index_id = @index_id FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num END CLOSE defragCur DEALLOCATE defragCur
そして最後に、最適化手順の後、インデックスに関する情報を収集します。
UPDATE dba SET dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent, dba.fragment_count_after = dm.fragment_count, dba.pages_count_after = dm.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN dba_tasks.dbo.index_defrag_statistic dba ON dm.[object_id] = dba.[object_id] AND dm.index_id = dba.index_id WHERE dba.proc_id = @currentProcID AND dm.index_id > 0
このようなスクリプトを実行すると、多くの有用な情報を取得してカウントできます。 たとえば、すべてのインデックスのサービス時間とそれぞれ個別に。 これがインデックスのサイズにどのように関係するかを理解し、この操作の有効性を確認してください。 このような情報を数回収集すると、手順をわずかに変更できます。これにより、一部のインデックスがより高速に断片化されます。 この場合、より頻繁に保守する必要があります。 受け取った情報をどのように使用するかは、自分で決めてください。 私の場合、そのような手順をそれぞれ分析した後、状況によって必要に応じてサービスプランを変更します。 私の基地は、24時間体制で重い負荷の下で稼働しています。 したがって、すべてのインデックスを常に再構築して、サーバーのパフォーマンスを1〜2時間低下させることはできません。 データベースが24時間動作する場合、そのようなことを実行するには、 リソースガバナーを構成する必要があります。
また、インターネットには詳細なスクリプトがありますが、注意してください。それらの多くは古いコマンドを使用しています。
私が使用するシステム表現の詳細は、msdnにあります。
sys.sysindexes
sys.tables
sys.dm_db_index_physical_stats