CPU 利用の多いクエリ TOP 100（dm_exec_query_stats）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「CPU 利用の多いクエリ TOP 100」を取得できるスクリプトです。DMV（動的管理ビュー）の dm_exec_query_stats と、動的管理関数（DMF）の dm_exec_sql_text を利用しています。

SELECT TOP 100
   total_elapsed_time / execution_count / 1000.0 AS [平均実行時間（ミリ秒）]
 , total_worker_time  / execution_count / 1000.0 AS [平均 CPU 時間（ミリ秒）]
 , total_physical_reads / execution_count AS [平均物理I/O 数]
 , (total_logical_reads + total_logical_writes)
                                       / execution_count AS [平均論理I/O 数]
 , SUBSTRING(text, (statement_start_offset / 2) + 1, 
        ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(text)
          ELSE statement_end_offset
          END - statement_start_offset) / 2) + 1) AS sqltext
FROM
 sys.dm_exec_query_stats
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY 2 DESC