sp_autostats (Transact-SQL)

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 Using Statistics to Improve Query Performance.

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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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)

sp_createstats (Transact-SQL)

UPDATE STATISTICS (Transact-SQL)

System Stored Procedures (Transact-SQL)

Concepts

Using Statistics to Improve Query Performance

Change History

Updated content
Revisions throughout the document to improve accuracy.
Topic refers to new statistics content in the topic, Using Statistics to Improve Query Performance.