2

The Plan Cache and the Query Store are not the same, even for the same query. When hunting for performance or monitoring information on a specific query or set of related queries, what are the advantages/disadvantages of each?

The general impression that my online research indicates is that the Query Store can be queried faster than the Plan Cache (I'm not sure why) and that its entries tend to last much longer (this is configurable), but I have found nothing said about situations where the Plan Cache is superior to the Query Store.

Assume that I do not care about the features that SQL Server 2017 and 2022 introduced that use Query Store for automatic performance tuning. Instead assume that I am comparing the Query Store and Plan Cache for the purposes of tasks that both can do.

1 Answer 1

6
+50

little bit

I generally prefer Query Store to the plan cache, since it's a more stable and reliable source for performance information. The plan cache clears out and loses plans for many reasons, and anyone ridiculous enough to enable optimize for ad hoc workloads can miss a significant amount of information.

The one thing that the plan cache has better detail on is query memory grants. Query Store only shows you how much memory a query used, but the plan cache also has details on how much was granted.

I've often found it useful to compare how much was granted vs. how much was used, both when tuning queries, and adjusting Resource Governor settings. Most servers I see with large amounts of memory have issues with the ~25% max memory grant allowance being far too high, and the plan cache is the only real built-in source for usage information like that. The one downside being that servers with a lot of memory churn can sometimes have short plan cache lifespans.

0

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.