都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

SQL Server のチューニングについてまとめてみる - その5 - ( クエリプランから不足インデックス情報を取得する )

 そろそろ、参加しているプロジェクトにおいて、DBMS のチューニングを始めなければいけません。ここ数日で、『SQL Server のチューニングについてまとめてみる』を充実させていきます。クエリプランから不足するインデックスを抽出したいと思います。クエリプランは、XML で格納されているため、通常の方法では抽出することはできません。

 そこで、XQuery を使用してクエリプランの XML にアクセスします。今回使用している関数は、value() メソッド (xml データ型)exist() メソッド (xml データ型) です。クエリプランからメソッドを使ってデータ抽出、存在チェックをするには、declare namespace sp = "..." で showplan の xsd を指定します。ただこの方法では、冗長になるため、WITH XMLNAMESPACES を使用し、一括で宣言します。

 上記の内容を踏まえて、下記のスクリプトSQL Server Managemnt Studio で実行してみましょう。

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

SELECT [データベース名] = qp.query_plan.value('(//sp:MissingIndex/@Database)[1]', 'nvarchar(100)'),
       [スキーマ名] = qp.query_plan.value('(//sp:MissingIndex/@Schema)[1]', 'nvarchar(100)'),
       [テーブル名] = qp.query_plan.value('(//sp:MissingIndex/@Table)[1]', 'nvarchar(100)'),
       [SQL文] = SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                                 WHEN -1 THEN
                                     DATALENGTH(st.text)
                                 ELSE
                                     qs.statement_end_offset
                                 END -
                                     qs.statement_start_offset)/2
                           ) + 1),
       [インデックスがないよ情報] = qp.query_plan.query('//sp:MissingIndexes'),
       [クエリプラン詳細] = qp.query_plan
FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE qp.query_plan.exist('//sp:MissingIndexes') = 1

f:id:koogucc11:20140904215507p:plain

 XQuery を使用することで、容易にクエリプランの XML の処理を記述することができます。赤枠部分をクリックすることで、不足しているインデックスおよび追加したほうがよいと思われる付加列候補が出力されます。

f:id:koogucc11:20140904215947p:plain

 XML 形式で出力されます。

f:id:koogucc11:20140904220214p:plain

 赤枠部分をクリックすると、クエリプランが表示されます。

f:id:koogucc11:20140904220425p:plain

 グラフィカルな実行プランが表示されます。

f:id:koogucc11:20140904220536p:plain

 今まで、あまり XQuery はまじめに使用したことがありませんでした。実際使用してみると、かなり強力な機能であることがわかると思います。

SQL ServerXML は少し研究しないといけないですね。