The other day I found myself looking at new\different ways to monitor the health of my production SQL Server 2005 servers. I thought that there must be a better way to monitor in SQL Server 2005 than in SQL Server 2000. So as I was looking through books online and perusing the internet I came across Dynamic Management Views (DMVs).

DMVs return server metric information that can be used to monitor the health of an instance, diagnose problems, and performance tune. One of the things I was looking for was a way to identify slow performing queries outside of using SQL Profiler. I came across the following script on SQL Server Performance website. This script returns a result set that contains queries that are performing slowly.

SELECT creation_time,
last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

The sys.dm_exec_query_stats view returns aggregate performance statistics for cached query plans. To find out more about this view click here. In my upcoming posts I will be covering the different DMVs in SQL Server 2005 in great detail. If you have other scripts that utilize this view please post them here!