SQL Server Database Administrators usually like to know which queries use the most CPU time and which queries use the most I/O. To get that information, SQL Server Database Administrators have run profiler pro-actively in SQL Server 7.0 and SQL Server 2000.
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.
Lets create the stored procedure dba_TOP_Queries_Average, as shown below.
/****** Object: StoredProcedure [dbo].[dba_TOP_Queries_Average]
Script Date: 03/11/2008 15:08:25 ******/
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
Lets execute the procedure as shown below. [Refer Fig 1.0]
This will produce the result found here. [Refer Fig 1.1]
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]
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]
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.
» See All Articles by Columnist MAK