April 28, 2013 at 6:06 am
Hi
I have a table with a clustered index and 6 non clustered indexes.The size of the table is 400G and logical fragmentation of the indexes are above 90%.
We have data modification in 24 hours of all days of week,but it is less at nights.
I want to rebuild the indexes and set fillfactor for the indexes that are not in ascending order.
1.Should I use rebuild or DBREINDEX?
2.Is it better to use online or offline option?(because of blocking)and is there any thing that I should consider if I use online rebuild ?
3.What is the order of rebuilding the clustered and non clustered indexes?which of them should be done first?
4.What would happen if it is canceled during the rebuild or dbreindex ?
I dont want to have a lot of time out in users request.What is the best scenario for doing this task?
April 28, 2013 at 6:53 am
1) DBCC DBREINDEX is deprecated, is included only for backward compatibility with SQL Server 2000 and should not be used any longer
2) Up to you. Depends on your requirements and edition.
3) Irrelevant, either, doesn't matter in the slightest.
4) Rolled back in its entirety.
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
April 28, 2013 at 9:53 pm
Thanks Gail
If I use online option, does it have any bad effect on my server?
I know that I should just be careful about disk space.
I want to use it just because of blocking,I want the user can do their select and insert in less time out,but I read that with online ,it takes long time to do the rebuild.
I use SQL Server 2008 Enterprise edition .
April 29, 2013 at 3:08 am
More TempDB space, more resources, longer duration. It still takes locks, just short-lived ones
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
April 30, 2013 at 12:33 am
Thanks Gail for your help 🙂
April 30, 2013 at 4:13 am
if you have a table that has a clustered index and non-clustered index(es), bear in mind that the non-clustered index use pointers to the clustered index key, to locate values.
April 30, 2013 at 4:45 am
alistair.mc14 (4/30/2013)
if you have a table that has a clustered index and non-clustered index(es), bear in mind that the non-clustered index use pointers to the clustered index key, to locate values.
As far as I understand, rebuilding just the clustered index has no negative effect on the non-clustered indexes, this is because the Clustered keys which are used as row locators for the NC indexes aren't changed by a rebuild of the clustered index. If I'm correct, the rebuild just applies a logical sort order.
If you were dropping the clustered index, you would have to rebuild the NC indexes as their row locators must be changed to RID's.
Gail, is this correct?
Thanks
April 30, 2013 at 5:01 am
alistair.mc14 (4/30/2013)
if you have a table that has a clustered index and non-clustered index(es), bear in mind that the non-clustered index use pointers to the clustered index key, to locate values.
True (pointers being the clustered index key values), but that has no bearing at all on the process of rebuilding indexes, online or otherwise.
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
April 30, 2013 at 5:16 am
GilaMonster (4/28/2013)
1) DBCC DBREINDEX is deprecated, is included only for backward compatibility with SQL Server 2000 and should not be used any longer2) Up to you. Depends on your requirements and edition.
3) Irrelevant, either, doesn't matter in the slightest.
4) Rolled back in its entirety.
Reorg's aren't rolled back...
http://sqlmag.com/blog/efficient-index-maintenance-using-database-mirroring
April 30, 2013 at 6:00 am
adb2303 (4/30/2013)
GilaMonster (4/28/2013)
1) DBCC DBREINDEX is deprecated, is included only for backward compatibility with SQL Server 2000 and should not be used any longer2) Up to you. Depends on your requirements and edition.
3) Irrelevant, either, doesn't matter in the slightest.
4) Rolled back in its entirety.
Reorg's aren't rolled back...
OP didn't ask about reorgs, he asked about rebuild/dbreindex (same thing). Those are rolled back in their entirety if stopped part way through.
Rebuild/reindex is a single operation, single transaction so if stopped has to roll back completely. ALTER INDEX... REORGANISE/DBCC INDEXDEFRAG is run as many, many very small transactions. If stopped part way through, only the small transaction that's currently running gets rolled back, not the entire operation.
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
April 30, 2013 at 8:37 am
mah_j (4/28/2013)
4.What would happen if it is canceled during the rebuild or dbreindex ?
Gail, dbreindex or alter reindex is not single unit of transaction? is it? So if we stop reindexing, work can be ratained, i beleive.
April 30, 2013 at 8:44 am
SQL Show (4/30/2013)
mah_j (4/28/2013)
4.What would happen if it is canceled during the rebuild or dbreindex ?Gail, dbreindex or alter reindex is not single unit of transaction? is it?
Rebuilding an index (which is what DBCC DBREINDEX and ALTER INDEX ... REBUILD do) is a single transaction, if cancelled, it rolls back entirely.
So if we stop reindexing, work can be ratained, i beleive.
Nope. You're maybe thinking of reorganise (DBCC INDEXDEFRAG/ALTER INDEX .. REORGANIZE), which I explained above.
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
April 30, 2013 at 8:47 am
Yep, I was wrong 🙂
April 30, 2013 at 9:38 am
mah_j (4/28/2013)
HiI have a table with a clustered index and 6 non clustered indexes.The size of the table is 400G and logical fragmentation of the indexes are above 90%.
We have data modification in 24 hours of all days of week,but it is less at nights.
I want to rebuild the indexes and set fillfactor for the indexes that are not in ascending order.
1.Should I use rebuild or DBREINDEX?
2.Is it better to use online or offline option?(because of blocking)and is there any thing that I should consider if I use online rebuild ?
3.What is the order of rebuilding the clustered and non clustered indexes?which of them should be done first?
4.What would happen if it is canceled during the rebuild or dbreindex ?
I dont want to have a lot of time out in users request.What is the best scenario for doing this task?
You should be aware that you will need at least enough free space in the database to create a new copy of largest index on that table, more than likely the clustered index.
If the clustered index is 350 GB, you will need that much free space, or else the database will expand if you have autogrow turned on.
If you don't have that much space, reorg(defrag) may be a better option.
April 30, 2013 at 10:16 am
GilaMonster (4/28/2013)
1) DBCC DBREINDEX is deprecated, is included only for backward compatibility with SQL Server 2000 and should not be used any longer2) Up to you. Depends on your requirements and edition.
3) Irrelevant, either, doesn't matter in the slightest.
4) Rolled back in its entirety.
3)
For me, I believe you should always rebuild the clustered index first. When rebuilding a nonclustered index offline, SQL has to fully scan the clustered index. Therefore, it's only logical to me to rebuild it first, so that the overhead of scanning it multiple times to rebuild the non clus indexes is reduced.
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply