I get asked this question over and over again: “I have a SQL Server that is performing badly. How can I find the worst performing queries?”. So in today’s blog posting I want to give you some information and guidance that will let you answer this question very easily.

Ask SQL Server!

The nice thing about SQL Server is that SQL Server itself can answer almost all of your questions, because SQL Server stores a lot of troubleshooting information in various Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). On the other hand this is also a bad thing, because you have to know the various DMVs/DMFs, and how to interpret and correlate them together.

One of the most important DMVs regarding your worst performing SQL Server queries is sys.dm_exec_query_stats. For every cached execution plan SQL Server stores detailed information about how this execution plan performed at runtime. And in addition SQL Server tells you how much CPU time and how much I/O this specific query consumed. This is one of the DMVs that I use on regular basis when I have to troubleshoot badly performing SQL Server installations.

Let’s get into sys.dm_exec_query_stats!

When you do a simple SELECT against sys.dm_exec_query_stats you get back a very wide result set with a lot of different columns – and with a huge amount of different numbers.

Let’s have a more detailed look at them. For every cached execution plan SQL Server gives you information about the following metrics:

• Worker Time (columns …_worker_time)
• Physical Reads (columns …_physical_reads)
• Logical Writes (columns …_logical_writes)
• Logical Reads (columns …_logical_reads)
• SQLCLR Time (columns …_clr_time)
• Elapsed Time (columns …_elapsed_time)
• Row Count (columns …_rows)

And for every metric you get 4 different columns with aggregation information:

• Total value
• Last value
• Min value
• Max value

With this information to hand it is very easy to find out what your worst performing queries are. But first you have to know what your performance bottleneck is – is it CPU or I/O bound? If your performance bottleneck is CPU bound, you can ask SQL Server for your top 5 worst performing queries regarding CPU consumption with the following query:

-- Worst performing CPU bound queries
SELECT TOP 5
	st.text,
	qp.query_plan,
	qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO

As you can see I just do here a simple ORDER BY total_worker_time DESC to get back the CPU intensive queries. In addition I also grab the SQL statement and the execution plan itself by calling the DMFs sys.dm_exec_sql_text and sys.dm_exec_query_plan. The following query shows how to find your worst performing queries regarding I/O consumption.

-- Worst performing I/O bound queries
SELECT TOP 5
	st.text,
	qp.query_plan,
	qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO

When you have the SQL statement and the execution plan in front of you, you can further troubleshoot the query and find out what caused the high CPU or I/O consumption.

Summary

SQL Server is an amazing product: it can immediately give you very good answers to your questions. You only have to know where to search for your answer. Regarding poor performing queries you should always start by analyzing the DMV sys.dm_exec_query_stats, where SQL Server stores runtime statistics about your execution plans.

Thanks for your time,

-Klaus