Sunday, April 18, 2010

Top SQL query by cpu for MSSQL Server

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: