How do I find indexes that have statistics_norecompute = ON

Question

I'm looking for a SQL Server 2005 query that will list all the indexes and with their respective STATISTICS_NORECOMPUTE value. I didn't see any obvious value in sysindexes that corresponds to that value.

Answer 1

The column is no_recompute in sys.stats which says

Every index will have a corresponding statistics row with the same name and ID (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.

So a JOIN between sys.indexes and sys.stats will match the index for you

Reason:

• statistics can be for columns or indexes
• an index has exactly one statistic.
• STATISTICS_NORECOMPUTE applies to the statistic for that index, not 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

DoubleJ query seems wrong to me. You can use this query to find the no recompute indexes:

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