How to find the queries which are use the most CPU time and most IO?

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.

USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[dba_TOP_Queries_Average]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_TOP_Queries_Average]
go
CREATE procedure dba_TOP_Queries_Average @topcount int=10,
@type varchar(10) ='cpu'
as
begin try
select Top (@topcount )
creation_time
, last_execution_time
,rank() over(order by (total_worker_time+0.0)/
execution_count desc,
sql_handle,statement_start_offset ) as row_no
, (rank() over(order by (total_worker_time+0.0)/
execution_count desc,
sql_handle,statement_start_offset ))%2 as l1
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000)
as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/
(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
then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
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
end try
begin catch
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
as LogicalWrites
, 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
end catch

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.

For example,

exec [dbo].[dba_TOP_Queries_Average]

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'

That’s it…

...S.VinothkumaR