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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.