As most of you might know, I am a big fan of the Query Store feature inside SQL Server 2016. There is however one feature I dearly miss, the ability to customize or build reports underneath the Query Store folder in SQL Server Management Studio.

Even though the built-in reports are pretty decent for seeing the information that is stored in the Query Store, they don’t always show the information I am after or it takes quite a few clicks to drill down to the information I want. In other cases I had to resort to the Query Store DMVs to extract the runtime metrics I was after. Even though the work involved in getting the information I wanted wasn’t extremely time consuming I decided it should be far easier and faster so I present to you the “Query Store Dashboard”!

Look at this glorious piece of reporting! If the nice graphs don’t convince you, maybe the fact that you can run this as a custom report inside SSMS will!

The entire report is built as a Reporting Services report (.rdl) and can be imported directly into SSMS. Even better, you can use the Query Store Dashboard yourself by following the instructions on this page: http://www.dotnine.net/sdm_downloads/custom-query-store-performance-dashboard/ it’s 100% free.

Let’s look at some of the information that is available in this Query Store Dashboard.

The top row of graphs display some hourly information. From left to right:

• The top 10 queries in the last hour based on average runtime duration.
• The top 10 queries in the last hour based on the amount of times the query has been executed in the last hour.
• The total number of unique queries executed in the last hour.
• The total number of unique execution plans generated in the last hour.

Using the top row of graphs should give you some insight at how many queries and execution plans are being processed by the query store and also see some of the “heavy” queries that ran in the last hour. One thing to note is that the query text itself is not returned by the graphs, instead the “query ID” is shown to identify the query. Keep in mind this is the query ID the Query Store assigns to the query so it should only be used when querying the Query Store DMVs.

The second row shows two different performance graphs. The first one “CPU Time consumption last 8 hours grouped by intervals” returns the CPU usage of all the queries that are stored in the Query Store for the last 8 hours. The information is grouped based in the interval you configured when enabling the Query Store (default 1 hour). The way the information is presented is in an area graph. The “area” consists of the minimum and maximum CPU usage of all the queries that have been executed in the interval. The dotted line that runs through the area is the average CPU usage of all the queries during that interval segment.

The other graph is practically identical to the CPU Time graph but it shows logical reads and writes performed by every query for the last 8 hours.

The third row inside the report shows queries that have a forced execution plan set through the Query Store. It returns the internal Query Store query and plan id as well as the first 200 characters of the query statement. The table will also show you if the plan forcing failed and for what reason and how many times it failed to force the specific execution plan.

Finally, the fourth row shows a bit about the Query Store configuration for the specific database. The gauge graph returns the amount of space the Query Store is using and the table next to it show how you configured the Query Store for this specific database.

Interested in trying out the Query Store Dashboard yourself? You can download the .rdl file and view the installation instruction on this page: http://www.dotnine.net/sdm_downloads/custom-query-store-performance-dashboard/

The Query Store Dashboard is still a work-in-progress for me. This means I will add additional functionality to the report in the future. If you find any bugs or issues when using the report, please let me know! Fill in the contact form on this website or send me a message through Twitter at @evdlaar.

If you are interested in viewing Query Store performance data on an instance wide level, check out the brand new “Query Store Instance Dashboard“!

Enjoy!