© Tracy Boggiano and Grant Fritchey 2019
T. Boggiano, G. FritcheyQuery Store for SQL Server 2019https://doi.org/10.1007/978-1-4842-5004-4_4

4. Standard Query Store Reports

Tracy Boggiano1  and Grant Fritchey2
(1)
Cary, NC, USA
(2)
Grafton, MA, USA
 
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:
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig1_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig2_HTML.jpg
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 .
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig3_HTML.jpg
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:
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig4_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig5_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig6_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig7_HTML.jpg
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:
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig8_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig9_HTML.jpg
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:
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig10_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig11_HTML.jpg
Figure 4-11

Overall Resource Consumption report standard grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig12_HTML.jpg
Figure 4-12

Overall Resource Consumption report standard grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig13_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig14_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig15_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig16_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig17_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig18_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig19_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig20_HTML.jpg
Figure 4-20

Top Consuming Resources additional grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig21_HTML.jpg
Figure 4-21

Top Consuming Resources additional grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig22_HTML.jpg
Figure 4-22

Top Consuming Resources additional grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig23_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig24_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig25_HTML.jpg
Figure 4-25

Top Consuming Resources chart view y-axis options

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig26_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig27_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig28_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig29_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig30_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig31_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig32_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig33_HTML.jpg
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:
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig34_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig35_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig36_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig37_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig38_HTML.jpg
Figure 4-38

Queries with High Variation y-axis options

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig39_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig40_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig41_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig42_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig43_HTML.jpg
Figure 4-43

Query Wait Statistics report categories reporting options

At the bottom of the Chart View, you also can change the x-axis on the chart to one of the options in the drop-down as seen in Figure 4-44.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig44_HTML.jpg
Figure 4-44

Query Wait Statistics report categories x-axis options

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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig45_HTML.jpg
Figure 4-45

Query Wait Statistics report categories grid view

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig46_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig47_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig48_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig49_HTML.jpg
Figure 4-49

Query Wait Statistics top five category y-axis options

../images/473933_1_En_4_Chapter/473933_1_En_4_Fig50_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig51_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig52_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig53_HTML.jpg
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.
../images/473933_1_En_4_Chapter/473933_1_En_4_Fig54_HTML.jpg
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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.117.35