Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 2, 2008

Top Queries in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date