How to find your worst-performing SQL Server query

Source: Internet
Author: User
Tags 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 you will be given some information guides 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  Span style= "COLOR: #0000ff" >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 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,5Qs.*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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.