SQL Server 2017 と SQL Database で利用可能な方法ですが、これらのバージョンでは、クエリストアの情報として、待ち事象の情報が追加されています。
待機クエリの検索
クエリストア自体が強力な機能なのですが、最新のバージョンでは、この情報に sys.query_store_wait_stats システムビューが追加されています。
今までのアプローチは、sys.dm_os_wait_stats を使用してインスタンスとしての待ち事象の傾向を調べたり、sys.dm_exec_session_wait_stats でセッション単位の待ち事象を調べるというアプローチだったかと思います。
SQL Server 2017 / SQL Database では、インスタンスや DB に対しての待ち事象を調査した後に、どのクエリが原因となっていそうかという調査をすることが可能になっています。
例としては、次のようなクエリを実行すると、取得期間の標準偏差で 10 ms 以上、ロック競合が発生していたクエリをクエリストアから調査するということが可能です。
SELECT T3.*, T1.*, T2.* FROM sys.query_store_wait_stats T1 LEFT JOIN sys.query_store_runtime_stats T2 ON T2.runtime_stats_interval_id = T1.runtime_stats_interval_id LEFT JOIN sys.query_store_plan T3 ON T3.plan_id = T1.plan_id WHERE stdev_query_wait_time_ms >= 10 and wait_category = 3 AND T2.first_execution_time >= DATEADD(dd, -7, GETUTCDATE())
クエリストアは「クエリ ID」から特定のクエリの情報を簡単に追跡することが可能ですので、上記のクエリの実行結果から実際のクエリの情報を取得するというアプローチが可能となっています。
上記の投稿ではロックについての情報を取得しているため「wait_category=3」で検索をしていますが、「wait_category=6」とした場合には、バッファーラッチとなりますので、ディスクからのデータアクセスで時間がかかったクエリの調査なども可能となります。
このような確認ができるようになったことで、特定の待ち事象が高い値となっていた場合の調査を、既存の情報だけで実施する場合に、どのクエリかの当たりが、かなりつけやすくなるのではないでしょうか。