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 ««1234»»»

Index Rebuild Taking too Long? Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 7,098, Visits: 12,604
ONLINE=ON is an Enterprise feature.

The rebuild operation, even with ONLINE=ON, can be blocked depending on the iso level and what is using that table...but 10 hours would be a seriously long running transaction and it would be surprising that if something like that were going on that it was not impacting anything else in the system besides this one rebuild attempt.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1440799
Posted Wednesday, April 10, 2013 7:27 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Yes. We are using Enterprise. It is just odd that it leads to a lot of blocking though...as soon as the script starts up I see processes marked as Head Blockers in the master database.
Post #1440801
Posted Wednesday, April 10, 2013 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959
dkschill (4/9/2013)
I will see if I can get a backup restored to another box, and see if the same issue shows up...and if it does then I will make adjustments to the restored db to see if I can get at root cause.

If I find anything I will be sure to post it here.

Thanks for the help!
-Dane


Incase if you haven't tried this :

i ) Check if there is any blocking while you are rebuilding.

ii) Run read/Write per sec perfmon counter against the drive on which the database is on while you are rebuilding the index.

Let us know how it goes.

Post #1440808
Posted Wednesday, April 10, 2013 7:42 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Just ran a reorg and it finished in less than second...tried the rebuild and it immediately created a backup on the master database. I will have to wait until Saturday night before I can run the rebuild for any length of time (and I will be sure to gather the metrics then).

Thanks for all the help and I will be sure to update this topic.

Thanks for the help,
Dane
Post #1440813
Posted Wednesday, April 10, 2013 7:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959
dkschill (4/10/2013)
Just ran a reorg and it finished in less than second...tried the rebuild and it immediately created a backup on the master database. I will have to wait until Saturday night before I can run the rebuild for any length of time (and I will be sure to gather the metrics then).

Thanks for all the help and I will be sure to update this topic.

Thanks for the help,
Dane


I am not following this, what do you mean by ".tried the rebuild and it immediately created a backup on the master database". What does backup of db has anything to do with rebuild?
Post #1440814
Posted Wednesday, April 10, 2013 7:51 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Shouldn't cross terms I suppose :)

I immediately saw a blocking chain on the master database. I found it to be dangerous, because I really wanted to troubleshoot it...

In this case though users are only affected while the rebuild is occurring so I just need to be patient and wait for the next scheduled run.

I don't have enough privileges to restore a backup to another location, and our primary DBAs are currently over booked.

Thanks again,
Dane
Post #1440816
Posted Wednesday, April 10, 2013 8:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 3,135, Visits: 11,481
Are you sure this is not a problem with your rebuild script or stored procedure?

What happens if you just run an index rebuild from a query window like this?

ALTER INDEX [PK_item] on [dbo].[item] REBUILD



Post #1440824
Posted Wednesday, April 10, 2013 8:08 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
At this point I am only running the individual rebuild. With, or with out, (online=on) I get the same results.

Thanks again!
-Dane
Post #1440827
Posted Wednesday, April 10, 2013 8:43 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:03 PM
Points: 782, Visits: 730
curious question - how much memory do you have on the machine.
Post #1440853
Posted Wednesday, April 10, 2013 10:02 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Here is what I see in sys.dm_os_sys_info:

physical_memory_in_bytes
68718006272

So about 64GBs.
Post #1440918
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse