2

Enabling Query Store can have performance benefits, particularly as of SQL Server 2022, but what about the costs? The documentation insists that the costs are small, but I want proof on my own system. I can query the DMVs to find up how much disk space that it's using per database, but that's not enough. How can I measure the performance costs of enabling (and using) Query Store? Furthermore, what other costs should I be aware of?

This article is a good attempt, but it came out while Query Store was still in development. It is therefore presumably out of date. Indeed, these two articles by Erin Stellato suggest that significant performance upgrades have been made over time. However, they still say very little about what the costs actually are.

2 Answers 2

2
+50

Query Store

How can I measure the performance costs of enabling (and using) Query Store?

The documentation insists that the costs are small, but I want proof on my own system.

Then enable it on your own system. That's truly the only way to tell how it's going to affect it. If that can't be directly in the live system, then a replicated copy with similar test workloads applied to it, on an equally provisioned and configured server would be the next best way to test.

If it's too intensive, then you can easily disable it. Though it has been improved measurably since it was originally released. It's also the general recommendation to enable it on new databases too.

Monitoring SQL Server

There's a series of free tools out there that you should become familiar with to monitor your SQL Server instance, in general. Using these tools, you should be able to keep a close eye on how your server is performing before and after enabling Query Store:

  1. sp_WhoIsActive - A standard tool for seeing what's running on your server in the moment, how long it's been running for, if there's any blocking, what it's waiting on, and it's execution plan in addition to a bunch of other useful information.

  2. First Responder Kit - This is a series of tools to help bring to light your biggest issues. With some of them, you can see what's currently running on your server, how long it's been running for, what's holding it up, and get the live execution plans for them.

  3. sp_PressureDetector - This is a great tool to holistically see how your server is operating from a CPU and Memory perspective. It'll give you in depth information on what your biggest bottlenecks are in regards to Memory and CPU pressure.

You can run these adhoc as needed or setup a routine process such as a SQL Job that runs them and stores the results to some monitoring tables. Otherwise, a full on monitoring tool may be worth considering purchasing which will collect similar data on a continuous basis for you.

0
-1

The sys.dm_os_memory_clerks view contains types for Query Store.

SELECT *
FROM sys.dm_os_memory_clerks
WHERE [type] LIKE 'MEMORYCLERK_QUERYDISK%'
ORDER BY pages_kb

These show how much RAM Query Store is taking up (shown in KB in the pages_kb column). If these are some of your top memory consumers, then you're in trouble.

Another method is to monitor your wait statistics. This article is mandatory reading for that topic. The waits related to Query Store all have names starting with "QDS". If those appear high up in the output of the big query in that article, then you may have problems caused by Query Store.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.