August 12, 2010 at 3:58 am
We weekly run a reorganization job over a large (3 Tb) database. Last week the application using the database crashed because of large numbers of processes that were blocked. The main blocker appeared to be our reorganization. This job blocked a system spid (TASK MANAGER) that was trying to update statistics on the index that was being reorganized (the index reorganization takes about 10 hours to complete). The user process initiating the update statistics on its turn blocked other user processes. I thought that an update statistic only needs to read from a table (even uncommitted) but it looks like it requests locks.
How can we solve this. We think of updating the statistics just before reorganizing the index but aren't sure this is the right way. Any suggestions are very welcome.
August 12, 2010 at 7:27 am
if you're defraging the indexes then the next step of this job should be updating the statistics.
As defrag doesn't updates the statistics.... if ur reindexing it then no need to run update ststistics.
Try running both in same job one after another.
Tell me if this helps
Rohit
August 12, 2010 at 7:41 am
[...if you're defraging the indexes then the next step of this job should be updating the statistics...]
The reorganization takes about 10 hours. All requests for an update of the statistic during this period will be blocked by the reorganization. We therefore think about updating the statistic before the reorganization and not afterwards. Updating the statistic after the reorganization will not solve our problem
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply