What Kind Of Statistics Updates Invalidate Plans?

Basics

If you update statistics when the underlying objects haven’t been modified, plans won’t be invalidated.

That makes total sense if your statistics update doesn’t also change the statistics.  But what about when they do?

That seemed like a no-brainer to me. What if you used a higher sampling percentage and got a more accurate (or just different) histogram?

It turns out that doesn’t always trigger a recompile. At least not according to Extended Events.

Setup

This is the XE session I’m using. The settings aren’t very good for tracking recompiles generally in production.

You wouldn’t wanna use no event loss and a 1 second dispatch latency.

Just an FYI, copy and paste cowboys and girls.

Here’s the table I’m using, which is simple enough.

First, I’m going to create some statistics with a really low sampling rate.

It’s going to be the only stats object on the table.

If I run this query, the plan will compile.

Then update stats with FULLSCAN and re-run the query above…

And my Extended Event session is empty. Unless I create stats on a column my query isn’t touching.

Because I know you’re going to ask — yes, the histogram is different.

Apparently this doesn’t change SQL Server’s view of things.

Before

After

When Does It Change?

I’m starting to really hate trivial plans (more). If I change my query to this:

Updating the statistics with FULLSCAN, after creating the statistics and running the query, a recompile is triggered.

WHY YOU

Stored Procedures, Too

It’s not just the Trivial Plan, it’s also the Simple Parameterization, which means…

Even with a stats update using FULLSCAN, this won’t recompile.

Unlike the ad hoc query, this won’t recompile if I create an unrelated statistics object.

Using a more complicated example in Stack Overflow results in the same thing.

What Does This Mean For You?

When you update statistics and data hasn’t changed, your plans won’t recompile. This is sensible.

When you update statistics and change your histograms, your plans may not recompile if they’re trivial and simple parameterized, or parameterized in a stored procedure.

This is perhaps less sensible, if you were counting on stats updates to trigger a recompilation because you’re trying to fix parameter sniffing, or another plan quality issue.

Thanks for reading!

Previous Post
What’s Different About SQL Server in Cloud VMs?
Next Post
Free SQL Server Training Next Week at GroupBy

2 Comments. Leave new

  • Sounds like they need to add a RECOMPILE option to the UPDATE STATISTICS to cause plans to recompile.

  • Henrik Staun Poulsen
    August 31, 2018 1:54 am

    I think this explains some of my problems on a big database. I ended up using OPTION (RECOMPILE) which is less than optimal on most systems. I also used EXEC SP_Recompile ‘object’ to ensure that I would get a refresh.
    Thank you very much for blogging about this.

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