SQL Server 2005 methods to find table INDEX or Statistics age for better performance

Recently we have had problems on one of the database that is used on 24/7 basis where a ETL process to import and export millions of rows on few tables has been taking longer time to finish. Further the SELECT queires on reports are timed-out and smaller queries are taking more time to finish.

First instance I thought REINDEXING would do some betterment on the performance, as it is for 2 days to complete the reporting queries without any issues and then again problem started. Eventually after struggling many hours to find out why index has not been selected by optimizer I realized that optimizer has old statistics for that index that does not represent the current state of data and therefore table statistics needs to be updated.

SO when you get similar problem at your end don't forget to check the table(s) index age before start looking into execution plan. Here is some very handy query to find out age of index / statistics.  Just re-write the following query by mentioning desired Table_Name :

SELECT 

            ‘Index Name’ = ind.name,

            ‘Statistics Date’ = STATS_DATE(ind.object_id, ind.index_id)

FROM

            SYS.INDEXES ind

WHERE

            OBJECT_NAME(ind.object_id) = ‘Table_Name’