Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jarviskrus
Starting Member
2 Posts |
 Posted - 2004-08-31 : 13:41:33
|
| Question: What would cause a "problem" with statistics that would require UPDATE STATISTICS to correct? Could it be a problem caused by a reboot?Scenario: I have had an unusual problem over the last few months. Two times to date when I come in in the morning I have 1 stored procedure that is taking 2 - 3 minutes to execute instead of 1 - 2 seconds. It is the only one out of several hundred that is misbehaving. I look at the execution plan and on 2 tables I see index scans instead of the index seeks that I was getting the day before. I defrag the all the indexes on the 2 tables and recompile the sp and no change. I then run update statistics on all indexes on the 2 tables and then recompile and it fixes the problem. The sp is running in the 1 - 2 second range.Additional info: a job runs sp_updatestats every night at 4:00 a.m. On the days that this happened the server was restarted around 5:00 a.m. - Also note that this does not happen every time there is a reboot. I just noticed that there was one when we had the problem.Thanks |
|
|
MuadDBA
628 Posts |
Posted - 2004-08-31 : 13:59:12
|
| How long does the sp_updatestats job take to run?If the server cut out at 5am and you were in the middle of your job, the statistics tables could have gotten whacked.Not sure, but does indexdefrag automatically update stats? I know index rebuild does. |
 |
|
|
jarviskrus
Starting Member
2 Posts |
Posted - 2004-08-31 : 16:09:38
|
| When sp_updatestats runs it take about 3 minutes to run. I initially suspected this as well. Also I don't think that DBCC INDEXDEFRAG automatically updates statistics but I don't know for sure. |
|
|
|
|
|
|
Performance problem Statistics related