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
Adding non-clustered indexes to SQL Server columns and tables frequently queried is possible. Learn how to add non-clustered indexes to SQL Server ...
Learn how to view a query execution plan in SQL Server with SQL Server Management Studio or Query Analyzer. Review and decrease query plan sub tree ...
Want to change the collation to SQL Server 2005 standards after upgrading databases from SQL Server 2000? Learn the best approach involving DTS and ...
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.