SQL Server 現在使用しているクエリで重いものを抽出するクエリ

SQL Server 現在使用しているクエリで重いものを抽出するクエリ

SQL Serverで現在使用しているクエリで実行時間の長いものを抽出するクエリを記述してます。

環境

  • OS windows10 pro
  • SQL server2017 Version 14.0.3223.3
  • SSMS 18.7.1

抽出クエリ

以下のクエリを実行することで、現在使用しているクエリで重いものを抽出することが可能です。
※ここでは上位10のみを抽出してます。

SELECT TOP 10
        rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) AS [row_no]
,       db_name(st.dbid) AS [database_name]
,       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] 
,       (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
,       creation_time
,       last_execution_time

,       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)

実行結果