SQL Server Community Tools: Capturing Which Queries Are Recompiling And Why With sp_HumanEvents

Posted on by Erik Darling

Classic Espionage

Like query compilations, query recompilations can be annoying. The bigger difference is that even occasional recompiles can introduce a bad query plan.

If your monitoring tools or scripts are warning you about high compiles or recompiles, sp_HumanEvents can help you dig in further.

We talked about compilations yesterday (and, heck, maybe I should have added that point in there, but hey), so today we’ll talk about recompilations.

There are a lot of reasons why a query might recompile:

  • Schema changed
  • Statistics changed
  • Deferred compile
  • Set option change
  • Temp table changed
  • Remote rowset changed
  • For browse permissions changed
  • Query notification environment changed
  • PartitionView changed
  • Cursor options changed
  • Option (recompile) requested
  • Parameterized plan flushed
  • Test plan linearization
  • Plan affecting database version changed
  • Query Store plan forcing policy changed
  • Query Store plan forcing failed
  • Query Store missing the plan
  • Interleaved execution required recompilation
  • Not a recompile
  • Multi-plan statement required compilation of alternative query plan
  • Query Store hints changed
  • Query Store hints application failed
  • Query Store recompiling to capture cursor query
  • Recompiling to clean up the multiplan dispatcher plan

That list is from SQL Server 2022, so there are going to be some listed here that you might not see just yet.

But let’s face it, the reasons you’re gonna see most often is probably

  • Schema changed
  • Statistics changed
  • Temp table changed
  • Option (recompile) requested

Mad Dog

To capture which queries are recompiling in a certain window, I’ll usually do something like this:

EXEC sp_HumanEvents 
    @event_type = 'recompiles',
    @seconds_sample = 30;

Sometimes recompiles can be good:

  • Schema changed: use a new index that suits the query better
  • Statistics changed: use newer statistics that more accurately reflect column data
  • Temp table changed: use a new histogram for a temp table more relevant to the query
  • Option (recompile) requested: burn it flat, salt the earth

But of course, there’s always an element of danger, danger when a query starts using a new plan. What if it sucks?

To cut down on recompiles, you can use this stuff:

  • Plan Guides
  • Query Store forced plans
  • Keep Plan/KeepFixed Plan query hints
  • Stop using recompile hints?

One thing that can be a pretty big bummer about recompiles is that, if you’re relying solely on the plan cache to find problem queries, they can leave you with very little (or zero) evidence about what queries are getting up to.

Query Store and some monitoring tools will capture them, so you’re better off using those for more in-depth analysis.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts