INDEX / Statistics Age August 17, 2007Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
When I was working with a major cable provider in UK I had a task to tune performance of a billing engine that runs over night and produced millions of rows in tables and suddenly start taking more then double time to finish.But 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 stats needs to be updated. Production DBAs has disabled the index rebuild job by mistake that runs after billing engine inserts millions of rows. I often heard from production DBA that they don’t have enough overnight window to rebuild indexes. Which makes database developer’s life misery in fixing performance without realizing that statistics are not up-to-date. I have experienced significant performance difference after updating statistics or rebuilding indexes.
Always check your index age before start looking into execution plan. Here is some very handy query to find out age of index / statistics. Replace Table_Name with your 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’