[Video] How to Find Queries Using OPTION RECOMPILE (And Their Parameters)

For years, I hated troubleshooting servers with high CPU usage caused by queries constantly asking for new execution plans. Hated it. SQL Server just doesn’t make it easy to find queries with recompile hints.

Then Erik Darling’s sp_HumanEvents came along.

And now troubleshooting frequent compilations is as easy as this:

Which produces this:

ARE YOU KIDDING ME? It gives me the parameters, the number of times the query compiled, how much time was spent doing those compilations, and more! It’s a SINGLE LINE OF T-SQL that sets up an Extended Events session, waits, tears it back down, and then lays out the output in a ridiculously easy-to-consume format!

Or, say you’re facing frequent re-compilations due to stats changes in high-turnover tables. No problem:

Here, I’m running a workload that causes a stats update in the middle of the workload, which triggers a recompile of a normally stable stored procedure:

It doesn’t get any easier than that. These are by no means the only tricks up sp_HumanEvents’ sleeve, either: it has all kinds of parameters to help you track down issues that would usually involve a lot of work.

If you prefer moving pictures with audio gibberish, here’s a live stream of me setting up the demo for this blog post:

Enjoyed this session? Follow me on TwitchYouTube, or Facebook to be alerted whenever I’m streaming. Here’s the code I used with the Stack Overflow database in the live session:

 

Previous Post
A Single Database View Cost Me $50/Day for Months.
Next Post
What Was the Worst Database Decision You Ever Made?

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}