クエリストアが使えれば済む話ですが、バージョン等の関係でクエリストアが使えないこともあると思います。
そのような環境下において、物理読み取りサイズが大きなクエリを探す方法を考えてみました。
こちらのブログで、dm_exec_requestsのダンプを定期的にとることで、物理読み取りサイズが大きなクエリを探す方法について紹介しました。
ただし、こちらのクエリだと、「並列実行で物理読み取りし続けて、読み取りがおわった瞬間に処理が終わるようなクエリ」については検知しにくいです。
↓ブログで紹介されている方法を使って5秒間隔でsys.dm_exec_requestsをダンプした結果です。
CXPACKET待ちとなっている状態ではreadsがカウントアップされないため、①から④のクエリはすべてreads=0のままとなっています。
ただし、同様のクエリをsys.dm_exec_query_statsから取得すると、かなり大量の物理読み取りを行っていたことが分かりました。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
DB_NAME(qt.dbid) as database_name
--query text
,qt.TEXT as parent_query
,SUBSTRING(qt.TEXT, qs.statement_start_offset / 2, (
CASE
WHEN qs.statement_end_offset = - 1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2) as statement
-- total
,total_worker_time / 1000 as total_CPU_time_ms
,total_elapsed_time / 1000 as total_duration_ms
,total_physical_reads as total_physical_reads
,total_physical_reads * 8 / 1024 / 1024 as total_physical_reads_gb
,execution_count
,last_execution_time
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt -- クエリテキスト用
ORDER BY total_physical_reads / execution_count desc
↓数十GBの物理読み取りが発生しているものも。
では、dm_exec_query_statsだけ取得すればいいのでは、という話になりますが、
・キャッシュアウトされるとNG（環境によっては数10秒でキャッシュアウトする可能性も）
・完了したものしか格納されない
という条件があります。
したがって、
dm_exec_requestsとdm_exec_query_statsのダンプを組み合わせて総合的に判断するのがよさそうです。