SQL Server Management Studio (SSMS) has seven built-in reports for Query Store. These reports give us the ability to quickly view performance data and troubleshoot performance issues in a graphical interface and transition the data into a grid view. In this chapter, we will explore how to interact with these reports and how they complement each other and work together. A list of the reports can be found below the database in Object Explorer as seen in Figure 4-1:
Figure 4-1
Object Explorer view of reports for Query Store
Regressed Queries Report
The first report we will look at is the Regressed Queries report. This report displays information on which queries over the specified period have started degrading in performance. There are several things you can do to navigate around inside of these reports, but first let’s take a glance at this report in Figure 4-2.
Figure 4-2
Regressed Queries report
Each bar on the left-hand side represents a different query that could be part of a stored procedure, trigger, user-defined function or be an ad-hoc query that executed in the specified period of time. The default period of time for the report is the last hour. If you click on any of the bars, the query plan will change at the bottom to show the estimated plan for that query. If you hover over any of the bars, it will show you stats for each one of the queries the bar represents as seen in Figure 4-3.
Figure 4-3
Regressed Queries report hover over bar statistics
On the left-hand side on the top left-hand pane, you can control the type of regression that is shown to you by clicking the bar that defaults to additional duration. Other options are seen in Figure 4-4:
Figure 4-4
Additional options for types of regressions
In the bottom pane on any report that shows you a query plan, you will see a box with three dots in the upper right-hand corner; once you click on the box, it will take the query text and put in a Query Editor window as seen in Figure 4-5.
Figure 4-5
Button to open query test in a Query Editor window
Also, on the bottom pane, you have two buttons as seen in Figure 4-6 that allow to force and unforce plans based on which plan you have highlighted and shown in the pane.
Figure 4-6
Regressed Queries report force and unforce plan buttons
You will also see the estimated plan that the stored procedure, function, trigger, or ad-hoc query generated during the period of time of the reporting period. Similarly, as you run an estimated or actual plan in a Query Editor window of SQL Server Management Studio (SSMS), you can hover over each plan operator to see detail statistics, warnings, and the percentages of where the most work has taken place. You can right-click on the plan to get the same options to as you get in the Query Editor window in SSMS as seen in Figure 4-7.
Figure 4-7
Regressed Queries report plan options
Note
The bottom pane functions the same way across all reports that show query plans.
Overall Resource Consumption Report
The Overall Resource Consumption report shows the resources the database is consuming by default for the last month. The report defaults to showing four categories of performance in total amounts: duration in milliseconds, execution count, CPU time in milliseconds, and logical reads in kilobytes. Here is an example of a report shown in Figure 4-8:
Figure 4-8
Overall Resource Consumption report
If you double-click on any of the bars shown in the chart, you will automatically open the next report we will be talking about, the Top Resource Consuming Queries report. If you hover over any of the bars, you will get a list of statistics for the period of time for the bar as seen in Figure 4-9.
Figure 4-9
Overall Consumption Report hover statistics
In the top right-hand corner, you have four buttons that allow you to control the reports. You can see a close-up picture of these in Figure 4-10:
Figure 4-10
Overall Resource Consumption report buttons
The Refresh button will allow you to refresh the report on the screen. The Standard Grid button allows you to view the data in a grid view; see Figures 4-11, 4-12, and 4-13 for an example.
Figure 4-11
Overall Resource Consumption report standard grid view
Figure 4-12
Overall Resource Consumption report standard grid view
Figure 4-13
Overall Resource Consumption report standard grid view
The Standard Grid view contains a lot more columns than you get in the Standard View with charts. You can click on the column headings of any of the columns, and it will sort the columns and place an arrow showing which way it sorted the data. By default, it is sorted by the interval start date and time.
The Chart button allows you to switch back to the Chart View if you are in the Standard Grid View.
Finally, the Configure button allows you to control items on the Chart View and the Time Interval shown in either the Standard View or the Chart View. Figure 4-14 shows the options available under the Configure button.
Figure 4-14
Configure button options for Overall Resource Consumption reports
On the top half of the screen as of SSMS, Figure 4-14 shows you all the metrics that are available to be displayed in the Chart View. You can also use it to remove any item from the view.
The bottom half of the screen in Figure 4-14 applies to both the Chart View and Standard Grid View. You can change the Time Interval from the last month which is the default to a value seen in Figure 4-15 to view a different period of time.
Figure 4-15
Overall Resource Consumption report configure time interval drop-down
When you select Custom from the drop-down box, the From and To boxes will no longer be greyed and out and you will be able to edit the days by typing or selecting from a calendar. Next, there is a drop-down box for Aggregation Size where you can specify the intervals it rolls the data into for your viewing. Values in the drop-down are shown in Figure 4-16.
Figure 4-16
Aggregation Size drop-down
Finally, you can select if data is shown to you in your Local Time Zone time or UTC zone.
Tip
This is the best report for validating whether or not your system is meeting your expected baselines you have established and for seeing if something unusual is happening on the server.
Top Resource Consuming Queries Report
The Top Resource Consuming Queries Report by default shows the top 25 queries summed up by total duration for the last hour. In Figure 4-17, you can see an example of what this report looks like. Like the other reports we have discussed, there are many options we can look at the top of the report.
Figure 4-17
Top Consuming Resources report
At the top right-hand corner, we have three buttons that control the overall reports as seen in Figure 4-18. The Portrait View button will move the three separate panes stack on top of each other instead of having the two panes beside each other at the top and one on the bottom. The Configure button does the same as we have seen in the last report; refer to Figure 4-14.
Figure 4-18
Top Consuming Resources report buttons
When we look at Figure 4-19, we can see the following buttons to allow you to control the top left-hand corner of the screen.
Figure 4-19
Top Consuming Resource report option bar
First, we have the metric we want to see for the top 25 queries by in the drop-down listed below:
Execution count
Duration (ms) (default)
CPU time (ms)
Logical reads (KB)
Logical writes (KB)
Physical reads (KB)
CLR time (ms)
DOP
Memory consumption (KB)
Row count
Log memory used (KB)
Tempdb memory usage (KB)
Wait time (ms)
Then instead of having the statistics by total, you can change the statistics to the following values:
Avg
Max
Min
Std dev
Total (default)
The Refresh button will refresh the report to the current period of time specified. Next, you have a button that will let you jump to the Track Queries Report to look at the query highlighted. The button with magnifying glass takes the selected query’s text and pops it a new query window for you to view. The Grid View button will provide you with additional metrics to look for each query. See Figures 4-20, 4-21, 4-22, and 4-23 for an example of what the Grid View looks like.
Figure 4-20
Top Consuming Resources additional grid view
Figure 4-21
Top Consuming Resources additional grid view
Figure 4-22
Top Consuming Resources additional grid view
Figure 4-23
Top Consuming Resources additional grid view
Columns will vary in this view based on the statistic you choose to look at; for example, the figure is based on totals, but if you pick avg, the view will show averages. The columns in general that are included in figures above are as follows :
Query_id
Object_id
Object_name
Query_sql_text
Duration
CPU time
Logical reads
Logical writes
Physical reads
CLR time
DOP
Memory consumption
Row count
Log memory used
Temp db memory used
Wait time
Execution count
Plan count
The Regular Grid View button shows far fewer columns as seen in Figure 4-24. The Regular Grid View concentrates on the metric and statistic you are looking at in the chart rather than showing all the metrics. Note both views have the ability for you to change which metric and statistic you want to view the data inside the view without returning to the Chart View.
Figure 4-24
Top Consuming Resources regular grid view
The Chart View button allows you to return to Chart View if you have entered either of the Grid Views.
You can change what is shown on the y-axis and x-axis of the chart on the Chart View on the pane as well. For the x-axis you have the options available in Figure 4-25, and for the y-axis you have the options available in Figure 4-26.
Figure 4-25
Top Consuming Resources chart view y-axis options
Figure 4-26
Top Consuming Resources chart view x-axis options
The right-hand side of the screen shows you the plans and their IDs associated with each query; as you click on the query, this side of the screen adjusts along with the query plan shown at the bottom of the screen.
Also, in the first pane, if you hover over any of the bars for the queries, it will show you details about the query and the plan you currently have selected as seen in Figure 4-27.
Figure 4-27
Top Consuming Resources report query ID data
On the left-hand side of the left-hand pane, you can change how you group the data. You can use whichever metric you selected from the top drop-down (duration, CPU, logical reads, etc.), execution count, and plan count as seen in Figure 4-28. The most useful here is plan count so you can find queries that have multiple plans which will help you identify possible queries with plans that can be forced.
Figure 4-28
Top Consuming Resources report left-hand pane group by drop-down
On the left-hand pane in Portrait mode, there are a series of buttons that control what you may do with the plans being displayed for the selected query. Figure 4-29 shows you the buttons as we go through and explain what each one does.
Figure 4-29
Top Consuming Resources report right-hand pane buttons
You are already familiar with the Refresh button as it just refreshes the current report you are in. The next button forces the plan whichever plan is highlighted in the pane. The next button either tells you the plan is not forced or will unforce a plan if it has been forced previously. The next button will allow you to compare two plans if you click two of the dots in the pane below and hold the Ctrl key. Figure 4-30 shows an example of what comparing a plan looks like. The differences in the plan operators are highlighted in red in the plan diagram. Beside that, you will have what you see in Figure 4-31 which shows you more details about the differences in where the time is spent on each operation.
Figure 4-30
Top Consuming Resources report comparing plan operator differences
The differences seen in Figure 4-31 have a not equal sign highlighted in yellow.
Figure 4-31
Top Consuming Resources report comparing report details
The next button shows you data for the plans in the Grid View as seen in Figure 4-32. The final button returns the pane into the Chart View.
Figure 4-32
Top Consuming Resources report grid view of plan data
In the right-hand pane, when it is in Chart View, you can hover over the plan and get data about the plan. Three types of icons will appear in this pane. A dot with nothing inside of it represents an unforced plan, a dot with a check mark represents a forced plan, and a square represents a failed execution of the query. These metrics change based on the metric you have selected at the top left-hand corner of the report; for example, if you have CPU Time chosen, you would see CPU instead of Duration. In Figure 4-33 you can see what the statistics an example of the statistics shown when you hover over a icon in the pane for the period of time it represents.
Figure 4-33
Top Consuming Resources report hover over query plan details
Queries with Forced Plan Report
The Queries with Forced Plans report shows you the query plans that have been forced on your database. This report can be used to check back to make sure you still see the same performance improvement when you forced the plan or to see what plans have been forced on the database. A sample report can be seen in Figure 4-34:
Figure 4-34
Queries with Forced Plan report
There are not as many configuration options in this report, but let’s explore the buttons we do have available to us. The three buttons in the left-hand pane are familiar to us; the first is the refresh button. The second is to open this query in the Track Queries report. The last is to open the query text in the Query Editor window.
The right-hand pane has at the very top the same buttons from the Top Resource Consuming Queries report. One is to put the report in Landscape mode which stacks the panes. Another one is to change it back to its default Portrait mode. Then we have the Configure button which looks the same as the one in the Top Resource Consuming report in Figure 4-34. Underneath that, you have the Refresh button for the right-hand pane. There is a button to force a plan and a button to unforce a plan. Lastly is the button to compare plans.
In the bottom pane, you have the plan showed for the highlighted plan ID from the top right-hand pane and the buttons to force or unforce the plan selected as seen in Figure 4-34. On the left-hand side of the chart, you have the option to change the statistic that is used to display the dots which is defaulted to average; see Figure 4-35 for the other options.
Figure 4-35
Queries with Forced Plan report y-axis options
Just like in the Top Resource Consuming Queries report, you have the same options to look at the query plans.
Queries with High Variation Report
The Queries with High Variation report can indicate queries with parameterization problems. Parameterization occurs with a query that is executed with one value and is parameterized with a parameter then executed again with the different value and a different plan would be more appropriate due to data distribution. For example, if you were to execute a query looking for every living in Montana (MO), it would produce a plan searching based on statistics for a small data set than say if you search for everyone living in California (CA). An example of Queries with High Variation report is shown in Figure 4-36.
Figure 4-36
High Variation report
The few differences in this report with the Top Resource Consuming Queries Report are that under the Statistics drop-down, you only have two options: Variation and Standard Deviation, and there is no Configure button. When you hover over the bars, you receive more limited information as it only shows Variation and Standard Deviation for the metric you have selected as seen in Figure 4-37.
Figure 4-37
Queries with High Variation summary information
You have the option on the left-hand pane to change the statistics that are used to display values; options can be seen in Figures 4-38 and 4-39.
Figure 4-38
Queries with High Variation y-axis options
Figure 4-39
Queries with High Variation queries x-axis options
When you hover over the dots, you receive the information based on the metric selected for the query plan as seen in Figure 4-40.
Figure 4-40
Queries with High Variation plan summary information
You can also control the y-axis of the right-hand pane by selecting one of the options on the left-hand side as seen in Figure 4-41.
Figure 4-41
Queries with High Variation report plan summary y-axis options
Query Wait Statistics Report
The newest report introduced to SSMS is the Query Wait Statistics report. When you initially open the report, you get a report that shows you the total wait time by category. Query Store takes all the wait statistics and groups them into 23 categories such as CPU, memory, buffer IO, etc. Wait Statistics is a tried and true way of troubleshooting SQL Server performance and welcome addition to Query Store in SQL Server 2017. Each wait statistics category is documented in Chapter 9. An example of this report can be seen in Figure 4-42.
Figure 4-42
Query Wait Statistics report categories
The report defaults like all other reports to show wait statistics by totals, but you have the option at the top and to the left to change to the options as shown in Figure 4-43.
At the top of the screen, you have three familiar buttons. The first being the Refresh button, the second being the Standard Grid button which how that looks can be seen in Figures 4-45 and 4-46, and finally, the button to change back to the Chart View.
Figure 4-45
Query Wait Statistics report categories grid view
Figure 4-46
Query Wait Statistics report categories grid view
Once you click on one of the category bars displayed in the chart, you are shown a drilled down report of top five queries that consumed the most resources for that category as seen in Figure 4-47. This gives you the ability troubleshoot queries based on where your bottleneck appears to be based on wait statistics. If you see high CPU that would be the first on the report, then you can drill down and see the top five queries using CPU and tune those queries or see if there is a plan that can be forced that uses less resources.
Figure 4-47
Query Wait Statistics top five by category report
The top bar based on drop-down has some statistics you can use to control which top five queries show up in the report as seen in Figure 4-48.
Figure 4-48
Query Wait Statistics top five Queries Statistics
To the right of that is a set of buttons all of them should be familiar to us by now except for the green arrow. The green arrow takes us back to the Query Wait Statistics Categories Report that we drilled down from.
Like other chart views, you can change the y-axis and x-axis attributes that are displayed; see Figures 4-49 and 4-50 for what options you have on this screen.
Figure 4-49
Query Wait Statistics top five category y-axis options
Figure 4-50
Query Wait Statistics top five by Category x-axis options
On the right-hand side pane, you have the usual dotted report representing the different plans over the period time. The buttons at the top are the same ones on the Top Resource Consuming Resource Queries Report seen in Figure 4-13. You can also change the y-axis to different statistics from the default of total to one of the values in the drop-down as seen in Figure 4-51.
Figure 4-51
Query Wait Statistics top five by Category Plan y-axis options
Tracked Queries Report
The Tracked Queries Report is used to show runtime statistics for a particular query you are tracking and see all the execution plans for that query. An example of a Tracked Queries Report can be seen in Figure 4-52. This is most useful when you have previously identified a query by query ID that you want to watch and see how the performance changes over time.
Figure 4-52
Tracked Queries Report
On the top left-hand corner, you have two ways to find queries that you want to track. One is to type the query ID in the white box then hit the green arrow to load the data. The data by default is for the last day. The other is to click the magnifying glass in which it will pop a window where it has loaded all the queries that have been stored in Query Store as seen in Figure 4-53.
Figure 4-53
Tracked Queries Report magnifying glass view
From here you can select a query to track then click on OK after which you will need to hit the green arrow for it to load the data in the report. Hovering over the dots reveals the statistics for the period of time based on the metric you have configured to be shown which is the duration by default as seen in Figure 4-54.
Figure 4-54
Tracked Queries Report statistics details
At the top, you have a couple of new buttons we have not seen. The first is the Auto Refresh button which will as suspected auto refreshes the report every 5 seconds. The Auto Refresh setting is configurable under the Configure button. There is only one other configurable setting in that screen we have not seen, and that is which query to track which can change from there.
Conclusion
In this chapter we looked at the Regressed Queries, Overall Resource Consumption, Top Resource Consumption Queries, Queries with Forced Plans, Queries with High Variation, Query Wait Statistics, and Tracked Queries reports and how they interacted with each other. We explored all the options you had available to change what data is reported on the screen and how to configure each report to meet your needs. These reports are vital to quickly being able to troubleshoot issues with Query Store on your SQL Server instance.
