SQL Clone
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 Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 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 (14 views, 38.00 KB)
- Win
- Win
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 707
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-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 252
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
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25066 Visits: 13698
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2387 Visits: 2134
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