Thursday, November 19, 2009

FAQ : How to disable AUTO UPATE STATISTICS for a specific table or index in SQL Server

Using sp_autostats system stored procedure we can switch off AUTO UPDATE STATISTICS on a specific table or index. Please note that, if AUTO UPDATE STAT is OFF in Database level, you can not switch ON object level. In otherwords, if the DB level Auto update stat is ON then you can override the DB level setting and switch off AUTO UPDATE STAT object level using this stored procedure.

sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]

Enabling automatic statistics for all indexes of a table
The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
EXEC sp_autostats 'Production.Product', 'ON'

No comments:

Locations of visitors to this page