In SQL Server there are a lot of DMV/Fs and other options which allow me to get information about query plans that are in cache, but I can't find any method to get information about all query plans, not just ones saved in cache. Can you, please, show how can I explore all query plans such as a number of times it has been used, its size, etc.?

  • 1
    I'm confused as to what you mean - where else would query plans be stored other than the cache? – George.Palacios Oct 18 '18 at 9:15
  • Cache holds plans only for actively used queries, less used queries fall out of cache usually in case of memory shortage @George.Palacios – ElGrig Oct 18 '18 at 9:25
  • You can even manually flush the cache – ElGrig Oct 18 '18 at 9:27
  • 3
    @EleonoraGrigoryan, check out query store if you are using SQL Server 2016+. – Dan Guzman Oct 18 '18 at 9:58
  • 3
    There are also monitoring tools that - among all the other things they provide - store plans over time, regardless of their current cache status, for all supported SQL Server versions, without having to be turned on per database, and without storing all that performance data in each database. Worth a look. Disclaimer: I work for SentryOne. – Aaron Bertrand Oct 18 '18 at 13:20

As Dan noted, you can use Query Store on SQL Server 2016+.

If you're on an earlier version, you can try Open Source Query Store, though I don't have any experience with using it.

As a last resort, you could log sp_BlitzCache to a table. There are instructions on doing that in the README. I'm not going to post them here. Fair warning: I contribute to that open source project.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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