How to find your worst-performing SQL Server query

I've often been asked this question repeatedly: "I have a poorly performing SQL Server." How do I find the worst performance query? “。 So in today's article there are some information and wizards that make it easy to find answers to your questions.

Ask SQL server!

One advantage of SQL Server is that it can answer almost all of your problems, because SQL Server stores a lot of troubleshooting information in each DMV and DMF. On the other hand this is also a disadvantage, because you have to know each DMV/DMF, and how to interpret and relate them together.

As for your worst performance SQL Server queries one of the most important DMV is sys.dm_exec_query_stats. For each cached execution plan, SQL Server stores the details of the execution plan at run time. In addition, SQL Server tells you the CPU time and I/O reads consumed by this query. When I troubleshoot poorly performing SQL Server, this is one of the basic DMV I often use.

Let's get into sys.dm_exec_query_stats!.

When you make a simple select query on sys.dm_exec_query_stats, you get a very broad set of records with many different columns--lots of different numbers.

Let's take a closer look at them. For each cached execution plan, SQL Server gives you information about the following metrics:

• Worker Time (Columns ... _ Worker hours)
• Physical Reads (Columns ... _ physical reading)
• Logical writes (Columns ... _ logical Write)
• Logical Reads (Columns ... _ logical reading)
• SQLCLR Time (Columns ... _ Common language runtime)
• Elapsed Time (Columns ... _ Runtime)
• Row count (Columns ... _ Number of rows)

For each metric, you get a different column of 4 collection information:

• Value (total value)
• Last value
• Min value (min value)
• Maximum value (max value)

Have this information on your hands to find out what your worst-performing queries are. But first you need to know what is your performance bottleneck--cpu or I/O limit? If your performance bottleneck is CPU throttling, you can use the following query to ask SQL Server to list the top 5 worst-performing queries based on CPU consumption:

--Worst performing CPU bound Queriesselect TOP 5    st.text, Qp.query_plan    ,    qs.*from sys.dm_exec_query_stats Qscross apply Sys.dm_exec_sql_text (qs.plan_handle) Stcross apply Sys.dm_exec_query_plan (Qs.plan_handle) QpORDER by Total_worker_time Descgo

You can see here that I used a simple ORDER by Total_worker_time DESC to return a CPU-intensive query. The SQL statement and the execution plan itself are also fetched by invoking the sys.dm_exec_sql_text and sys.dm_exec_query_plan DMF. The following code shows how to find your worst-performing query based on I/O consumption.

1--worst performing I/O bound queries 2 SELECT TOP 5 3     st.text, 4     Qp.query_plan, 5     qs.* 6 from Sys.dm_exec_q Uery_stats QS 7 Cross apply Sys.dm_exec_sql_text (Qs.plan_handle) St 8 cross Apply sys.dm_exec_query_plan (Qs.plan_handle) QP 9 ORDER by Total_logical_reads DESC10 GO

When you have SQL statements and execution plans in front of you, you can further analyze the query to find out what is causing high CPU or I/O consumption.

Summary

SQL Server is a stunning product: it can give you a good answer to your question right away. You just need to know where to find your answer. As for poorly performing queries, you should always start by analyzing the DMV sys.dm_exec_query_stats , where SQL Server saves you execution of scheduled run-time statistics.

Thanks for your attention!

How to find your worst-performing SQL Server query