How do I find indexes that have statistics_norecompute = ON

Related searches

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.

STATISTICS_NORECOMPUTE – When would anyone want to use it , 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� 2: Added Missing Join on object_id (the ID of the Table). The Index_ID is only Unique to the Table.

You can use this query:

select TableName =, 
       IndexName =, 
       StatName =, 
  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,,

Advisability of using STATISTICS_NORECOMPUTE, For Microsoft SQL Server, it's not recommended you use STATISTICS_NORECOMPUTE on indexes, but there are cases when you might be� As I've compared the test and model environments I've noticed that most of the indexes in the model environment have STATISTICS_NORECOMPUTE set to ON while those in test do not. In all of the environments there is a nightly job that updates the statistics of all of the databases.

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

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

Index Statistics Norecompute, It's really a situational thing that you want to look at per-table or per-index, and you really need to find out what's in production before taking any� isnt there a relationship between statistics_norecompute and clustered indexes? My recollection is a bit fuzzy but I thought there was a theory out there that statistics

ALTER TABLE index_option (Transact-SQL), So on a table with 100,000 rows once 20,500 of the column data has changed statistics will be recomputed or in the case of SQL Server 2005 and� The STATISTICS_NORECOMPUTE setting do not affect whether statistics will be refreshed by this index rebuild. Index statistics is always refreshed by an index rebuild - can't turn that off. Whether or not you like it, you will get that free lunch.

This includes indexes that are created due to a UNIQUE or PRIMARY KEY constraint. ON Long-term table locks are not held for the duration of� Some of the indexes on one box were updated with REBUILD WITH (STATISTICS_NORECOMPUTE = ON) The source sql box does not have that on its alter index statements, so I just need to remove that

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON Sales.SalesOrderHeader SET ( STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = ON, ALLOW_PAGE_LOCKS = ON ) ; To modify index columns. To add, remove, or change the position of an index column, you must drop and recreate the index. See Also. CREATE INDEX (Transact-SQL)