SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Re-Index :Performance ISSUE


Re-Index :Performance ISSUE

Author
Message
vishalsurya50
vishalsurya50
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 786
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
Chris Harshman
Chris Harshman
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5104 Visits: 4010
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?
vishalsurya50
vishalsurya50
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 786
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 .
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10692 Visits: 13687
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?

---------------------------------------------------------------------
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10692 Visits: 13687
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

---------------------------------------------------------------------
vishalsurya50
vishalsurya50
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 786
re-index was the lead blocker..
vishalsurya50
vishalsurya50
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 786
re-index step alone kept running 7 hrs..
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10692 Visits: 13687
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.

---------------------------------------------------------------------
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