When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested.

Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.

If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in the optimizer not using an index when it should, or using an index when it would be more efficient to scan the table.

That's why it is crucial that the statistics for an index be updated regularly. How often? Well, that, of course, is going to depend on the data being stored, the frequency of updates, inserts, and deletes to the table, etc. It could be nightly; it could monthly. It just depends.

So, how can you tell when the statistics where last updated for an index?

The following query demonstrates this. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.

Yes, this script doesn't work for SQL Server 2000 since the sys.indexes view doesn't exist in that version. I should have pointed that out in the original post.

I'll create a similar script for SQL Server 2000 and post in the next day or two.

In the meantime, for SQL Server 2000 you can use the DBCC SHOW_STATISTICS command to see the last time an index's statistics were updated. For example, the following command shows information about the aunmind index on the authors tables in the pubs database.

I ran into this a lot when I was in the data warehousing world. We had tons of stored/archived data that was only occasionally queried on, but when it was, more times than not, I would need to go in and make updates such as this. Fortunately the systems are much more aware and will clue you in when these changes need to happen.