SQL Server のチューニングについてまとめてみる - その10 - ( 実行プランの StatementText の中に、調査用に使用するための文字列を埋め込んでみる )

今回やることを説明してみる

　『SQL Server のチューニングについてまとめてみる』も今回で10回目になりました。それを記念してブログを『はてな記法 』で書いてみます。( 何で記念に『はてな記法』なのかは意味不明ですが... ) 今回は、実行プラン中に独自文字列を保持するようにしてみます。

使用する環境を説明してみる

　私の Windows 8.1 Update1、64ビット 上で 動作している SQL Server 2014 を使用します。データベースは、AdventureWorks2014です。あと、開発環境として、Visual Studio 2013 Update2 を使用します。

お試しした内容を説明してみる

　大きなプロジェクトになると、実行プランを見ていてもどのプログラムから、どんな感じで発行されているのかわからないことがよくあります。なにかキーワードが入っていればわかりやすいと思ったので、SQL 発行時に下記のような工夫をします。

private void button1_Click(object sender, EventArgs e)
{
    string cstr = string.Empty;

    cstr += "Data Source = " + "localhost" + ";";
    cstr += "Initial Catalog =" + "AdventureWorks2014" + ";";
    cstr += "Integrated Security = SSPI;";

    using (var scon = new System.Data.SqlClient.SqlConnection(cstr)) { 
        scon.Open();

        var cmd = scon.CreateCommand();
        cmd.CommandType = CommandType.Text;
        // CommandText にコメントを付加して実行します。
        cmd.CommandText = "-- From WindowsFormApplication" + "\n" + "SELECT * FROM HumanResopartment";
    
        var rd = cmd.ExecuteReader();
        cmd.Dispose();
        cmd = null;

        while (rd.Read())
        {

        }

        rd.Close();
        rd.Dispose();
        rd = null;
    }
}

　コメントを付加して実行することで、実行プラン内の StatementText に下記のように記録されます。(実行プランの中身は部分的に省略しています。)

<StmtSimple StatementText="-- From WindowsFormApplication&#xA;SELECT * FROM HumanResources.Department"></StmtSimple>

　記録されただけでは意味ないので、埋め込んだ情報を元に実行プランを含めた色々な情報を抽出してみましょう。過去のブログでいろいろやっているので、それらを参考にクエリを組んでみました。

SQL Server のチューニングについてまとめてみる - その5 - ( クエリプランから不足インデックス情報を取得する ) - 都内で働くSEの技術的なひとりごと

SQL Server のチューニングについてまとめてみる - その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう ) - 都内で働くSEの技術的なひとりごと

SQL Server のチューニングについてまとめてみる - その7 - ( プランキャッシュの状態を確認する ) - 都内で働くSEの技術的なひとりごと

『 From WindowsFormApplication 』 という文字列が StatementText 属性に埋め込まれているものを抽出するクエリは下記の通りです。下記のクエリを SQL Server Management Studio で実行してみましょう。※雑なクエリだなぁ。

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

SELECT [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),
       /* ClusterIndexScan を抽出します。何か出力されたら、QueryPlan を確認してね。 */
       [ClusteredIndexScan] = qp.query_plan.query('//sp:RelOp[@PhysicalOp="Clustered Index Scan"]'),
       /* IndexScan を抽出します。 何か出力されたら、QueryPlan を確認してね。*/
       [IndexScan] = qp.query_plan.query('//sp:RelOp[@PhysicalOp="Index Scan"]'),
       /* Warnings を抽出します。何か出力されたら、QueryPlan を確認してね。 */
       [Warnings] = qp.query_plan.query('//sp:Warnings'), 
       /* Lookup を抽出します。 何か出力されたら、QueryPlan を確認してね。*/
       [Lookup] = qp.query_plan.query('//sp:IndexScan[@Lookup="1"]'),
       /* Nested Loops を抽出します。 何か出力されたら、念のためにQueryPlan を確認してね。*/
       [Nested Loops] = qp.query_plan.query('//sp:RelOp[@PhysicalOp="Nested Loops"]'),
       /* QueryPlan を表示します。 */
       [QueryPlan] = 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
/* contains 句を使って、StatementText 属性から 『Form WindowsFormApplication』を抽出します。 */
WHERE qp.query_plan.exist('//sp:StmtSimple[contains(@StatementText, ''From WindowsFormApplication'')]') = 1

　contains に指定した条件で抽出されています。

おわりに何か書いてみる

　はてな記法いいですね！ブログ書くのがすごく楽です。はてなでブログを始めて、1年半たってようやくそのすごさに気づいた...