HomeProgramDatabase

How to Check the Query Performance in an SQL Server

This wikiHow teaches you how to to use the SQL Server Query Store to monitor the performance of your database queries. Open the SQL Server Management Studio. You can use the Query Store feature of SQL Server to monitor your database for...
Part 1 of 3:

Enabling the Query Store

  1. How to Check the Query Performance in an SQL Server Picture 1How to Check the Query Performance in an SQL Server Picture 1
    Open the SQL Server Management Studio. You can use the Query Store feature of SQL Server to monitor your database for performance issues. This feature is included with SQL Server 2016 and later, but you'll need to enable it manually.[1]
  2. How to Check the Query Performance in an SQL Server Picture 2How to Check the Query Performance in an SQL Server Picture 2
    Open Object Explorer. If you don't see Object Explorer already, click the View menu at the top of the screen, then click Object Explorer.[2]
  3. How to Check the Query Performance in an SQL Server Picture 3How to Check the Query Performance in an SQL Server Picture 3
    Right-click the database you want to check. A menu will appear.[3]
    1. The Query Store can't be used to monitor the master or tempdb databases.
  4. How to Check the Query Performance in an SQL Server Picture 4How to Check the Query Performance in an SQL Server Picture 4
    Click Properties.
  5. How to Check the Query Performance in an SQL Server Picture 5How to Check the Query Performance in an SQL Server Picture 5
    Click Query Store. It's on the Properties dialog box.
  6. How to Check the Query Performance in an SQL Server Picture 6How to Check the Query Performance in an SQL Server Picture 6
    Select On under ″Operation Mode (Requested).″ The Query Store will begin tracking all queries.
  7. How to Check the Query Performance in an SQL Server Picture 7How to Check the Query Performance in an SQL Server Picture 7
    Refresh the database in the Object Explorer panel. This adds the Query Store folder to the panel.
  8. How to Check the Query Performance in an SQL Server Picture 8How to Check the Query Performance in an SQL Server Picture 8
    Customize when the Query Store aggregates new data. The Query Store will aggregate new stats every 60 minutes by default. Here's how to change the interval (using 15 minutes as an example):[4]
    1. ALTER DATABASE
    2. SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);.
Part 2 of 3:

Finding the Top Resource-consuming Queries

  1. How to Check the Query Performance in an SQL Server Picture 9How to Check the Query Performance in an SQL Server Picture 9
    Click the - next to ″Query Store″ in the Object Explorer. This displays all Query Store options.
    1. Use this method to find out which specific queries are using the most server resources.[5]
  2. How to Check the Query Performance in an SQL Server Picture 10How to Check the Query Performance in an SQL Server Picture 10
    Click Top Resource Consuming Queries. This opens a graph that displays the 25 most resource-consuming queries in the database. These results are pretty broad, but you can further customize the graph to get more helpful information.[6]
  3. How to Check the Query Performance in an SQL Server Picture 11How to Check the Query Performance in an SQL Server Picture 11
    Click Configure. It's at the top-right corner of the report. A dialog window will appear.
  4. How to Check the Query Performance in an SQL Server Picture 12How to Check the Query Performance in an SQL Server Picture 12
    Select your resource consumption criteria. In the top section (″Resource Consumption Criteria″), choose which resource you want to check (e.g., CPU Time, Memory Consumption), and the desired statistic (e.g., Avg, Total).
  5. How to Check the Query Performance in an SQL Server Picture 13How to Check the Query Performance in an SQL Server Picture 13
    Select a time period. Under the ″Time Interval″ section, choose the time period for which you want to view results. You can select an option from the drop-down menu or insert specific dates into the provided boxes.
  6. How to Check the Query Performance in an SQL Server Picture 14How to Check the Query Performance in an SQL Server Picture 14
    Choose how many results to see. To see all queries for the selected time period, click All under the ″Return″ header. To display a specific number of queries, select Top and enter a number (e.g. 10, 100).
    1. If you want to stick to the top 25 worst offenders, there's no need to make any changes under the ″Return″ header.
  7. How to Check the Query Performance in an SQL Server Picture 15How to Check the Query Performance in an SQL Server Picture 15
    Click OK. The stats will now refresh to display what you want to see.
  8. How to Check the Query Performance in an SQL Server Picture 16How to Check the Query Performance in an SQL Server Picture 16
    Adjust the view (optional). Use the small graph icons (the grid, chart, and bar graph) to view the results in various formats. These buttons are at the top-right corner of the results.
Part 3 of 3:

Checking for Regressed Queries

  1. How to Check the Query Performance in an SQL Server Picture 17How to Check the Query Performance in an SQL Server Picture 17
    Click the - next to ″Query Store″ in the Object Explorer. This displays all Query Store options.
    1. Use this method to find specific queries that are performing more slowly than they used to.[7]
  2. How to Check the Query Performance in an SQL Server Picture 18How to Check the Query Performance in an SQL Server Picture 18
    Click Regressed Queries. This opens the Regressed Queries panel, where you'll find the queries and plans in the Query Store.[8]
  3. How to Check the Query Performance in an SQL Server Picture 19How to Check the Query Performance in an SQL Server Picture 19
    Select a plan from the first drop-down menu. It's the menu labeled ″Check for regression in″ above the top-left corner of the panel. Each of the plans will display a different graphical performance report for your queries.
    1. For example, if you want to check for issues with in the amount of time it takes for queries to run, select Duration.
    2. To see issues relating to RAM usage, select Memory Consumption.
  4. How to Check the Query Performance in an SQL Server Picture 20How to Check the Query Performance in an SQL Server Picture 20
    Select a statistic from the second drop-down menu. This is the menu labeled ″Based on″ (to the right of the previous menu. This re-displays the results.
5 ★ | 1 Vote
queriesstorequeryclick

You should read it

May be interested

Database