Thursday, November 6, 2008

Figuring out the most popular queries in seconds

Sql Server 2005 provides Dynamic Management Views(DMV) that can help save you a lot of work. One excellent and helpful DMV is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.

With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;

Find queries suffering most from blocking –
(total_elapsed_time – total_worker_time)

Find queries with most CPU cycles –

Find queries with most IO cycles –
(total_physical_reads + total_logical_reads + total_logical_writes)

Find most frequently executed queries –

You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at:

No comments: