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.
Requires Free Membership to View
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.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in August 2005