Share via

Will SQL Server use ‘incomplete’ or ‘dirty’ statistics during online index rebuild?

  • Article

We had a customer who opened an issue with us and wanted to know the behavior of statistics during online index rebuild.  Specifically, he suspected that SQL Server might have used ‘incomplete’ statistics because his application uses read uncommitted isolation level.

This type of questions comes up frequently.  I thought I’d share my research and answers to this customer so that readers will benefit from this blog.

In order to answer the question more accurately, let’s be specific.     Let’s call Stats1 for index1’s statistics before online index rebuild and stats2 is after online index rebuild.   Furthermore, let’s call Stats3 for any incomplete stats during the index rebuild.   Now the question becomes:  during online index index rebuild  for index1 (started but not completed), which stats will my query (compiled during online index rebuild) use (stats1, stats2 or stats3)?

Here are few key points that answer the above question:

  1. First of all, there is no stats3.  SQL Server never stuffs in flight stats to stats blob for use during online index rebuild.  Even you are under dirty read, you won’t get non-existing stats3.
  2. During online index rebuild, stats1 (old stats) continues to be available for use until the very end
  3. Stats2 (new stats) will be updated at very end of index rebuild .
  4. During the brief period when SQL switches to new stats (stats2), no one can access stats at all.  Even with read uncommitted isolation level, you can’t access it.    This is because SQL Server acquires schema modification lock at the very last of online index rebuild to make changes in meta data including stats change.   Even you have read uncommitted isolation level, you still need schema stability lock for the table.  You can’t have that when schema modification lock is granted by someone else.  In short, you will never see anything in between.  You either see before (stats 1) or after (stats2).
  5. After online index rebuild, all queries involved in the tables will need to recompile .

What about Index reorg?

REORG does nothing related to statistics update. In other word, REORG doesn’t update stats for the index at all.    I have posted a blog.   In the interest of finding impact of reorg on locks and recompile, I did more research.  Re-org won’t cause recompile of your query or hold schema modification lock.  It requests a schema stability lock which is much ligher weight.  Reorg does acquires and releases x locks on pages or rows.  But these have no effect on stats or queries in read uncommitted isolation levels.  In otherwords, your query in read uncommitted isolation will continue to run without any impact.  Re-org only help on data is accessed physically.   No stats update, no recompile. 

What is the duration of schema stability locks

for online index rebuild, duration of schema-modification lock (for rebuild, sql acquire schema modification lock) is very brief towards the end.  All it does is to do metadata update?

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

  • Anonymous
    November 16, 2015
    Is the scenario also applicable while updating statistics?