DB負荷などで見ておきたいことのまとめなど。(今のプロジェクトではSQLServerを使っていますが、他のデータベースでも同じかと思います。)
参考URL
SQLServer: 現在実行中クエリのリアルタイムトラブルシューティング
現在進行形のSQLを確認
現在発行されているSQLの一覧とその状態を見るクエリです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SELECT TOP 100 der.session_id as spid ,der.blocking_session_id as blk_spid ,datediff(s, der.start_time, GETDATE()) as elapsed_sec ,DB_NAME(der.database_id) AS db_name ,des.host_name ,des.program_name ,der.status -- Status of the request. (background / running / runnable / sleeping / suspended) ,dest.text as command_text ,REPLACE(REPLACE(REPLACE(SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1, ((CASE der.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text) ELSE der.statement_end_offset END - der.statement_start_offset) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS current_running_stmt ,datediff(s, der.start_time, GETDATE()) as time_sec ,wait_resource --ロックされているリソース名 ,wait_type ,last_wait_type --最後または現在の待機の種類の名前 ,der.wait_time as wait_time_ms ,der.open_transaction_count ,der.command ,der.percent_complete ,der.cpu_time ,(case der.transaction_isolation_level when 0 then 'Unspecified' when 1 then 'ReadUncomitted' when 2 then 'ReadCommitted' when 3 then 'Repeatable' when 4 then 'Serializable' when 5 then 'Snapshot' else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level ,der.granted_query_memory * 8 as granted_query_memory_kb --キロバイト単位 ,deqp.query_plan -- 実行プラン FROM sys.dm_exec_requests der JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS deqp WHERE des.is_user_process = 1 AND datediff(s, der.start_time, GETDATE()) >= 1 -- 例：1秒以上実行中のクエリに限定 AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集 ORDER BY datediff(s, der.start_time, GETDATE()) DESC |
見るべきポイントなど
メインで見ているテーブルのデータは以下のURLを参考に。
- spid・・セッションID
- elapsed_sec(=time_sec)・・SQLの実行されている時間(単位:秒)
- status・・現在のクエリの状態
- command_text・・実行されているSQL
- wait_time・・ブロックされているときの待ち時間
- granted_query_memory・・キロバイト
検索条件は
|
1 2 3 4 |
WHERE des.is_user_process = 1 AND datediff(s, der.start_time, GETDATE()) >= 1 -- 例：1秒以上実行中のクエリに限定 AND dest.text like '%%' -- クエリの中身でlike検索したいときはここを編集 |
となっているので、実行中で1秒以上実行されているSQL(あとは任意のSQLの検索語句)を抽出します。
コネクションの数
どの場所からどれくらいつながれているかのチェック
|
1 2 3 4 5 |
SELECT DB_NAME(sP.dbid) AS the_database , COUNT(sP.spid) AS total_database_connections FROM sys.sysprocesses sP GROUP BY DB_NAME(sP.dbid) ORDER BY 1; |
あまりにも多い場合、余計なSQLが発行されすぎていないか、プログラム側でcloseをつけているかなどのチェックが必要ですね・・・
メモリ消費量のの高いSQLを見る方法など
|
1 2 3 4 |
SELECT * FROM sys.dm_exec_query_memory_grants a outer APPLY sys.dm_exec_sql_text(a.sql_handle) AS sql_text outer APPLY sys.dm_exec_query_plan(a.plan_handle) AS query_plan order by grant_time desc, granted_memory_kb desc |
負荷の高いSQL(現在進行中ではなく、累積)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 [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時間が長い順に並べ替える。 ; |
- total_worker_time・・前回数分の実行時間
- execution_count・・実行回数
一定の保存期間で単純に累積のSQLの実行時間などを見ていくSQLです。
