Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Query Store SSMS Panes


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

One of the new features of SQL Server 2016 to learn is Query Store. In one of our last tips about Query Store we reviewed how to configure and access Query Store runtime statistics with SQL Server Management Studio (SSMS). We also provided examples of the Query Store usage and wrote about some useful queries.

In this tip, we will look at the other Query Store options within SQL Server Management Studio (SSMS).

Solution

In the previous Query Store tips we primarily used only one of the Query Store SSMS panes - "Top Resource Consuming Queries". In this tip we will review the remaining Query Store panes:

SSMS Panes

Tracked Queries in the SQL Server Query Store

There are several ways to open the "Tracked Query" pane.

Once you have a query you want to track highlighted in the "Top Resource Consuming Queries" pane or "Regressed Queries" pane you can click on the "Track the selected query..." button:

Tracking Query Button

If you know a query ID you can use the "Tracked Query" Query Store pane directly and continue monitoring this specific query. Open the "Tracked Query" pane from SSMS and enter the query ID you would like to track (1):

Tracking Query Pane

You can configure the time interval (2) different from the default one ("Last day").

You can also choose a different metric ("Duration" is the default):

Tracking Query Pane Configure

When you track a query you can refresh the results manually or set the pane to "Auto-update" (3). This will automatically update the pane every 5 seconds. Auto update is also configurable and you can set it to different intervals.

Similar to the "Top Resource Consuming Queries" pane you can compare plans, view the query or force selected plan here.

Regressed Queries Pane in SQL Server Management Studio

The "Regressed Queries" pane displays queries regressed for example in Duration (default) during the last week (default history interval):

Query Store Pane - Regressed Queries

Click on the "Configure" button and note the rest of the default configuration values:

Regressed Queries Default Configuration

In our case we want to change the configuration to show queries regressed in logical reads. We also want to show only queries that were executed at least 10 times:

Regressed Queries Configuration Change

Note the "Y Axis" dropdown metrics that are available.

The pane's Vertical View (as well as Grid View for the left part of the pane) will give you a little bit better presentation of the regressed query details:

Regressed Queries Grid view or vertical

Overall Resource Consumption

The last pane to review is the "Overall Resource Consumption" pane:

Query Store Pane - Overall Consumption Chart

By default it will display CPU time, Duration, Execution Count and Logical Reads for the last week aggregated by hour:

Query Store Pane - Overall Consumption Chart Configuration

When you switch to the Grid View you will be able to view all of the available metrics for the resource consumption grouped by the aggregation size selected in the configuration:

Query Store Pane - Overall Consumption Grid

From both - grid and chart view you can drill down to the details by double-clicking one of the values in the grid view or by clicking one of the bars in the chart view. The "Top Resource Consuming Queries" pane will be opened for the specified period of time (for the bar or row that was clicked).

In our example we clicked on the "Execution Count" area of the "Overall Resource Consumption" pane in the chart view. It opened the "Top Resource Consuming Queries" pane filtered by the execution count:

Top Resource Consuming Queries - chart view click

When you click on the "CPU Time" area of the "Overall Resource Consumption" pane in chart view it will open for you accordingly the "Top Resource Consuming Queries" pane filtered by the CPU Time, and so on for each area of the chart.

Now we will double-click on the grid view - on row #1 "total logical reads" value:

OverallConsumptionGrid - grid view click

The "Top Resource Consuming Queries" pane will open and it will be filtered by the total logical reads:

Top Resource Consuming Queries - grid view click

Note, that in this version you can not click on the "total execution count" in the grid view. This might be fixed in the RTM version of the SQL Server 2016.

Note as well, that the results in the "Top Resource Consuming Queries" pane will be filtered by the interval selected on the chart view or the grid view of the "Overall Resource Consumption" pane. So, if you configured the "Overall Resource Consumption" pane to display results by hour your results will be displayed for the selected hour.

You can confirm this by clicking on the "Configure" button under the details (in the "Top Resource Consuming Queries" pane):

Top Resource Consuming Queries - verify pane's configuration

Summary

In this tip we have reviewed the last three Query Store panes. We have reviewed the default configuration settings and provided explanations on what drill down options are available.

Note: This article was written based on SQL Server 2016 CTP 2.2. Some screens and behaviors may change in final release. For example, in SQL Server 2016 CTP 2.3 "Vertical View" and "Compact View" buttons were renamed to the "Portrait View" and "Landscape View" correspondingly.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools