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

　実行プランのキャッシュ状態、パフォーマンスをチェックするクエリです。オブジェクトの種類が Adhoc か Prepared なのかとか、その他色々気になりますよね？

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