In SQL Server 2005 we could query this information using the dynamic management view, such as dm_exec_query_stats and dm_exec_sql_text.

This article illustrates how to query dynamic management views such as dm_exec_query_stats and dm_exec_sql_text to find the TOP queries based on Average CPU and Average I/O.

Step 1

Let’s create the stored procedure dba_TOP_Queries_Average, as shown below.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[dba_TOP_Queries_Average]    
 Script Date: 03/11/2008 15:08:25 ******/
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

--dba_TOP_Queries_Average

Step 2

Let’s execute the procedure as shown below. [Refer Fig 1.0]

exec [dbo].[dba_TOP_Queries_Average]

This will produce the result found here. [Refer Fig 1.1]

Step 3

By default, this procedure shows the results of the TOP 10 queries based on Average CPU usage. In order to display the TOP 10 queries based on average I/O, we could execute the following Transact SQL Statement as shown below. [Refer Fig 1.2]

exec [dbo].[dba_TOP_Queries_Average] @type = 'Avg'

This will produce the result shown here. [Refer Fig 1.3]

Step 4

Instead of the Top 10 Queries, you can also specify a larger number of queries based on Average CPU or Average I/O, then execute the following transact SQL Statement. [Refer Fig 1.4]

exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'Avg'
exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'cpu'

This will produce the following result, with 14 rows. [Refer Fig 1.5,1.6]

Conclusion

By querying the dynamic management views such as dm_exec_query_stats and dm_exec_sql_text, we can create a procedure that produces the top queries based on Average CPU and Average I/O.