Michael Swart helps root out queries which get recompiled frequently and so won’t be in the cache:
Some of my favorite monitoring solutions rely on the cached queries:
- My own top 20 queries at http://michaeljswart.com/go/top20
- sp_blitzcache
- etc…
but some queries will fall out of cache or don’t ever make it into cache. Those are the dark queries I’m interested in today. Today let’s look at query recompiles to shed light on some of those dark queries that maybe we’re not measuring.
By the way, if you’re using SQL Server 2016’s query store then this post isn’t for you because Query Store is awesome. Query Store doesn’t rely on the cache. It captures all activity and stores queries separately – Truth in advertising!
Click through for an Extended Event session which looks for recompilation.