Figuring out the most popular queries in seconds

Another great DMV that can help save you a lot of work 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