How to find indexes that have statistics_norecompute = ON

Question

I am looking for a SQL Server 2005 query that lists all indexes and their STATISTICS_NORECOMPUTE value. I did not see any obvious value in sysindexes that matches this value.

Answer 1

The no_recompute column in sys.stats that says

Each index will have a corresponding statistics row with the same name and identifier (sys.indexes.object_id = sys.stats.object_id AND sys.indexes.index_id = sys.stats.stats_id), but not every statistics row has a corresponding index.

This way the JOIN between sys.indexes and sys.stats will match the index for you

Cause:

• statistics can be for columns or indexes
• an index has exactly one statistic.
• STATISTICS_NORECOMPUTE applies to statistics for this index, not to the index itself

Answer 2

You can use this query:

 select TableName = so.name, IndexName = si.name, StatName = s.name, s.no_recompute from sys.indexes si inner join sys.stats s on si.object_id = s.object_id inner join sys.objects so on si.object_id = so.object_id where no_recompute = 0 and so.[type] in ('U', 'V') order by so.name, si.name, s.name 

Answer 3

The DoubleJ request seems to me wrong. You can use this query to find indexes without recalculating:

 SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, s.name FROM sys.indexes AS i LEFT JOIN sys.stats AS s ON i.index_id = s.stats_id AND i.object_id = s.object_id WHERE s.no_recompute = 1