Export (0) Print
Expand All
Expand Minimize
3 out of 3 rated this helpful - Rate this topic

sp_autostats (Transact-SQL)

Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a specified table or indexed view in the current database.

ms188775.note(en-US,SQL.90).gifNote:
In the context of this stored procedure, the term index refers to statistics on the table or view.

Topic link icon Transact-SQL Syntax Conventions


sp_autostats [ @tblname = ] 'table_name' 
    [ , [ @flagc = ] 'stats_flag' ] 
    [ , [ @indname = ] 'index_name' ]
[ @tblname = ] 'table_name'

Is the name of the table or view for which to display the automatic UPDATE STATISTICS setting. table_name is nvarchar(776), with no default. If index_name is supplied, SQL Server 2005 modifies the automatic UPDATE STATISTICS setting for that index.

[ @flagc = ] 'stats_flag'

Specifies the automatic UPDATE STATISTICS setting for the table, view, or index:

ON = enabled

OFF = disabled

stats_flag is varchar(10), with a default of NULL.

[ @indname = ] 'index_name'

Is the name of the index for which to enable or disable the automatic UPDATE STATISTICS setting. index_name is sysname, with a default of NULL.

0 (success) or 1 (failure)

If stats_flag is specified, this procedure 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.

AUTOSTATS

varchar(3)

Current automatic UPDATE STATISTICS setting: OFF or ON.

Last Updated

datetime

Date the statistics was last updated.

If the specified index is disabled, or the specified table has a disabled clustered index, an error message will be displayed.

To change the automatic UPDATE STATISTICS setting requires membership in the db_owner fixed database role, or ALTER permission on table_name.To display the automatic UPDATE STATISTICS setting requires membership in the public role.

A. Displaying the current status of all indexes for a table

The following example displays the current statistics status of all indexes on the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product';

B. Enabling automatic statistics for all indexes of a table

The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'ON';

C. Disabling automatic statistics for a specific index

The following example disables the automatic statistics setting for the AK_Product_Name index of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.