SQL Server

インデックスの状態を診断する新しいツール

Randy Dyess

概要:

• インデックスの断片化
• インデックスの使用
• インデックス操作の状況

この記事で使用しているコードのダウンロード: DyessSQLIndex2007_03.exe (151KB)

通常、SQL Server のパフォーマンス チューニングでは、データベース内のインデックスの微調整を最優先またはそれに近い優先順位で実行します。SQL Server クエリ オプティマイザで、クエリの実行中にインデックスを正しく利用するためには、有効なインデックスを作成することだけでなく、インデックスの状態にも注意を払う必要があります。

SQL Server™ 2005 で導入された一連の動的管理ビュー (DMV) および動的管理関数 (DMF) は、データベース管理者がインデックスの有効性を確認したり、パフォーマンスの問題を検出したりするのに役立ちます。

DMV と DMF を使用すると、サーバーの状態を調査して、サーバー インスタンスの状態やパフォーマンスの監視、および問題の診断に役立つ情報を取得することができます。以前のバージョンの SQL Server でデータベース管理を行っていた皆さんは、DMV と DMF が DBCC コマンドを置き換える存在であることにお気付きでしょう。DMV と DMF では、特定のシステム ストアド プロシージャを実行したり、多数のシステム テーブルのクエリを送信したり、SQL Profiler を使用してイベントをキャプチャしたりできます。

インデックスが正常に機能しているかどうかを調べるには、sys.dm_db_index_physical_stats、sys.dm_db_index_usage_stats、sys.dm_db_index_operational_stats の 3 つの DMV および DMF を使用すると便利です。これらの DMV および DMF により、インデックスの入出力とロックのパターンを確認し、クエリ オプティマイザがデータベース内で不要な競合を発生させない形でインデックスを利用しているかどうかを判断できます。

インデックスの断片化

DMF の sys.dm_db_index_physical_stats は、DBCC SHOWCONTIG ステートメントの代替機能であり、インデックスの断片化の状態を示します。ただし、DBCC SHOWCONTIG がインデックスを含むテーブルに共有ロック (S) を設定するのに対して、sys.dm_db_index_physical_stats はインテント共有ロック (IS) を設定するだけなので、関数実行中のテーブルのブロックが大幅に低減されます。

sys.dm_db_index_physical_stats を使用してインデックスの断片化を判定するには、関数の実行後に出力される 3 列のデータを確認します。インデックスの論理的な断片化 (ヒープのエクステントの断片化) は、avg_fragmentation 列の値から判断できます。論理的な断片化は、インデックスのリーフ レベルで順序が異なるページの割合で表されます。一方、エクステントの断片化は、インデックスのリーフ レベルで順序が異なるエクステントの割合で表されます。論理的な断片化やエクステントの断片化が発生すると、正しい順序でページを読み取るためにディスク ヘッドの位置をたびたび変更しなければならなくなり、入出力回数とディスク ヘッドの移動回数が増えるため、パフォーマンスが低下するおそれがあります。論理的な断片化もエクステントの断片化も、できる限りゼロに近くなるように工夫してください。

インデックスの内部断片化は、ページのゆとりの割合で表されます。インデックス ページにできるだけゆとりを持たせることはもちろん重要ですが、インデックス ページのゆとりとインデックス ページへの挿入数のバランスをとり、ページ分割数を絶対的な最小値に保つことも重要です。

インデックス ページのゆとりを確認するには、sys.dm_db_index_physical_stats の avg_page_space_used_in_percent 引数を調べます。この値が 100% に近づくように正しく構成するには、ページ分割数を監視しながらインデックスの fillfactor を調整します。インデックスの fillfactor の設定値が大きすぎると、ページ分割数が急増します。インデックスの fillfactor の調整は、時間と手間のかかる作業です。したがって、適切な計画の下に実行する必要があります。なお、ランダムな挿入が行われないインデックスでは、ページ分割数の増加を心配することなく fillfactor を 100 に設定できます。

AdventureWorks.HumanResources.Employee テーブルのすべてのインデックスの断片化のレベルを確認するには、次のようなステートメントを使用します。

SELECT * 
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL 
-- NULL to view all indexes; 
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information

この DMF を使用すると、再構築の必要なインデックス、再編成の必要なインデックス、およびメンテナンスの不要なインデックスを自動的に特定できます。この DMF の avg_page_space_used_in_percent 列と avg_fragmentation_in_percent 列の値は、許容される論理しきい値および密度のしきい値を超えるインデックスの断片化を表します。これらの値を調べると、インデックスに対して実行する処理を決定しやすくなります。

