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.
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]
Fig 1.0
This will produce the result found here. [Refer Fig 1.1]
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’
Fig 1.2
This will produce the result shown here. [Refer Fig 1.3]
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’
Fig 1.4
This will produce the following result, with 14 rows. [Refer Fig 1.5,1.6]
Fig 1.5
Fig 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.