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

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

こんな時、RECOMPILE したほうがいいよねと思って記事書いてみた

 今日は午前中お休みを取ったので、出社前に下記のブログで作成した調査クエリを実行したときにふと思ったことを書いてみたいと思います。

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

 クエリの内容は下記の通りです。

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), 
       [PlanCacheが検索された回数] = cp.usecounts,
       [CacheSize(byte)] = cp.size_in_bytes,
       [オブジェクトの種類] = cp.objtype,
       [キャッシュ内のオブジェクトの種類] = cp.cacheobjtype,
       [プランハンドル] = cp.plan_handle,
       [プランがコンパイルされた時間] = qs.creation_time,
       [総CPU時間(msec)] = qs.total_worker_time,
       [平均CPU時間(msec)] = qs.total_worker_time/qs.execution_count, 
       [総物理読込回数] = qs.total_physical_reads,
       [平均物理読込回数] = qs.total_physical_reads/qs.execution_count,
       [総論理読込回数] = qs.total_logical_reads,
       [平均論理読込回数] = qs.total_logical_reads/qs.execution_count,
       [総論理書込回数] = qs.total_logical_writes,
       [平均論理書込回数] = qs.total_logical_writes/qs.execution_count,
       [総実行時間(msec)] = qs.total_elapsed_time,
       [平均実行時間(msec)] = qs.total_elapsed_time/qs.execution_count,
       [最少行数] = qs.min_rows,
       [最大行数] = qs.max_rows,
       [総行数] = qs.total_rows, 
       [平均行数] = qs.total_rows/qs.execution_count,
       [クエリハッシュ] = qs.query_hash,
       [クエリプランハッシュ] = qs.query_plan_hash
FROM sys.dm_exec_cached_plans cp
INNER JOIN sys.dm_exec_query_stats qs
ON cp.plan_handle = qs.plan_handle 
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

 すると下図のように、調査クエリを実行した結果に調査クエリがリストアップされたりして邪魔だなぁと思ったわけです。
f:id:koogucc11:20150204105314p:plain
 
 ここで少し工夫をします。OPTION(RECOMPILE)を付加します。

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), 
       [PlanCacheが検索された回数] = cp.usecounts,
       [CacheSize(byte)] = cp.size_in_bytes,
       [オブジェクトの種類] = cp.objtype,
       [キャッシュ内のオブジェクトの種類] = cp.cacheobjtype,
       [プランハンドル] = cp.plan_handle,
       [プランがコンパイルされた時間] = qs.creation_time,
       [総CPU時間(msec)] = qs.total_worker_time,
       [平均CPU時間(msec)] = qs.total_worker_time/qs.execution_count, 
       [総物理読込回数] = qs.total_physical_reads,
       [平均物理読込回数] = qs.total_physical_reads/qs.execution_count,
       [総論理読込回数] = qs.total_logical_reads,
       [平均論理読込回数] = qs.total_logical_reads/qs.execution_count,
       [総論理書込回数] = qs.total_logical_writes,
       [平均論理書込回数] = qs.total_logical_writes/qs.execution_count,
       [総実行時間(msec)] = qs.total_elapsed_time,
       [平均実行時間(msec)] = qs.total_elapsed_time/qs.execution_count,
       [最少行数] = qs.min_rows,
       [最大行数] = qs.max_rows,
       [総行数] = qs.total_rows, 
       [平均行数] = qs.total_rows/qs.execution_count,
       [クエリハッシュ] = qs.query_hash,
       [クエリプランハッシュ] = qs.query_plan_hash
FROM sys.dm_exec_cached_plans cp
INNER JOIN sys.dm_exec_query_stats qs
ON cp.plan_handle = qs.plan_handle 
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st OPTION(RECOMPILE)

 OPTION(RECOMPILE)を付加することで、プランに格納されなくなります。テスト的に実行するクエリなども OPTION(RECOMPILE)を付加してくれると助かりますね。調査クエリを実行したときに、『なんだー、この業務上絶対ありえないクエリーは!』とかイライラせずに済みますからね。

※お、新しい SQL Server 本が...

Microsoft SQL Server 2014 Unleashed

Microsoft SQL Server 2014 Unleashed

  • 作者: Ray Rankins,Chris Gallelli,Alex T. Silverstein,Paul Bertucci
  • 出版社/メーカー: Sams Publishing
  • 発売日: 2015/05/03
  • メディア: ペーパーバック
  • この商品を含むブログを見る
Securing SQL Server, Third Edition: Protecting Your Database from Attackers

Securing SQL Server, Third Edition: Protecting Your Database from Attackers