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 –
(total_worker_time)
•

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

Find most frequently executed queries –
(execution_count)
•

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:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx