Last week I shared my custom Query Store Dashboard that can be used to view additional performance information that isn’t always directly available through the built-in Query Store reports.
The dashboard I shared last week returns information on a per-database basis by right-clicking a database with the Query Store enabled and selecting the Query Store Dashboard custom report. But let’s say we are also interested in viewing Query Store data on a SQL Server Instance level. In that case the dashboard isn’t very usable since you would have to combine/aggregate/calculate the dashboard results of all the databases that have the Query Store enabled in your SQL Server Instance yourself. Let me save you the trouble of all that manual work and share with you the Query Store Instance Dashboard!
If you liked the Query Store Database Dashboard (I renamed the dashboard so the difference between both dashboards is clear) I shared last week, you’ll love this additional dashboard!
The Query Store Instance Dashboard will provide you with Query Store information that is not yet available through any of the built-in reports, and provides detailed performance information for the entire SQL Server Instance and per-database. For instance, if you want to see the amount of logical reads queries used for every Query Store enabled database this is the dashboard you’ll want to use. Not only does it show you the amount of logical reads per-database but it also returns the grand total so you can see directly what database has the most impact on the logical reads.
Just like the Query Store Database Dashboard the Query Store Instance Dashboard visualizes a large part of its data in easy to read graphs, saving you the time to build the graphs yourselves and helping you detect abnormal behavior quicker. Let’s look at the information provided by the Query Store Instance Dashboard!
A trip through the Query Store Instance Dashboard
The top row returns information about the Query Store configuration inside your SQL Server Instance. In the first graph you can see how many databases have the Query Store feature enabled. All of the reporting in this dashboard is based on databases that have the Query Store enabled. The second graph shows the amount of unique queries stored in the Query Store per database. Finally, the table at the right shows parts of the Query Store configuration per database. The most interesting part of the table is the data bar underneath “Storage”. This data bar shows the current storage usage of the Query Store for that specific database. Using the data bar you can quickly identify if the Query Store is running out of space for a database.
The second section of the Query Store Instance Dashboard digs deeper into the runtime metrics stored in the Query Store. Here you can see graphs that detail CPU Time, Nr. Of plan executions and logical reads/writes all shown per-database and including a Total so you can easily see which database is responsible for the total instance load.
The final section in the dashboard returns the top 5 longest running queries based on average runtime duration per database. The table also includes the compatibility level used by the query and the first 200 characters of the query text. The Query ID column shows the ID the Query Store assigned to this query so you can easier grab information from the Query Store DMVs if you want to dig deeper for this specific query.
Installation and usage
Just like the Query Store Database Dashboard, the Query Store Instance Dashboard is available for download through my GitHub page located here: https://github.com/Evdlaar/QueryStoreDashboard.
Inside the wiki on the GitHub page are the installation and usage instructions for both the dashboards.
All of the dashboards are 100% free and you can customize them any way you want. If you happen to run into any bugs or issues, let me know and I’ll fix them as soon as possible!
