Most of us wanted to know which queries are use the most CPU time and most IO in a db server.
We can get that information using the dynamic management view, such as dm_exec_query_stats and dm_exec_sql_text.
For this, first of all we need to create the following procedure.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_TOP_Queries_Average]
CREATE procedure dba_TOP_Queries_Average @topcount int=10,
@type varchar(10) ='cpu'
select Top (@topcount )
,rank() over(order by (total_worker_time+0.0)/
sql_handle,statement_start_offset ) as row_no
, (rank() over(order by (total_worker_time+0.0)/
sql_handle,statement_start_offset ))%2 as l1
, (total_worker_time+0.0)/1000 as total_worker_time
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, total_logical_reads+total_logical_writes as [AggIO]
(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then ' '
else ( substring(st.text,(qs.statement_start_offset+2)/2,
(case when qs.statement_end_offset = -1
end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as db_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
order by case when @type='cpu' then (total_worker_time+0.0)/(execution_count*1000)
else (total_logical_reads+total_logical_writes)/(execution_count+0.0) end desc
select -100 as row_no
, 1 as l1, 1 as create_time,1 as last_execution_time,1 as total_worker_time,1 as AvgCPUTime,1 as LogicalReads,1
, ERROR_NUMBER() as execution_count
, ERROR_SEVERITY() as AggIO
, ERROR_STATE() as AvgIO
, ERROR_MESSAGE() as query_text
, 0 as db_name
, 0 as object_name
Now we can get the top 10 queries based on the CPU usage. By default our procedure will return top 10 queries. Because we given the topcount as 10.
If we need to get top 10 queries based on the average I/O usage, do as following.
exec [dbo].[dba_TOP_Queries_Average] @type = 'Avg'
If we need to get top n queries,
exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'Avg'
exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'cpu'