Index defrag takes forever to run

We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last week or so) I've noticed that it's been taking significantly longer (2-7 hours). This morning, I had to terminate the job because it had been running for a good 8 and a half hours.

I took a look at its log and it looks like it hadn't even finished with the indices from the first table by the time I cancelled the job:

```
Job '02.00 am - Index defrag' : Step 1, 'run index defrag' : Began Executing 2012-06-27 02:00:05

DBCC: Defrag phase of index 'table1_pk_index' is 27% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 27% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 27% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 27% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag phase of index 'table1_pk_index' is 28% complete. [SQLSTATE 01000]
DBCC: Defrag ph

You should approach index reorganize performance troubleshooting problems the same way you approach any performance troubleshooting problem: identify the waits and resources, using a methodology like waits and queues for example. Once you do the due diligence and collect the data (perf counters, wait stats etc), if you are still unable to find the issue we can help, rather than ask us to roll the 8 ball and guess (and more importantly, rather than you working by guess).

Next, there are many maintenance scripts out there that are a heck of a lot better than reorganizing ad-nauseam every night, like Ola Hallengren or Michelle Ufford just to give two examples.

StackExchange Database Administrators Q#19990, answer score: 8