動的ビューでよく使うクエリ備忘録

SQL Serverの動的ビューは非常に多くの情報を提供してくれますが、如何せん数が多くその全部を全て覚えるのは現実的ではありません。
時々使うクエリを備忘録としてまとめます。

基本的にはどこで拾ったものですが、どこで拾ったかは記録していないので出典は明記できません。

過去に実行したクエリを探したい時

SELECT
    st.text
  , qs.last_execution_time
  , qs.total_rows
  , qs.total_elapsed_time
FROM  sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE last_execution_time >= '2019/12/27'
ORDER BY last_execution_time DESC

DBに接続しているユーザーを一覧表示したい時

SELECT
    r.session_id
  , r.status
  , s.login_name
  , c.client_net_address
  , s.host_name
  , s.program_name
  , st.text
  , c.connect_time
FROM sys.dm_exec_requests as r
INNER JOIN sys.dm_exec_sessions as s ON r.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_connections as c ON r.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

最もリソースを消費しているクエリを一覧表示したい時

SELECT
    DB_Name(qp.dbid) AS [DB]
  , qp.dbid AS [DBID]
  , qt.text
  , qs.execution_count
  , qs.total_rows
  , qs.min_rows
  , qs.max_rows
  , qs.last_rows
  , qs.total_logical_reads/qs.execution_count AS avg_logical_reads
  , qs.total_physical_reads/qs.execution_count AS avg_physical_reads
  , qs.total_logical_writes/qs.execution_count AS avg_writes
  , (qs.total_worker_time/1000)/qs.execution_count AS avg_CPU_Time_ms
  , qs.total_elapsed_time/qs.execution_count/1000 AS avg_elapsed_time_ms
  , qs.last_execution_time
  , qp.query_plan AS [Plan]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY execution_count DESC, qs.total_logical_reads desc, total_rows desc;

最もブロック時間が多く発生しているクエリを一覧表示したい時

SELECT TOP (20)
   DB_NAME(qt.dbid) as dbname
  , qt.text as blocked_query
  , (total_elapsed_time - total_worker_time) / qs.execution_count AS average_time_blocked
  , total_elapsed_time - total_worker_time AS total_time_blocked
  , qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY average_time_blocked DESC;