I'm adding to my post.  After this was initially posted I was told that I don't have any cool graphics like some of the other posts... no cool colors...  So I did what I could in a short amount of time...

A little while back we started having performance issues with some of our SQL queries.  No matter what we did to optimize the queries and changing indexes on tables we still came up with the same issue... A query that would sometimes complete in 20 or so seconds... and other times totally timeout after 30 seconds.  We eventually found that the problem was that SQL Server was not updating the statistics on the table indexes. In the end (after fixing our statistics problem) this query would run in a matter of a few seconds.

Without going too deep, statistics are basically information about your tables and indexes that SQL Server keeps.  That information is then used to determine the best execution plan to use when executing a query.  Just because there is an index on a table column does not necessarily mean that the index will be used.  Before the query is executed SQL server runs a check to see which method will require less I/O (using the index or just doing a table scan).  If the table scan is going to require less I/O than using the index, then the index is ignored.

 Where we run into problems is that the execution plan is not re-evaluated until the statistics are changed.  So what was happening to us is that the amount of data has changed over the years on a table that was not getting the statistics updated (even though the "Auto Update Statistics" option was set to true for the database.  Basically we were using an ineffecient execution plan.  If you want to go deeper into how the Optimizer determines execution plans based on statistics you can visit THIS LINK to an article on sql-server-performance.com written by Brad McGeHee.  It's worth the read if you're a true SQL geek.

 A quick note on the "Auto Update Statistics" option in SQL Server 2000... It's not fullproof.  This is what obviously caused our problem, so our table statistics should have been getting updated.  Microsoft says that if your table has between 6 and 500 rows in the table, the statistics will be updated after 500 modifications are made.  If there are more than 500 rows the statistics will be updated when the number of modifications is greater than {number of rows in table} *.20 + 500... but like I said, some tables or indexes can slip through the cracks with no rhyme or reason.  If you want to see how many modifications have happened on a table, you can look in the sysIndexes table.  Check the rowmodctr column.

We tried to run the UpdateStatistics command, but that didn't fix the problem.  Only after dropping the statistics for every index and allowing SQL Server to recreate the indexes as necessary did the problem go away.  At this point, our query went from running 20-30 seconds down to 1-2 seconds.

To drop statistics for an index, you use the follwoing command:
drop statistics {tablename}.{indexname}

To loop through all indexes for a table, we set up a stored procedure to drop the statistics... we created a cursor to loop through the sysobjects table and pull all of the indexes for a table, performing the "drop statistics" on each. 

 In our environment, we have a large number of databases with a large number of tables with a large number of indexes.  Trying to drop all of the indexes by hand would be too tedius, so we wrote a couple of small stored procedures... one "drop stats for all tables" to call the above stored procedure for each table in a database, and one "drop stats for all dbs" to call the "drop stats for all tables" with each database on a server.  So basically we can call one stored procedure and all of the index statistics get dropped on the server.

To view this code, click HERE.

 After sitting here writing this entry, I'm tempted to write a procedure to run at a scheduled interval to alert us if statistics are getting out of whack... If I do, I'll put the code in here so you can get it...

Chris Antoniak  DBA