CODEWITHSUNDEEP
sql-servert-sql
FLash
FLash

Reputation: 746

SQL server - Statistics and Recompiling

A bulk update was done on a table. This caused an SP to execute very slowly even if it had OPTION(RECOMPILE). Executing sp_updatestats fixed this.

So I assume the statistics stored in 'somewhere' was old and the SP, even if recompiled, used the old statistics.

How is statistics and recompiling related?

Upvotes: 0

Views: 561

Answers (2)

Greg Low
Greg Low

Reputation: 1586

Everyone is new at some stage so don't feel bad.

For SQL Server 2012, I wrote a detailed whitepaper for the SQL Server team at Microsoft. You'll find it here: http://download.microsoft.com/download/d/2/0/d20e1c5f-72ea-4505-9f26-fef9550efd44/plan%20caching%20and%20recompilation%20in%20sql%20server%202012.docx

A few precise details have now changed very slightly, but all the core material is still totally relevant, and should explain what you're missing if you have time to wade through it.

What can happen with an update to a table (or a set of deletes or inserts) is that what seems like a lot of rows have changed, but not enough have changed to trigger an auto-stats update. So you still have the old stats.

But then the kicker is that you query the data that was just changed (or inserted). So SQL Server thinks there is none of that data, based on the old stats, and comes up with a plan for a very small number of rows, often involving lookups. But then it finds there were a large number of rows, and it's then using the wrong plan.

The good news is that in SQL Server 2017, if you're using the latest DB compat mode, SQL Server has the smarts to defer making the plan decision until it's actually worked out how many rows are involved. In many cases, but not all, that will automagically fix the issue for you.

But in your case, as soon as you recomputed the stats, SQL Server created a new plan and then ran fast again.

The OPTION(RECOMPILE) won't have helped because it would have recompiled using the old stats.

Hope that helps.

Upvotes: 1

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

Recompiling discards the cached execution plan of your SP and creates a new one. As you've correctly guessed the current statistics will be used for plan generation, so to make sure your newly generated plan is a good one you should update the statistics first.

You can find more information about statistics in the docs.

Upvotes: 2

Related Questions