DBサーバのCPU負荷が高騰する事が多く、よく調査を依頼されます。
いつもはパフォーマンスダッシュボードを見ています。
インスタンスの右クリックメニューから[レポート]＞[標準レポート] です。
※SSMSのバージョン17.2以上、が要件です
表示はバケるのですが、Excelにエクスポートすると解消されます。
右中段の「CPU」をクリックすると、高コストクエリの一覧が表示されます。
こちらも同じく文字バケております。
そして、こちらが先程のレポートをExcelにエクスポートしたものになります。
累積CPU時間でソートされているので、「１SQLがめっちゃくちゃ時間が掛かる」だけでなく
「一回の処理は短いけど超絶連続実行されている」ようなケースも挙がるみたいです。
いつもこのレポートをExcelで渡していたのですが、
「全体の状況を把握したい。レポートの各クエリが、全CPU時間に対して何％だったか調査できないか」
というリクエストを受けました。
レポートは上位２０件の為、この累積時間が全体の1%なのか10%なのかもっとなのか、
対策によって全体のコストが何％削減されたのか、が知りたいという事でした。
で、色々調べてちょっと手を加えたのが下記。
SELECT query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) AS "累積 CPU 時間",
MIN(query_stats.statement_text) AS "クエリ"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
これで、このSQL実行時点での全累積CPU時間が取れるようです。
参考にしたのは下記ページ
sys.dm_exec_query_stats (Transact-SQL)
A. TOP N クエリを確認する
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver15#a-finding-the-top-n-queries ;
対策した後はDBサーバのCPU使用率が激減しました。（平均使用率で30%程度）
その割合と、上記SQLで比較した減少率がほぼ同じだったので、大きく間違ってはいないと思います。