sp-autostats-transact-sql.md

title	sp_autostats (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	sp_autostats_TSQL sp_autostats
dev_langs	TSQL
helpviewer_keywords	sp_autostats
ms.assetid	d1df8c15-ee73-49eb-9d13-6e98943c3e38
author	stevestein
ms.author	sstein
monikerRange	=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

sp_autostats (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]

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.

Transact-SQL Syntax Conventions

Syntax

  
sp_autostats [ @tblname = ] 'table_or_indexed_view_name'   
    [ , [ @flagc = ] 'stats_flag' ]   
    [ , [ @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_flag' 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_flag 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.

AUTO_UPDATE_STATISTICS is always OFF for memory-optimized tables.

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

Statistics
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)
sp_createstats (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
System Stored Procedures (Transact-SQL)