Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reindex task taking for ever Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 1:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 23, 2014 1:58 PM
Points: 145, Visits: 331
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


  Post Attachments 
Untitled.png (7 views, 38.25 KB)
Post #1440673
Posted Wednesday, April 10, 2013 1:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:45 PM
Points: 119, Visits: 490
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"
Post #1440675
Posted Wednesday, April 17, 2013 9:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 9:46 AM
Points: 12, Visits: 128
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.
Post #1443577
Posted Thursday, April 18, 2013 3:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 26, 2014 8:15 PM
Points: 10, Visits: 58
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...
Post #1443694
Posted Thursday, April 18, 2013 3:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
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.


---------------------------------------------------------------------

Post #1443711
Posted Thursday, April 18, 2013 4:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:40 PM
Points: 466, Visits: 1,923
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
Post #1443729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse