Query Store Instance Dashboard

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!

querystoreinstancedashboard

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

qsi_blog_01

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.

qsi_blog_02

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.

qsi_blog_03

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: //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!

22 comments to “Query Store Instance Dashboard”

You can leave a reply or Trackback this post.
  1. I think that what you posted was actually very reasonable.
    However, what about this? suppose you added a little content?
    I am not saying your information isn’t solid, however what if you added a
    title that makes people want more? I mean Query Store Instance Dashboard – DotNine is
    kinda boring. You might peek at Yahoo’s front
    page and note how they write post titles to
    grab viewers interested. You might add a video or a related pic or two to get readers interested about what you’ve written. Just my opinion, it
    would bring your posts a little livelier.

  2. You are so cool! I don’t think I’ve read through anything like that before.
    So great to discover somebody with some genuine thoughts on this topic.
    Seriously.. many thanks for starting this up. This website
    is something that is required on the web, someone with a bit
    of originality!

  3. Nice post. I was checking constantly this blog and I am impressed!
    Extremely helpful information particularly the remaining part :) I maintain such information a lot.
    I was looking for this particular info for
    a long time. Thank you and best of luck.

  4. When someone writes an paragraph he/she retains the thought of a user in his/her mind that how a user can know it.
    So that’s why this piece of writing is perfect.
    Thanks!

  5. I do accept as true with all of the ideas you’ve introduced to your post.
    They’re very convincing and will certainly work.
    Still, the posts are very short for beginners. May just you please prolong them
    a bit from next time? Thanks for the post.

  6. Aw, this was an exceptionally nice post. Taking a few
    minutes and actual effort to create a good article… but what can I say… I hesitate a whole lot and never seem to get anything done.

  7. I was recommended this blog by means of my cousin. I am now not sure whether this
    submit is written by way of him as no one else realize such certain approximately my difficulty.
    You’re incredible! Thanks!

  8. You undoubtedly make that seem convenient with your display but I actually find this sort
    of matter to get actually a thing that I think We might never find out.
    It seems too complex and intensely broad in person. I am looking
    forward to your impending post, I can try to get utilized to it!

  9. I like reading a paper that can receive visitors to think.
    Furthermore, many thanks to get allowing for myself to straight forward analysis!

  10. Hello there I am so delighted I found your webpage, I really found you by error, while I
    was searching on Google for something else, Nonetheless I
    am here now and would just like to say thank you for a marvelous post and a all round exciting blog (I also
    love the theme/design), I don’t have time to look over it
    all at the moment but I have bookmarked it and also added your
    RSS feeds, so when I have time I will be back to read more, Please do keep up the great work.

Write a Reply or Comment

Your email address will not be published.