Finding the state of your statistics

Up-to-date statistics help ensure that the most appropriate index is chosen to obtain
the underlying data. They also help ensure that the correct index access mechanism is
chosen, for example, seek or lookup. Running the SQL script given in the following
listing will identify the current state of your statistics


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
, s.rowcnt AS 'Row Count'
, s.rowmodctr AS 'Number Of Changes'
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS
DECIMAL(28,2)) * 100.0)
AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName

The output will show you when index statistics were last updated and the percentage of rows that have changed since the last update of statistics. This is the criteria you should follow to determine if you need to update your statistics.

Note: An index’s statistics tend to be updated automatically when 20% of its rows have
changed. For large tables, this might take a considerable time, during which queries
may run more slowly because of having stale statistics.