Problem solve Get help with specific problems with your technologies, process and projects.

Recovering SQL Server performance without sp_updatestats

Alternatives to running sp_updatestats to recover SQL Server performance are offered by performance and tuning expert Jeremy Kadlex.

I have a third-party application. It is based on .NET and SQL Server 2000. It is running on a clustered pair of Dell 6650 servers, attached to an EMC SAN. But the performance goes into the tank a couple of times each week. We see queries beginning to take much longer and timing out. The central processing unit on the server spikes to 100%. We have learned that we can temporarily recover from this issue by running sp_updatestats. This process takes several minutes to run, but immediately upon completion, the central processing unit drops back to more normal levels and performance returns to normal. We already have jobs scheduled daily that perform reindex and update stats. Yet, for some reason, the statistics get out of whack and we have to run the sp_updatestats to recover. We are running SQL Server 2000 SP3.
For a problem that is deceptive like this one, I would recommend getting in touch with the vendor to find out if other customers have the issue and how they have corrected it. Depending on the response, I would probably recommend taking a look at a performance monitoring and management tool that can monitor the system with low overhead on a 24X7 basis. Another configuration to check for the SQL Server database is the 'Auto create statistics' and 'Auto update statistics'. These configurations can be found by opening Enterprise Manager, opening the Databases folder, right clicking on the database, selecting Properties and clicking on the Options tab.

Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.