Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

sp_autostats (Transact-SQL)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

sp_autostats [ @tblname = ] 'table_or_indexed_view_name' 
    [ , [ @flagc = ] 'stats_value' ] 
    [ , [ @indname = ] 'statistics_name' ]

[ @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.

0 (success) or 1 (failure)

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.

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.

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.

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

Community Additions

ADD
Show:
© 2015 Microsoft