// Net planet-es V2

インデックスの使用回数、断片化情報など、インデックスチューニングのために使用するクエリのサンプルを掲載します。

動作確認はSQL Server 2005 Developer Edition で行っています。

1. インデックスの統計情報を取得する

インデックスの情報の取得に今回は動的管理ビューを使用します。インデックス関連の動的管理ビューはBooks Onlineのインデックス関連の動的管理ビューおよび関数 (Transact-SQL)を参照してください。

1.1 インデックスの断片化情報の取得

インデックスの断片化情報は、インデックス関連の動的管理関数の一つである、sys.dm_db_index_physical_stats 動的管理関数を使用します。インデックス名はsys.indexes 管理ビューと結合を行うことで取得します。

-- データベースのインデックスの断片化情報を取得する
select DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc
     , i.name, s.fragment_count, s.avg_fragmentation_in_percent
 from sys.dm_db_index_physical_stats(DB_ID('Component'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id

1.2 インデックスの使用回数を取得する

SQL Server が起動してから、インデックスの使用回数の統計情報を取得できます。SELECT文などを使用したときに、インデックスが使用された回数を取得することができます。インデックス関連の動的管理ビューの一つであるsys.dm_db_index_usage_stats動的管理ビューを使用します。

-- SQLサーバが起動してからの、インデックス操作の使用回数
-- と直近の実行された時刻を取得します
SELECT *
  FROM sys.dm_db_index_usage_stats
where database_id = DB_ID('Component')
  and OBJECT_ID('dbo.Article') = object_id

2. 統計情報の取得

SET STATICS IO などを使用するとSQL の実行の統計情報を取得できます。TIME, PROFILE, XML などを指定できます。

-- SQLのディスク利用状況に関する情報を表示します
SET STATISTICS IO ON
-- 各SQLの解析、コンパイル、および実行に必要な時間をミリ秒単位で表示します
SET STATISTICS TIME OFF
-- 
SET STATISTICS PROFILE ON
-- SQL を実行し、実行方法に関する詳細情報をウェルフォームド XML ドキュメント形式で生成します
SET STATISTICS XML ON

sys.dm_exec_query_stats 動的管理ビューを使用すると、キャッシュされているクエリの実行プラン(論理,物理リード回数、実行時間など)を取得できます。キャッシュプランをDBCC FREEPROCCACHE を使用してクリアしてから論理リード回数などを取得しています。

-- キャッシュをクリア
DBCC FREEPROCCACHE
GO

SELECT *
  FROM dbo.Article
 WHERE ArticleID = 1
-- キャッシュされたクエリ プランの集計パフォーマンス統計
SELECT sql.text, stat.total_logical_reads, stat.total_physical_reads
  FROM sys.dm_exec_query_stats stat
 CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) sql

3.推定実行プランの表示

SQL Server Management Studio を使用するとグラフィカルに推定の実行プランと、実行プランを表示することができますが、テキストとして実行コストの見積もりを出力するこができます。SET SHOWPLAN_ALL, SHOWPLAN_TEXT,SHOWPLAN_XML を使用します。

-- SQL ステートメントを実行せず、代わりにステートメントの実行方法と実行に必要なリソースの見積もりに関する詳細情報を返します
SET SHOWPLAN_ALL ON
-- SQL ステートメントを実行せず、代わりにステートメントの実行方法に関する詳細情報を返します
SET SHOWPLAN_TEXT ON
-- SQL ステートメントを実行せず、代わりにステートメントの実行方法に関する詳細情報を、整形式の XML ドキュメントで返します
SET SHOWPLAN_XML ON

4. まとめ

今回の説明は以上です。誤り、指摘点などがありましたらご指摘ください。