Changing Statistics Cause Longer Compilation Times

Execution Plans, Statistics
18 Comments

I need to be up front with you, dear reader, and tell you that you’re probably never going to need to know this.

I try to blog about stuff people need to know to get their job done – things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server. This, however, is not one of those things. This is fun, obscure trivia, documenting a situation you’ll probably never run into, prompted by Forrest’s post proving that compilation time includes more than parse time.

Start with a hard-to-compile query plan.

I’ll use any Stack Overflow database and the hard-to-compile query plan from my post, Bad Idea Jeans: Building Big Query Plans, modified to join to the Users table:

Run the query, and check the Messages tab:

That complex query plan is hard to build, so it took some time:

The table variable doesn’t have any rows in it, though, so execution time is nearly instantaneous. The hard part here is clearly building the query plan.

But it gets much worse.
Try updating stats while it runs.

In one window, free the plan cache and start rebuilding the indexes on the Users table with online = on so the CTE query can execute:

Then in another window, run the CTE query again. Make a pot of coffee or hit the loo, because you’re gonna have some time. Several minutes later, both queries finish, and the output of the CTE’s statistics is pretty bizarre:

What’s happening? Every time the Users rebuild finishes, it’s also updating stats. The query compilation sees those updated stats, and…decides that the plan it just got done building is invalid, so it’d better go try to build another plan! Thus, the repeated messages about parse & compile time.

Yes, I actually hit this problem.

It sounds crazy, but to recreate a client issue, I was loading a 10 billion row table, which takes a while. As it was loading, its statistics were constantly changing, because as it grew, it was constantly hitting the automatic stats updates threshold.

As a result, simple queries with no where clause or order by could finish instantly, but add in even the simplest where clause, and the query would never finish. It was constantly recompiling as more data was loaded.

Shout out to Joe Obbish and Paul White who helped me troubleshoot this problem last year – all credit for solving the mystery goes to them:

Previous Post
What’s New & Undocumented in SQL Server 2019 Cumulative Update 9
Next Post
How to Trace a Nested Stored Procedure Without Using Profiler

18 Comments. Leave new

  • I actually ran into this issue last week. I was trying to get a few key statistics uptodate as the customer could dump a large amount of orders in the system at any time and we wanted to have the statistics updated at that point to make sure the queries were aware of that.
    We overdid it a bit though and kept updating the statistics even when not needed. This caused a few complex queries that normally ran in about 50 milliseconds to take a few seconds as they kept getting compiled. It took a while before I realized what was happening.

    Reply
  • very interesting, but what does sql do in 1 minute of compilation time ??? Is it spent on what?

    Reply
  • danielle.paquette-harvey
    February 16, 2021 7:33 pm

    Wow nice stuff! I love fun, obscure trivia, documenting a situation I’ll probably never run into, because one day or another, I’m sure to run into it 😉 Thanks for sharing that with us!

    Reply
  • So, it sounds like have auto update statistics on while loading a large table is a performance problem. Kind of like keeping indexes on the table instead of dropping/creating them.

    Reply
  • That’s awesome, what an unusual edge case!
    Sounds like SQL Server could have some belt ‘n braces to deal with that, such as a timeout so that if a query has had n number of compilations within x seconds for the same spid then just run with it?

    Reply
  • Updating statistics is indeed a hit and auto update statistics let’s lazy DBA’s say we have it covered and live with the comments of poor performance instead of understanding what Update Statistics does and what the benefit is. Of course updated statistics have their purpose duh ! But what an excellent real world senarion from Brent. Thank You. Derek

    Reply
  • And this topic probably leads to availing of the latest query execution plan. sp_recompile etc.

    Reply
  • but can’t we disable the auto-update of the statistics at the table level?

    Reply
    • Sure. You have to know to do that, and when, though, and when to enable it again. (That’s well beyond the scope of this post.)

      Reply
      • Phillip Griffith
        February 17, 2021 9:23 pm

        I think Brent is giving us a hint that leaving auto-update of statistics turned off, from start to finish of a jumbo table load, is not always ideal.

        I could be wrong.

        Reply
  • You are correct and that is my experience and interpretation. Experienced the effect many years ago when SQL Server was processing 900 transactions per second coping with multiple extremely busy production lines in a factory. I suppose the pain experienced due to the performance of out of date statistics made me sit up and try to understand what happens when statistics are being updated. I think there is no golden rule as you need to know you environment and make decisions. Importantly statistics must be updated. How you chose to navigate is the quandry. Still learning this very day many aspects of SQL Server. Derek.

    Reply

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