Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

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.

Topic link iconTransact-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.

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 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

Updated content

Revisions throughout the document to improve accuracy.

Topic refers to new statistics content in the topic, Using Statistics to Improve Query Performance.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.