4
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

SQL Server CPU負荷が高いSQLを検出するためのSQL

Last updated at Posted at 2020-07-06

このまま動作します。
コピぺ、編集して利用するためのSQLです。
自由に使って頂いて構いません。

SQLServerにおいて、データベースサーバのCPUに高い負荷をかけているSQLを見つける用途で使用できます。

#SQLの実行結果の見た目
image.png

#CPU負荷が高いSQLを検出するためのSQL

SELECT
    TOP 50 --上位50件
    [dm_exec_query_stats].[total_worker_time] / [dm_exec_query_stats].[execution_count] / 1000 AS [平均CPU時間(ミリ秒)],
    [dm_exec_query_stats].[max_worker_time] /1000 AS [最大CPU時間(ミリ秒)],  
    [dm_exec_query_stats].[total_worker_time] / 1000 AS [合計CPU時間(ミリ秒)],
    [dm_exec_query_stats].[total_logical_reads] / [dm_exec_query_stats].[execution_count] AS [平均読取数],
    [dm_exec_query_stats].[max_logical_reads] AS [最大読取数],  
    [dm_exec_query_stats].[total_logical_reads] AS [合計読取数],
    [dm_exec_query_stats].[last_execution_time] AS [最終実行時刻],
    [dm_exec_query_stats].[execution_count] AS [実行回数],
    [dm_exec_sql_text].[text] AS [SQL(コメントあり)]

FROM
    [master].[sys].[dm_exec_query_stats] [dm_exec_query_stats]
    CROSS APPLY [master].[sys].[dm_exec_sql_text]([dm_exec_query_stats].[sql_handle]) [dm_exec_sql_text]

WHERE
    [dm_exec_sql_text].[text] NOT LIKE '%dm_exec_query_stats%' --本クエリを結果から取り除く。

ORDER BY
    [dm_exec_query_stats].[total_worker_time] / [dm_exec_query_stats].[execution_count] DESC --一回あたりのCPU時間が長い順に並べ替える。
;

SQL Server 2014 12.0.x.xで動作確認をしています。

4
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?