【SQLServer】現在実行中の重いクエリの一覧を出す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 |
USE master GO SET NOCOUNT ON GO SELECT TOP 100 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) AS [row_no] , db_name(st.dbid) AS [database_name] , creation_time , last_execution_time , (total_worker_time+0.0)/1000 AS [total_worker_time(ms)] , (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)] , (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)] , (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)] , total_logical_reads AS [LogicalReads(page)] , total_logical_writes AS [logicalWrites(page)] , total_logical_reads+total_logical_writes AS [AggIO(page)] , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)] , execution_count , total_rows , st.text AS [batch_query_text] , CASE WHEN sql_handle IS NULL THEN ' ' ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2 ELSE qs.statement_end_offset END - qs.statement_start_offset) /2 ) ) END AS [statement_query_text] , plan_generation_num , qp.query_plan FROM sys.dm_exec_query_stats AS [qs] CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st] CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp] WHERE total_worker_time > 0 ORDER BY total_worker_time DESC OPTION (RECOMPILE) |
