Fun with SQL Server 2005 DMV’s

Here is another useful Dynamic Management View (DMV) query to find out which cached query plans are executed most fequently.

SELECT TOP 50 qt.text, qs.execution_count, qs.total_elapsed_time/qs.execution_count AS ‘AvgElapsedTime’, qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,
qs.max_logical_reads, qs.max_logical_writes
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 — This filters to a specific database
ORDER BY qs.execution_count DESC

The sys.dm_exec_query_stats view shows aggregate performance statistics for cached query plans. This means that if a stored procedure is edited and saved or if it is recompiled, it’s statistics will be cleared out and will start from scratch in the view. Also, like all DMV’s, the data is lost when SQL Server is restarted.

Bearing this in mind, what I like to do to get a more accurate snapshot is to run the query to get the initial results. Then, I build a script that explicitly recompiles the stored procedures that show up in the list from the intial query results, like this:

sp_Recompile ‘GetUserName’

GO

Until each of the top fifty Stored Procedures are in the script. Then I run the script to recompile all fifty SP’s, (which clears their performance statistics in the view). Now, after this somewhat tedious preparation, I can run the original query after a minute, five minutes, etc. to get more accurate counts of query execution counts.

One thing to keep in mind is that the execution times in the view are in microseconds, not milliseconds.

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s