How do I find indexes that have statistics_norecompute = ON
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 = 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
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:
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
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)