Check the fantastic query below to find the 25 top most cpu intensive queries for a SQL Server Instance.
select top 25 * from (SELECT pa.value AS dbid,
       COALESCE(DB_NAME(st.dbid),
                DB_NAME(CAST(pa.value as int))+'*') AS DBNAME, 
       SUBSTRING(text,
        -- starting value for substring 
        CASE WHEN statement_start_offset = 0 
             OR statement_start_offset IS NULL 
               THEN 1 
               ELSE statement_start_offset/2 + 1 END,
        -- ending value for substring
        CASE WHEN statement_end_offset = 0 
          OR statement_end_offset = -1 
          OR statement_end_offset IS NULL 
               THEN LEN(text) 
               ELSE statement_end_offset/2 END - 
                   CASE WHEN statement_start_offset = 0 
                          OR statement_start_offset IS NULL 
                               THEN 1 
                               ELSE statement_start_offset/2  END + 1
        )  AS TSQL, total_worker_time,execution_count,
        total_worker_time/execution_count avg_cpu,total_physical_reads/execution_count avg_physical_read,
        total_elapsed_time/execution_count avg_elapsed_time,total_logical_reads/execution_count avg_logical_read
FROM sys.dm_exec_query_stats  
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
   OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
    WHERE attribute = 'dbid') a where a.dbid>4 order by avg_cpu desc;
Let me know any problem on execution.
Thanks.
 
 
No comments:
Post a Comment