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

Re-Index :Performance ISSUE Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 5:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 AM
Points: 26, Visits: 469
Hi,

In Our environment we have a job in sql server 2012 which has two steps .

1)Executing a stored procedure.
2)Re-Index for all tables in a database where large no of tables are present. It occurs daily.


Normally the jobs takes only 5 mins to execute successfully. But yesterday the job executed for more than 7 hrs which affected production users and i stopped it manually. Dont know why?

Whether it may be due to deadlock?. How to prevent it from deadlock if present in future.

Can you let me know the reason and to avoid it in future.


Regards
Vishalsurya
Post #1431468
Posted Friday, March 15, 2013 6:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 1,848, Visits: 2,006
To know if blocking was the issue, you would have had to check the running requests using sys.dm_exec_requests or look for waiting locks in sys.dm_tran_locks before you stopped the job. Does this job do any logging?
Post #1431494
Posted Friday, March 15, 2013 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 AM
Points: 26, Visits: 469
Normally what are the causes for this type of long running re-index query ?It ran for 7 hrs.. normally it would run within 5 mins. I checked sp_who2 active and found in the Blkby column . There were blockings present in the particular database - >tables .
Post #1431498
Posted Friday, March 15, 2013 6:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 5,973, Visits: 12,873
which step ran long?

If the reindex step does the reindex depend on thresholds to determine what it reindexes or always reindex everything?

any output from the job?


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

Post #1431499
Posted Friday, March 15, 2013 6:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 5,973, Visits: 12,873
vishalsurya50 (3/15/2013)
Normally what are the causes for this type of long running re-index query ?It ran for 7 hrs.. normally it would run within 5 mins. I checked sp_who2 active and found in the Blkby column . There were blockings present in the particular database - >tables .


what process was the lead blocker?

by the way these are blocks, not deadlocks


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

Post #1431503
Posted Friday, March 15, 2013 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 AM
Points: 26, Visits: 469
re-index was the lead blocker..
Post #1431569
Posted Friday, March 15, 2013 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 AM
Points: 26, Visits: 469
re-index step alone kept running 7 hrs..
Post #1431570
Posted Friday, March 15, 2013 9:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 5,973, Visits: 12,873
I would guess there was more work for the reindex to do this time, it then became a timing issue as it ran into your online day.

running this daily is likely overkill, its rare all tables would need reindexing daily. If not already doing so unless reindex tables that are actually fragmented, there are a number of scripts available to do that.


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

Post #1431604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse