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.
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
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.