sql server: find most excessive CPU queries« Wróć do listy pytań

SELECT TOP 50
  st.text AS [SQL Definition]
, SUBSTRING(
    st.text,
    qs.statement_start_offset / 2 + 1,
    ( 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
  ) AS [Hot Path]
, qs.execution_count AS [Execution Count]
, qs.total_worker_time / 1000000 AS [Total CPU (s)]
, (qs.total_worker_time / $1000000) / qs.execution_count AS [Average CPU (s)]
, qs.total_elapsed_time / 1000000 AS [Total Time (s)]
, qs.total_logical_reads / qs.execution_count AS [Average Logical Reads]
, qs.total_logical_writes / qs.execution_count AS [Average Logical Writes]
, qs.total_physical_reads / qs.execution_count AS [Average Physical Reads]
, ISNULL(qp.query_plan, '') AS [Query Plan]
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
 ORDER BY [Total CPU (s)] DESC