How to Check the Query Performance in an SQL Server

 Posted on May 6, 2023  |  3 minutes  |  469 words  |  Mittie Cheatwood

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 2 of 3:

Finding the Top Resource-consuming Queries

  • Picture 9 of How to Check the Query Performance in an SQL Server Click the - next to ″Query Store″ in the Object Explorer. This displays all Query Store options.
  • Use this method to find out which specific queries are using the most server resources.[5]
  • Picture 10 of How to Check the Query Performance in an SQL Server

    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]

  • Picture 11 of How to Check the Query Performance in an SQL Server

    Click Configure. It's at the top-right corner of the report. A dialog window will appear.

  • Picture 12 of How to Check the Query Performance in an SQL Server

    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).

  • Picture 13 of How to Check the Query Performance in an SQL Server

    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.

  • Picture 14 of How to Check the Query Performance in an SQL Server 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).
  • If you want to stick to the top 25 worst offenders, there's no need to make any changes under the ″Return″ header.
  • Picture 15 of How to Check the Query Performance in an SQL Server

    Click OK. The stats will now refresh to display what you want to see.

  • Picture 16 of How to Check the Query Performance in an SQL Server

    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

  • Picture 17 of How to Check the Query Performance in an SQL Server Click the - next to ″Query Store″ in the Object Explorer. This displays all Query Store options.
  • Use this method to find specific queries that are performing more slowly than they used to.[7]
  • Picture 18 of How to Check the Query Performance in an SQL Server

    Click Regressed Queries. This opens the Regressed Queries panel, where you'll find the queries and plans in the Query Store.[8]

  • Picture 19 of How to Check the Query Performance in an SQL Server 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.
  • 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.
  • Picture 20 of How to Check the Query Performance in an SQL Server

    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.

    • Samuel Daniel Samuel Daniel

    Update 05 March 2020

    ncG1vNJzZmismaXArq3KnmWcp51ktbDDjK2mZpuYmrCsedOhnGappZq%2FunnPnqmfp6Kirq%2BvxGagp2WRo3q0vctmqp6qppq%2F