April 10, 2013 at 1:11 am
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
April 10, 2013 at 1:17 am
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"
April 17, 2013 at 9:16 pm
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.
April 18, 2013 at 3:10 am
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...
April 18, 2013 at 3:51 am
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.
---------------------------------------------------------------------
April 18, 2013 at 4:35 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply