9

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due to space constraints and uptime requirements.

I have noticed that it takes a long time for certain tables to be defragmented. For instance if I examine the "sys.dm_exec_requests" dynamic management view, I can see that the following INDEXDEFRAG is currently churning away on the clustered index of a table that has a table_id of 829610394:

DBCC INDEXDEFRAG (0, 829610394, 1)

I know that it will be a long time before the defragmentation process completes. Leaving aside the fact that the script currently running will eventually defragment all the tables, is there any harm in me manually running another DBCC INDEXDEFRAG on the clustered index of another table while the current command executes? Will both tables actually be defragmented at the same time if I do this?

Improve this question

2 Answers 2

Reset to default
15

Yes, you can do it for multiple tables. You can't do it for multiple indexes on the same table - I invented a new index lock subresource to prevent it. Same behavior in ALTER INDEX ... REORGANIZE that I replaced it with in 2005.

Thanks

Improve this answer
2
  • 2
    You can't get a more accurate answer than one from the person who wrote it! Don't forget about the IO load associated with doing multiple rebuilds or reorgs at the same time, and be sure to use ALTER INDEX rather than the DBCC command going forward.
    – AndrewSQL
    Commented Jan 21, 2011 at 4:18
  • Agreed, I don't know how it would be possible to get a more accurate answer on this particular issue than one from straight from Paul Randal. Thanks Paul.
    – RelentlessMike
    Commented Jan 21, 2011 at 5:43
6

Note that DBCC INDEXDEFRAG & DBREINDEX are deprecated and replaced by ALTER INDEX:

Important

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead. --http://msdn.microsoft.com/en-us/library/ms177571(v=SQL.90).aspx

As for running two simultaneously, it depends on your file layout. If they're all on the same disk(s), you would likely slow each down as they would be fighting against each other for I/O. You would be best to only REORG or REBUILD when necessary. Check out Michelle Ufford's script here for an automated solution: http://sqlfool.com/2010/04/index-defrag-script-v4-0/

Improve this answer
1
  • Thanks Eric. In this particular case, the database is spread across multiple data files, on different partitions, and on top of an IBM DS8300 SAN. So I think I'll be okay as far as I/O goes.
    – RelentlessMike
    Commented Jan 21, 2011 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.