jump to navigation

INDEX / Statistics Age August 17, 2007

Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
trackback

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’

  

Advertisements

Comments»

1. Anonymous - January 16, 2008

–Here is the query that should check index age in SQL Server 2000:

SELECT
‘Index Name’ = ind.name,
‘Statistics Date’ = STATS_DATE(ind.id, ind.indid)
FROM
SYSINDEXES ind
WHERE
OBJECT_NAME(ind.id) = ‘respondent’

Like

2. Anonymous - June 17, 2008

This is a possible way of measuring statistics age but this should be used for determining the statistics updation routine for SQL Server 2005 as update statistics is based on column modifications in SQL 2005 rather the row modifications

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: