T-SQL Tuesday #124 – Monitoring Query Store’s Impact on Your System

This month’s T-SQL Tuesday blogging party is brought to you well by me and I wanted to talk more about Query Store.  I did write a book on it but there if still more to know about that is not in the book.  I am sure the rest of everyone’s posts will prove enlightening and provide valuable content for folks using or looking to implement Query Store.  Someone should have told Grant to hold off a week on his post about DROP / CREATE of procedures and what happens with plan forcing so it could officially be part of the party.

I frequently get asked while presenting about the impact of running Query Store on the instance and one thing that was not in the book was the performance counters that were added to help track just that.  When Microsoft introduce Query Store they gave us four performance counters:

1. Query Store CPU Usage
2. Query Store Logical Reads
3. Query Store Logical Writes
4. Query Store Physical Reads

Each one of these states can be tracked as a total for the instance or tracked down to each individual database.  See this print screen to see how it looks in Performance Monitor to select them.

I’ve taken an update the Telegraf solution to monitor this on my demo environment so here are is the information needed for that as well.  In your telegraf.conf add the following to capture the metrics per database.

[[inputs.win_perf_counters.object]]
   ObjectName = "SQLServer:Query Store"
   Instances = ["*"]
   Counters = ["Query Store CPU Usage", "Query Store logical reads", "Query Store logical writes", "Query Store physical reads"]
   Measurement = "sql_qs"

Once we have this we can build a nice charts into our Instance Overview dashboard to show us the data.  I managed to drive a little bit of workload through my random workload generator but not much.  Got the CPU to say 1% there for a second.

In short, here is a easy to see what impact Query Store is having on the performance on your instance and narrow it down to which database is taking up the most resources.  There are things you can look at well but I’ll save that for another blog post.  The Instance Overview chart will be updated on my GitHub to have the Query Store panel if you happen to be using Telegraf and want to try this out.

I’m looking forward to reading everyone else’s contributions.