March 15, 2013 at 5:31 am
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
March 15, 2013 at 6:16 am
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?
March 15, 2013 at 6:27 am
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 .
March 15, 2013 at 6:29 am
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?
---------------------------------------------------------------------
March 15, 2013 at 6:33 am
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
---------------------------------------------------------------------
March 15, 2013 at 8:46 am
re-index was the lead blocker..
March 15, 2013 at 8:46 am
re-index step alone kept running 7 hrs..
March 15, 2013 at 9:48 am
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.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply