Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 13 down vote accepted

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

share|improve this answer
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 Jan 21 '11 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 Jan 21 '11 at 5:43
add comment

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/

share|improve this answer
    
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 Jan 21 '11 at 5:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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