Rebuild or DBREINDEX

Question

Rebuild or DBREINDEX

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ScottPletcher (4/30/2013)
When rebuilding a nonclustered index offline, SQL has to fully scan the clustered index.

No it doesn't. That would be an inefficient way of running a rebuild. Both online and offline index rebuilds read the old index (or an identical index if you happen to have duplicate indexes) to build the new one, the old index is only dropped once the new one is built, hence why you need more than the size of the index in free space (data file) to rebuild the index.

See the execution plan of an index rebuild of a nonclustered index (both offline and online)

Gail obviously disagrees, stating cleary that it makes absolutely no difference whatsoever. But I have to strongly disagree with that: if the original table (clus index) is hugely fragmented, I think it has to be better to defragment it before fully scanning it multiple times.

Except that you'll only be scanning the clustered index once, when you rebuild the clustered index, rebuilding the nonclustered indexes only requires scanning that particular nonclustered index.

Hence it makes absolutely no difference what order you rebuild the indexes on a table as each index gets scanned once in the process of being rebuilt.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

GilaMonster (4/30/2013)
ScottPletcher (4/30/2013)
When rebuilding a nonclustered index offline, SQL has to fully scan the clustered index.
No it doesn't. That would be an inefficient way of running a rebuild. Both online and offline index rebuilds read the old index (or an identical index if you happen to have duplicate indexes) to build the new one, the old index is only dropped once the new one is built, hence why you need more than the size of the index in free space (data file) to rebuild the index.
See the execution plan of an index rebuild of a nonclustered index (both offline and online)
Gail obviously disagrees, stating cleary that it makes absolutely no difference whatsoever. But I have to strongly disagree with that: if the original table (clus index) is hugely fragmented, I think it has to be better to defragment it before fully scanning it multiple times.
Except that you'll only be scanning the clustered index once, when you rebuild the clustered index, rebuilding the nonclustered indexes only requires scanning that particular nonclustered index.
Hence it makes absolutely no difference what order you rebuild the indexes on a table as each index gets scanned once in the process of being rebuilt.

According to BOL, SQL scans the table:

"Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency."

But, according to the plan in 2008 it doesn't; in 2005, it does. Of course if the index was disabled at any point, SQL has to scan the table anyway.

I still strongly urge you to rebuild the clustered index first if at all possible, rather than trying to guess when SQL will or won't need to scan the full table, particularly if you still work with SQL 2005 instances (as I do).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ScottPletcher (4/30/2013)
According to BOL, SQL scans the table:
"Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency."

Yup, seen and reported that error in Books Online before. I'll compile another long list of BoL errors and report them when I have a moment, maybe they'll get fixed sometime in the next decade.

But, according to the plan in 2008 it doesn't; in 2005, it does.

2005 also has the option to read the old index to build the new, can't recall under what circumstances it will do that. It's why the recommendation for corrupt nonclustered indexes (not the incorrect recommendation you quoted above) is to drop the corrupt index then build a new one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

ScottPletcher (4/30/2013)
According to BOL, SQL scans the table:
"Rebuilding the index offline, by contrast, will force a scan of the clustered index (or heap) and so remove the inconsistency."

I reported the bug in the documentation (after Gail pointed out the problem). Note there is a link in the Connect item description to a thread on this site with all the details.

https://connect.microsoft.com/SQLServer/feedback/details/775884

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato