統計コレクションMS SQL 2008 R2によるインデックスの最適化

DBAが新しいデータベースを展開した後に直面する最初のタスクの1つは、メンテナンスの計画を立てることです。 多くの場合、保守計画にはインデックスの最適化タスクが含まれます。 デフラグが日曜日から月曜日の夜に行われただけでなく、どのように行われたか、どのくらい実行されたか、どのインデックスが再構築され、デフラグ後に残った状態にあるかを知っているとき、私はそれが好きです。

このような統計を収集するために、完了した作業に関する情報を収集し、最も詳細な説明も提供する小さなスクリプトを作成しました
プロシージャの前後のインデックスの状態について。

しかし、単純なものから始めて、このデータを保存するテーブルを作成します（サーバーデータベースを処理するときに使用するテーブルを置く別のデータベースを作成しました）。

コラム	種類	解説
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