Rediger

sys.sp_autostats (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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 and Statistics.

Transact-SQL syntax conventions

Syntax

sys.sp_autostats
    [ @tblname = ] N'tblname'
    [ , [ @flagc = ] 'flagc' ]
    [ , [ @indname = ] N'indname' ]
[ ; ]

Arguments

[ @tblname = ] N'tblname'

The name of the table or indexed view for which to display the AUTO_UPDATE_STATISTICS option. @tblname is nvarchar(776), with no default.

[ @flagc = ] 'flagc'

Updates or displays the AUTO_UPDATE_STATISTICS option. @flagc is varchar(10), and can be one of these values:

Value Description
ON On
OFF Off
Not specified Displays the current AUTO_UPDATE_STATISTICS setting

[ @indname = ] N'indname'

The name of the statistics for which to display or update the AUTO_UPDATE_STATISTICS option. @indname is sysname, with a default of NULL. 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.

Return code values

0 (success) or 1 (failure).

Result set

If @flagc is specified, sp_autostats reports the action that was taken but returns no result set.

If @flagc isn't specified, sp_autostats returns the following result set.

Column name Data type Description
Index Name sysname 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, you need membership in the db_owner fixed database role, or ALTER permission on @tblname.

To display the AUTO_UPDATE_STATISTICS option, you need 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 Production.Product table.

Note

In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to intended database and run the rest of the code sample.

USE AdventureWorks2022;
GO

EXECUTE sp_autostats 'Production.Product';
GO

B. Enable AUTO_UPDATE_STATISTICS for all statistics on a table

The following example enables the AUTO_UPDATE_STATISTICS option for all statistics on the Production.Product table.

USE AdventureWorks2022;
GO

EXECUTE 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 Production.Product table.

USE AdventureWorks2022;
GO

EXECUTE sp_autostats 'Production.Product', 'OFF', AK_Product_Name;
GO

Related content

  • Sidst opdateret den