How to find your worst-performing SQL Server query

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 intosys.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 queries  select  top  5   St.  text   *  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 go  

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 queries2 SELECT TOP 53St.text,4 Qp.query_plan,5Ssl*6  fromsys.dm_exec_query_stats QS7  CrossAPPLY sys.dm_exec_sql_text (qs.plan_handle) St8  CrossAPPLY sys.dm_exec_query_plan (qs.plan_handle) QP9 ORDER  byTotal_logical_readsDESCTen 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