How to find your worst-performing SQL Server query
This article will explain in detail how to find out your worst-performing SQL Server query, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.
Ask SQL Server!
One of the advantages of SQL Server is that it can answer almost all your questions, because SQL Server stores a lot of troubleshooting information in various DMV and DMF. On the other hand, this is also a disadvantage, because you have to know the various DMV/DMF and how to interpret and relate them.
As for your worst performance SQL Server query, one of the most important DMV is sys.dm_exec_query_stats. For each cached execution plan, SQL Server stores details of the execution plan at run time. In addition, SQL Server tells you how much CPU time this query takes and how much time it takes to read it. When I troubleshoot SQL Server with poor performance, this is one of the basic DMV I often use.
Let's get into sys.dm_exec_query_stats!
When you do a simple SELECT query on sys.dm_exec_query_stats, you get a very extensive recordset with many different columns-a lot of different numbers.
Let's take a closer look at them. For each cached execution plan, SQL Server gives you the following metric information:
Worker Time (columns... _ worker time)
Physical Reads (columns... _ physical read)
Logical Writes (columns... _ logical write)
Logical Reads (columns... _ logical read)
SQLCLR Time (columns... _ common language runtime)
Elapsed Time (columns... _ run time)
Row Count (columns... _ rows)
For each metric, you get four different columns of aggregate information:
Total value (Total value)
Previous value (Last value)
Minimum (Min value)
Maximum (Max value)
With this information on hand, find out what your worst-performing query is. But first of all, you need to know what is your performance bottleneck-CPU or Igamot O limit? If your performance bottleneck is a CPU limit, you can use the following query to ask SQL Server to list the top 5 worst performance queries based on CPU consumption:
-- 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
You can see that here I use a simple ORDER BY total_worker_time DESC to return CPU-intensive queries. It also grabs the SQL statement and the execution plan itself by calling 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 Istroke O consumption.
-- Worst performing Imax 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 SQL statements and execution plans in front of you, you can further analyze the query to find out what causes the high CPU or IWeiO consumption.
On how to find out your worst performance of the SQL Server query to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.
