Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Displays or changes the automatic statistics update option, AUTO_UPDATE_STATISTICS, for an index, a statistics object, a table, or an indexed view.
For more information about the AUTO_UPDATE_STATISTICS option, see ALTER DATABASE SET Options (Transact-SQL) and Statistics.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_autostats [ @tblname = ] 'table_or_indexed_view_name'
[ , [ @flagc = ] 'stats_value' ]
[ , [ @indname = ] 'statistics_name' ]
Arguments
[ @tblname= ] 'table_or_indexed_view_name'
Is the name of the table or indexed view to display the AUTO_UPDATE_STATISTICS option on. table_or_indexed_view_name is nvarchar(776), with no default.[ @flagc= ] 'stats_value'
Updates the AUTO_UPDATE_STATISTICS option to one of these values:ON = ON
OFF = OFF
When stats_flag is not specified, display the current AUTO_UPDATE_STATISTICS setting. stats_value is varchar(10), with a default of NULL.
[ @indname= ] 'statistics_name'
Is the name of the statistics to display or update the AUTO_UPDATE_STATISTICS option on. To display the statistics for an index, you can use the name of the index; an index and its corresponding statistics object have the same name.statistics_name is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
If stats_flag is specified, sp_autostats reports the action that was taken but returns no result set.
If stats_flag is not specified, sp_autostats returns the following result set.
Column name |
Data type |
Description |
|---|---|---|
Index Name |
varchar(60) |
Name of the index or statistics. |
AUTOSTATS |
varchar(3) |
Current value for the AUTO_UPDATE_STATISTICS option. |
Last Updated |
datetime |
Date of the most recent statistics update. |
The result set for a table or indexed view includes statistics created for indexes, single-column statistics generated with the AUTO_CREATE_STATISTICS option and statistics created with the CREATE STATISTICS statement.
Remarks
If the specified index is disabled, or the specified table has a disabled clustered index, an error message is displayed.
Permissions
To change the AUTO_UPDATE_STATISTICS option requires membership n the db_owner fixed database role, or ALTER permission on table_name.To display the AUTO_UPDATE_STATISTICS option requires membership in the public role.
Examples
A. Display the status of all statistics on a table
The following displays the status of all statistics on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product';
GO
B. Enable AUTO_UPDATE_STATISTICS for all statistics on a table
The following enables the AUTO_UPDATE_STATISTICS option for all statistics on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'ON';
GO
C. Disable AUTO_UPDATE_STATISTICS for a specific index
The following example disables the AUTO_UPDATE_STATISTICS option for the AK_Product_Name index on the Product table.
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name;
GO
See Also
Reference
ALTER DATABASE SET Options (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
System Stored Procedures (Transact-SQL)