June 4, 2009 at 8:33 am
Question: What causes the high CPU -- low I/O behavior and why is it cured by DBREINDEX?
Background:
Running SQL Server 2000. Over the past few years we've had situations where code that was running fine will suddenly hang. Using sp_who2 I can see that the process is doing little I/O but CPU usage is very high. I've seen it where an update that usually executes in under a minute can suddenly take several hours -- if it completes at all. I have learned that killing the query and doing a DBCC DBREINDEX on the table being updated will solve the problem. I have seen this occur on different tables and the DBREINDEX seems to be the cure.
- Randall Newcomb
June 4, 2009 at 9:07 am
Probably bad execution plan due to out of date stats and probably because DBREINDEX updates stats.
Try UPDATE STATISTICS next time, see if it helps.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2009 at 11:44 am
Thanks for the response. I'll give UPDATE STATISTICS a try next time I encounter the issue since it would probably be faster than DBREINDEX.
Do you recall if DBCC INDEXDEFRAG also updates statistics?
- Randall Newcomb
June 5, 2009 at 12:11 pm
randall.c.newcomb (6/5/2009)
Do you recall if DBCC INDEXDEFRAG also updates statistics?
It does not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply