Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reindex task taking for ever


Reindex task taking for ever

Author
Message
Ravi-156610
Ravi-156610
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 333
SQL Server 2008 R2 - 10.50.2500.

We have a Database, about 35 GB in size, which has got several tables and indexes. I ran a report 'Index Physical Statistics' and it came up with recommendation for rebuild couple of indexes. I tried to rebuild these indexes, all but few wouldn't. The reindex on these few indexes seems to be taking for ever and i had to cancel the reindex task. Below is the detail of the index on which reindex task was taking for ever:

IndexName Index Type Partitions Depth Operation Recommended
XXXXXX , Nonclustered, 1, 2, Rebuild

Partition Type, Avg Frag%, #Fragments, Avg Pages per Frag, # Pages
1, 50, 2, 1, 2




Any ideas?

Thanks,
Ravi
Attachments
Untitled.png (7 views, 38.00 KB)
- Win
- Win
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 601
check > DBCC SHOWCONTIG

And also check if any applications are accessing the tables, any transactions running.

And I would suggest maintenance activities should be done in non peak hours (off business hours).

Hope this help..!

Cheers,
- Win
"Dont Judge a Book by its Cover"
jay81
jay81
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 243
You might want to check available space on the index/data drive. Reindex (not rebuild) needs space as it is an online operation. You might want to check the log space as well since it's a logged operation as well.
VickyDBA
VickyDBA
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 78
Rebuild also requires space and that is in tempdb. so you will have to check the space on tempdb Drive and the growth in size of tempdb while your rebuild operation runs.

Also, it is better to check if something is blocking to your reorganize\rebuild operation while you are running that. Also, as Win suggested, you should do this activity while non peak hours as this requires time and potentially locks on the table for some time...
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 Visits: 13687
how long is for ever?

One thing that report does not take into account is the no. of pages. An index with only 2 pages is not worth rebuilding or even reorganising. Accepted value for no. of pages in an index before its worth de-fragmenting is 500 - 1000. Therefore you can ignore the recommendation to rebuild this index.

Having said that a rebuild on that index should be almost instantaneous. I have heard of issues such as this being resolved by running dbcc updateusage.

It is worth checking for blocking as well.

---------------------------------------------------------------------
chandan_jha18
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 2133
Before writing anything, I want you to inform that 'cancel' a rebuild task is not something nice to do and could land you up in a bad scenario.

Please try to make sure the following conditions are met:

1) Make sure you have sufficient amount of transaction log space and do not let 'autogrowth' parameter increase the size and give it that much in advance.

2) Make sure not to perform this when you have a lot of DMLs going in your tables, at times in nights there are a lot of imports running so keep an eye there.

3) TempDB should have enough space and again, dont let autogrow happen as much as possible.

To getting quicker results you should remember that 'Rebuild Index' task is considered as a bulk operation, so you might want to change the recovery model to 'Bulk-logged' for minimal logging and once you are done change the recovery model to full and issue a fresh full backup to keep the chain intact.

Let me know how it goes. All the best!!!

Chandan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search