PDF download Download Article
Keep an eye on your database queries with the Query Store
PDF download Download Article

This wikiHow teaches you how to to use the SQL Server Query Store to monitor the performance of your database queries.

Things You Should Know

  • You can enable the Query Store via the SQL Server Management Studio.
  • Use the - button on the Query Store to pull up a menu where you can check the top resource-consuming queries.
  • You can also click ″Check for regression in″ in the - menu to look for regressed queries.
Part 1
Part 1 of 3:

Enabling the Query Store

PDF download Download Article
  1. 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. If you don’t see Object Explorer already, click the View menu at the top of the screen, then click Object Explorer . [2]
    Advertisement
  3. A menu will appear. [3]
    • The Query Store can’t be used to monitor the master or tempdb databases.
  4. It’s on the Properties dialog box.
  5. ″ The Query Store will begin tracking all queries.
  6. This adds the Query Store folder to the panel.
  7. 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]
    • ALTER DATABASE <database_name>
    • SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15); .
  8. Advertisement
Part 2
Part 2 of 3:

Finding the Top Resource-consuming Queries

PDF download Download Article
  1. This displays all Query Store options.
    • Use this method to find out which specific queries are using the most server resources. [5]
  2. 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. It’s at the top-right corner of the report. A dialog window will appear.
  4. 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. 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. 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 ).
    • If you want to stick to the top 25 worst offenders, there’s no need to make any changes under the ″Return″ header.
  7. The stats will now refresh to display what you want to see.
  8. 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.
  9. Advertisement
Part 3
Part 3 of 3:

Checking for Regressed Queries

PDF download Download Article
  1. This displays all Query Store options.
    • Use this method to find specific queries that are performing more slowly than they used to. [7]
  2. This opens the Regressed Queries panel, where you’ll find the queries and plans in the Query Store. [8]
  3. 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.
    • For example, if you want to check for issues with in the amount of time it takes for queries to run, select Duration .
    • To see issues relating to RAM usage, select Memory Consumption .
  4. This is the menu labeled ″Based on″ (to the right of the previous menu. This re-displays the results.
  5. Advertisement


Expert Q&A

Ask a Question
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Thanks for submitting a tip for review!

      About This Article

      Article Summary X

      1. Enable the Query Store in Management Studio.
      2. Refresh the database.
      3. Expand the Query Store folder.
      4. Click Top Resource Consuming Queries .
      5. Click Configure .
      6. Set your parameters.
      7. Click OK .

      Did this summary help you?
      Thanks to all authors for creating a page that has been read 42,206 times.

      Is this article up to date?

      Advertisement