Suggestions to replace DBCC INDEXDEFRAG

Question

Suggestions to replace DBCC INDEXDEFRAG

DKlein

SSCrazy

Points: 2351
More actions
November 26, 2012 at 8:45 am

#266610

We currently use DBCC INDEXDEFRAG to defrag indexes over 25% fragmentation. We are a 24/7 shop and I would need to replace it with a process that is 'online' so to speak. Is 'Alter index' as they suggest, a viable alternative in 2012?
Thanks,
DK

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Yes, it's been available since SQL Server 2005. Online index rebuilds are only available with Enterprise Edition, however. (At least that was the case up to 2008 R2, and as far as I know it still is in 2012).

John

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

DKlein (11/26/2012)
We currently use DBCC INDEXDEFRAG to defrag indexes over 25% fragmentation. We are a 24/7 shop and I would need to replace it with a process that is 'online' so to speak. Is 'Alter index' as they suggest, a viable alternative in 2012?
Thanks,
DK

DBCC INDEXDEFRAG is an online operation, so you don't need to replace it for that reason.

If you still want to replace it, you can use the following commands.

You can use this command to reorganize (defragment) an index.

From SQL Server Books Online: "REORGANIZE:Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG."

ALTER INDEX MyIndex ON MyTable REORGANIZE

If you are running Enterprise or Developer edition, and the table does not have LOB data, you can use this command to rebuild an index in online mode:

ALTER INDEX MyIndex ON MyTable REBUILD WITH ONLINE = ON

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

Change to ALTER INDEX as suggested by Michael. DBCC INDEXDEFRAG is deprecated and will be removed in SQL v12 or v13.

http://msdn.microsoft.com/en-us/library/ms177571%28v=SQL.100%29.aspx

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

DKlein SSCrazy Points: 2351 More actions · Answer 4

Thanks to all, we are organizing a transition from sql server '05 to sql server '12. I am going through maintenance jobs to prepare for the new version. I am going to try to test the alter index with the online = on option.

Thanks again,

DK

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

DKlein (11/26/2012)
We currently use DBCC INDEXDEFRAG to defrag indexes over 25% fragmentation. We are a 24/7 shop and I would need to replace it with a process that is 'online' so to speak. Is 'Alter index' as they suggest, a viable alternative in 2012?
Thanks,
DK

don't just rely on the fragmentation percentage, you may find that its quicker to rebuild the index rather than defrag it.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

DKlein (11/26/2012)
Thanks to all, we are organizing a transition from sql server '05 to sql server '12. I am going through maintenance jobs to prepare for the new version. I am going to try to test the alter index with the online = on option.
Thanks again,
DK

Just remember that if you have XML indexes or Spatial indexes, you will not be able to do online rebuilds of those indexes.

That is an improvement over 2008 R2 where you could not rebuild online if the index contained LOB columns ( varchar(max), nvarchar(max), varbinary(max), TEXT, NTEXT, IMAGE), and that meant that the clustered index could not be rebuilt online.