Back in the days of SQL Server 7.0 we used to have queries that would work perfectly well for days suddenly slow down by factor of 10 or more, resulting in application timeouts. Although the database was queried heavily for reports, it only grew in large nightly batches as new data was fed in and occasionally, old data cleared out.
The solution was to rebuild statistics daily and rebuild all indexes a couple of times a week.
When SQL Server 2000 came out, I fell out of the habit, because SQL Server 2000 didn’t seem prone to the problem. Its indexes stayed nicely balanced.
Today I had a complex query in a stored procedure in SQL Server 2000 that normally takes about 10 seconds to return a result, take 37 to 49 seconds. Just long enough to time out a key client application. One that was revenue-critical.
My first impulse was to run SQL Server 2005’s Tuning Advisor against the offending stored procedure. It projected a 42% performance improvement by adding an index and a couple of stats. I did this, but the performance actually got a little worse.
It took awhile for the old SQL Server 7 issue to come back to me. I knew something like this had happened to me before, but the passage of about nine years left it pretty buried in my memory banks. I ran DBCC REINDEX against all the tables involved in the query and sure enough — response time was now 5 seconds.
As it turns out this client had nothing in place to regularly reindex or refresh statistics, and as far as I know that hasn’t happened in at least three years. So I was right, SQL Server 2000 is considerably better in this regard. In fact I suspect things only got flaky because the server went down due to a bad RAID controller a couple of days back.
Nevertheless, it wouldn’t hurt to schedule a job to refresh statistics and indexes once in awhile.
{ 1 comment… read it below or add one }
Not sure how much activity your Database is getting during the day but you can also turn on Auto Update Statistics which may help this problem from happening again. However, if the SQL Server is very active then its probably best not to have it turned on.