プロシージャ キャッシュの中身を参照（dm_exec_cached_plans）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「プロシージャ キャッシュの中身」を参照できるスクリプトです。DMV（動的管理ビュー）の dm_exec_cached_plans と、動的管理関数（DMF）の dm_exec_query_plan、dm_exec_sql_text を利用しています。

SELECT text AS sqltext, query_plan AS 実行プラン, usecounts, *
 FROM sys.dm_exec_cached_plans
    CROSS APPLY
      sys.dm_exec_query_plan (plan_handle)
	CROSS APPLY
	  sys.dm_exec_sql_text( plan_handle )

プロシージャ キャッシュの objtype ごとの集計（dm_exec_cached_plans）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「プロシージャ キャッシュの objtype ごとの集計」を取得できるスクリプトです。DMV（動的管理ビュー）の dm_exec_cached_plans を objtype で GROUP BY しています。

SELECT
   objtype
 , SUM(size_in_bytes) / 1024 AS 使用サイズKB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY 2 DESC

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

I/O 数の多いクエリ TOP 100（dm_exec_query_stats）

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

SELECT TOP 100
   total_physical_reads / execution_count AS [平均物理I/O 数]
 , (total_logical_reads + total_logical_writes)
                        / execution_count AS [平均論理I/O 数]
 , total_elapsed_time / execution_count / 1000.0 AS [平均実行時間（ミリ秒）]
 , total_worker_time  / execution_count / 1000.0 AS [平均CPU 時間（ミリ秒）]
 , 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 1 DESC, 2 DESC

tempdb のファイルの使用状況（dm_db_file_space_usage）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「tempdb のファイルの使用状況」を取得できるスクリプトです。DMV（動的管理ビュー）の dm_db_file_space_usage をクエリしています。

SELECT * FROM sys.dm_db_file_space_usage

tempdb の接続ごとの使用状況（dm_db_session_space_usage）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「tempdb の接続ごとの使用状況」を取得できるスクリプトです。DMV（動的管理ビュー）の dm_db_session_space_usage をクエリしています。

SELECT * FROM sys.dm_db_session_space_usage

tempdb のタスクごとの使用状況（dm_db_task_space_usage）

TechEd 2009 での私のシアターセッション「SQL Server 動的管理ビュー再入門」で紹介した「tempdb のタスクごとの使用状況」を取得できるスクリプトです。DMV（動的管理ビュー）の dm_db_task_space_usage をクエリしています。

SELECT * FROM sys.dm_db_task_space_usage

-- dm_exec_requests と JOIN して接続情報
-- dm_exec_query_plan で実行プランを取得可能
SELECT query_plan, text, *
 FROM sys.dm_db_task_space_usage t
  INNER JOIN sys.dm_exec_requests r
    ON  t.session_id = r.session_id
    AND t.request_id = r.request_id
     CROSS APPLY
      sys.dm_exec_sql_text( sql_handle )
       CROSS APPLY sys.dm_exec_query_plan (plan_handle)