インデックスの状態によっては、図 1 の例で AdventureWorks サンプル データベース内にデータが返されない場合がありますが、この例はその他のデータベースに簡単に適用することができます。

Figure 1 ページのゆとりと断片化の確認

--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10 
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75 
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

クエリの結果をテーブル変数に格納し、このテーブル変数をループして正しい ALTER INDEX ステートメントの動的文字列を構築する作業は、簡単に実行できます (図 2 を参照)。

Figure 2 動的な ALTER INDEX 文字列の構築

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD' 
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

インデックスの使用

sys.dm_db_index_physical_stats は、DBCC SHOWCONTIG ステートメントの代わりに使用してインデックスの状態を確認できる強力な動的管理関数ですが、テーブルに対して実行されるクエリでどのインデックスを使用するのがよいかを特定するというもっと複雑な問題に直面することがよくあります。通常、データベースの開発者や管理者は、クエリの実行中にクエリ オプティマイザが使用すると思われるテーブル上にインデックスを構築します。これまでの SQL Server のリリースでは、これらのインデックスが実際に使用されているかどうかを確認するのは、はるかに困難な作業でした。インデックスを削除してクエリのパフォーマンスが変化するかどうかを確認するか、クエリの実行計画をキャプチャして、インデックスの使用状況をスキャンする必要がありました。

新しい動的管理ビュー、sys.dm_db_index_usage_stats では、クエリ オプティマイザやテーブルに対して実行されるクエリでインデックスがどのように使用されているかを簡単に把握できます。このビューからインデックスの有用性を判断できるので、クエリ オプティマイザが使用していない任意のインデックスを削除することができます。インデックスが記憶域スペースを無駄に占有していないか、未使用のインデックスのメンテナンスによってデータベースのパフォーマンスが低減していないかと心配する必要がなくなりました。

シーク数とスキャン数がゼロのインデックスに対してこの DMV を実行し、その出力を調べることで、そのインデックスが前回 SQL Server を起動したときから使用されているかどうかを判定できます。ただし、多くの DMV および DMF には永続性がありません。DMV や DMF を使用してインデックスの使用状況を確認するときは、SQL Server を再起動すると値がゼロに戻ってしまう点に注意してください。インデックスは、前回サービスを開始したときから不要になっていた可能性もありますが、週末、月末、または四半期末のレポート クエリで必要になります。

前回 SQL Server サービスを再開してから使用されていないインスタンス内のすべてのインデックスを表示するには、次のステートメントを使用します。

SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

インデックス操作の状況

インデックス操作の状況を把握するには、DMF の sys.dm_db_index_operational_stats を使用すると非常に便利です。sys.dm_db_index_operational_stats では、データベース内の各インデックスについて、入出力、ロック、ラッチ、およびアクセス メソッドのアクティビティを表示できます。ここからインデックスの使用状況を把握し、過剰な入出力アクティビティやインデックス内の "ホット スポット" の存在によって発生するインデックス ロックの問題を診断できます。

この DMF によって出力されるラッチ待機情報は、READ および WRITE 操作でインデックスのリソースにアクセスするために必要な所要時間を割り出すために役立ちます。これにより、インデックスの保存に使用されるディスク サブシステムがインデックスの入出力アクティビティに対して適切であるかどうかを判断できます。また、インデックスのデザインと使用状況によってホット スポットが発生したかどうかも示します。ホット スポットでは、インデックスのページ内のアクティビティが非常に多いため、ページ内でデータの競合が発生します。このような競合が発生すると、通常、この領域に対する READ または WRITE の操作が大量にブロックされます。

図 3 に、AdventureWorks.HumanResources.Employee テーブルのすべてのインデックスのロック パターンと入出力パターンの確認方法を示します。

Figure 3 ロックと入出力のパターンの特定

SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

その他の詳細

DVM と DMF には、ここで紹介した以外にもさまざまな使用方法があります。補足記事「参考資料」に挙げた SQL Server Books Online の記事をよく読み、DVM または DMF の出力結果から得られるさまざまな情報について理解を深めてください。

この記事では取り上げなかった DMF および DMV については、SQL Server クエリ最適化チームのブログ記事 (blogs.msdn.com/queryoptteam/570176.aspx) (英語) を参照してください。

参考資料

• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats

Randy Dyess は、SQL Server OLTP システムを専門分野とする Solid Quality Learning の指導者です。彼は、SQL Server 関連の書籍や記事を多数執筆しており、

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.