Applies to
ApexSQL Plan
Summary
This article describes the Query store dashboard feature and all graphical pans that this feature presents.
Description
This feature gives a quick graphical overview of top queries based on average duration, execution count, executed rows, wait types etc. With the query store dashboard, it is possible to easily drill down to review query details and export desired diagrams.
All graphical panels are located under the Dashboard tab of the Query store window. But before seeing any of data and graphic in the Dashboard tab, first the Query store feature needs to be enabled; otherwise the following “No data was collected for this diagram” text will appear in all graphical panes:
More about the ApexSQL Plan Query store can be seen on the Query store KB article.
Once the Query store is enabled, it will start to collect a query runtime execution statistic and wait statistics information. The collected data will be graphically shown in different diagrams under the Dashboard tab:
On the top left side of the Dashboard tab, next to the Refresh button, the combo box is located, which contains items for different view of the Query store dashboard diagrams:
There are four different views of showing dashboard diagrams:
- Hourly overview
- Resource usage overview
- Wait statistics overview
- All
Hourly overview
This overview shows data in four diagrams. The first diagram shows top 10 queries, based on average duration in the last few hours:
By default, the diagram is set to show average duration in the last hour. That value can be changed and set to higher value by setting the value in the Interval selection box. The Min value is 1 hour and the max value is 24 hours:
The next diagram under the Hourly overview is the diagram which shows the top 10 queries that are most executed in the particular set of time:
Left side of the diagram shows number of executions and, on the bottom of the diagram, top 10 query ids are shown. Hover over the vertical blue bar and the tooltip with query id and exact execution counts will be displayed:
The next diagram shows the numbers of unique queries that are executed in the last hours. This chart does not show a total number of executions but the number of distinct, queries that have been executed in the specific set of time, which is set in the Interval selection box:
The last diagram on under the Hourly overview shows top 10 queries based on executed rows:
Left side of the graph shows the number of the rows and on the bottom, query ids are displayed
The blue and green vertical bars can be removed from the chart diagram by clicking the Average rows or Maximum rows button on the top left side of the diagram. In this way, the graph can be cleaned and information of interest can be left in the graph (e.g. Maximum rows):
Resource usage overview
In this section, four graphs are displayed which show performance metrics of all queries that are collected over the collection time interval that is set in the Interval selection box:
By default, the collection interval is set to the last eight hours.
The first three graphs show the maximum and average values for worker time consumption, Logical Reads/Writes and Elapsed query time.
The fourth graph shows the queries with multiple execution plans. The left side of the graph shows how many queries have execution plans and, on the bottom, query ids are displayed:
Wait statistics overview
By default, this section shows the waits status of the last 8 hours. It shows top 10 wait types, top 10 queries with the longest wait time, total wait time per interval in the last 8 hours. The last graph shows top 10 longest wait types:
When hover over the vertical bar, the tooltip with additional information for particular wait time will be displayed:
More about wait types can be found in the SQL Server wait types page.
All
With this view, all dashboard diagrams will be shown at once:
The diagrams can be exported, saved locally as an image, right mouse click on the desired diagram and, from the context menu, choose the Export diagram command or use the Export all diagrams command if want to save all diagrams at once:
On the Save dashboard window in the File name box, give the diagram appropriate name, under the Save as type, choose the desired format for the image and press the Save button:
The saved image will look something like this:
The minimum permission needed for reviewing the diagrams under the Dashboard tabs is the VIEW DATABASE STATE permission:
USE AdventureWorks2014 GO GRANT VIEW DATABASE STATE TO [USER];
FAQs:
Q: I have VIEW DATABASE STATE permissions, but when I use the Wait statistics overview, the following warning message appears:
Can’t refresh diagrams. Reason: VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
The user does not have permission to perform this action.
A: For reviewing the diagram for the top 10 longest wait types under the Wait statistics overview section, VIEW SERVER STATE permission is needed:
USE Master GO GRANT VIEW SERVER STATE TO [USER]
A reason for this is because ApexSQL Plan uses some Dynamic management views and functions (DMV) for which the VIEW SERVER STATE permission is needed in order to show information for the top 10 longest wait types diagram:
