SQL query for identifing the 20 slowest queries on your server

Hi All,

I was reading a book from manning publisher titled "SQL Serer DMV's IN ACTION" and came across this useful query for finding the 20 Slowest queries in your server which is very useful stuff for the people who are facing database performance issue. I thought i will post this on my blog and will be handy resource .

All the credit goes to the author of this finest book

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) #A
AS [Total Elapsed Duration (s)]
, execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1, #B
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans as cp
on qs.plan_handle=cp.plan_handle
ORDER BY total_elapsed_time DESC #C

#A Get query duration
#B Extract SQL statement
#C Sort by slowest queries

Explanation from the book mentioned above :

The DMV sys.dm_exec_query_stats contains details of various metrics that relate to an individual SQL statement (within a batch). These metrics include query duration (total_elapsed_time),the number of times the query has executed (execution_count).Additionally,it records details of the offsets of the individual query within the parent query.To get details of the parent query and the individual query, the offset parameters are passed to the DMF sys.dm_exec_sql_text.

The Cross Apply statement can be thought of as a join to a table function that in this case takes a parameter,. Here the parameter is the id of the cached plan that contains the textual representation of the query. The query’s cached plan is also output, as XML. The results are sorted by the total_elapsed_time. To limit the amount of output only the slowest 20 queries are reported on.

The results show the cumulative impact of individual queries, within a batch or stored procedure. Knowing the slowest queries will allow you to make targeted improvements,confident in the knowledge that any improvement to these queries will have the biggest impact on performance.

The cached plan is probably the primary resource for discovering why the query is running slowly, and often will give an insight into how the query can be improved.The NULL values in the Databasename column mean the query was run either ad hoc or using prepared SQL (i.e. not as a stored procedure). This itself can be interesting since it indicates areas where stored procedures are not being re-used, and possible areas of security concern. Later, an improved version of this query will get the underlying database name for the ad-hoc or prepared SQL queries from another DMV source.

Take Care , C u soon