sp_autostats (Transact-SQL)sp_autostats (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

顯示或變更索引、統計資料物件、資料表或索引檢視表的自動統計資料更新選項 AUTO_UPDATE_STATISTICS。Displays or changes the automatic statistics update option, AUTO_UPDATE_STATISTICS, for an index, a statistics object, a table, or an indexed view.

如需 AUTO_UPDATE_STATISTICS 選項的詳細資訊, 請參閱ALTER DATABASE SET (選項) transact-sqlSTATISTICSFor more information about the AUTO_UPDATE_STATISTICS option, see ALTER DATABASE SET Options (Transact-SQL) and Statistics.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

[ @tblname = ] 'table_or_indexed_view_name'這是要在其中顯示 AUTO_UPDATE_STATISTICS 選項的資料表或索引視圖名稱。[ @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_nameNvarchar (776) , 沒有預設值。table_or_indexed_view_name is nvarchar(776), with no default.

[ @flagc = ] 'stats_flag'將 AUTO_UPDATE_STATISTICS 選項更新為下列其中一個值:[ @flagc = ] 'stats_flag' Updates the AUTO_UPDATE_STATISTICS option to one of these values:

ON = ONON = ON

關閉= 關閉OFF = OFF

未指定stats_flag時, 會顯示目前的 AUTO_UPDATE_STATISTICS 設定。When stats_flag is not specified, display the current AUTO_UPDATE_STATISTICS setting. stats_flagVarchar (10) , 預設值是 Null。stats_flag is varchar(10), with a default of NULL.

[ @indname = ] 'statistics_name'這是要在其中顯示或更新 AUTO_UPDATE_STATISTICS 選項的統計資料名稱。[ @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_namesysname, 預設值是 Null。statistics_name is sysname, with a default of NULL.

傳回碼值Return Code Values

0 (成功) 或 1 (失敗)0 (success) or 1 (failure)

結果集Result Sets

如果指定了stats_flag , sp_autostats會報告所採取的動作, 但不會傳回任何結果集。If stats_flag is specified, sp_autostats reports the action that was taken but returns no result set.

如果未指定stats_flag , sp_autostats會傳回下列結果集。If stats_flag is not specified, sp_autostats returns the following result set.

資料行名稱Column name 資料類型Data type 描述Description
Index NameIndex Name varchar(60)varchar(60) 索引或統計資料的名稱。Name of the index or statistics.
AUTOSTATSAUTOSTATS varchar(3)varchar(3) AUTO_UPDATE_STATISTICS 選項的目前值。Current value for the AUTO_UPDATE_STATISTICS option.
上次更新日期Last Updated datetimedatetime 最近更新統計資料的日期。Date of the most recent statistics update.

資料表或索引視圖的結果集包括針對索引所建立的統計資料、使用 AUTO_CREATE_STATISTICS 選項產生的單一資料行統計資料, 以及使用CREATE statistics語句所建立的統計資料。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 永遠都是 OFF。AUTO_UPDATE_STATISTICS is always OFF for memory-optimized tables.

PermissionsPermissions

若要變更 AUTO_UPDATE_STATISTICS 選項, 需要db_owner固定資料庫角色的成員資格, 或TABLE_NAME上的 ALTER 許可權。若要顯示 AUTO_UPDATE_STATISTICS 選項, 需要public角色中的成員資格。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.A. 顯示資料表上所有統計資料的狀態Display the status of all statistics on a table

下列範例會顯示 Product 資料表上所有統計資料的狀態。The following displays the status of all statistics on the Product table.

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

B.B. 針對資料表的所有統計資料啟用 AUTO_UPDATE_STATISTICSEnable AUTO_UPDATE_STATISTICS for all statistics on a table

下列範例會針對 Product 資料表的所有統計資料啟用 AUTO_UPDATE_STATISTICS 選項。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.C. 針對特定索引停用 AUTO_UPDATE_STATISTICSDisable AUTO_UPDATE_STATISTICS for a specific index

下列範例會針對 AK_Product_Name 資料表的 Product 索引停用 AUTO_UPDATE_STATISTICS 選項。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  

另請參閱See Also

統計資料 Statistics
ALTER DATABASE SET 選項 (Transact-SQL) ALTER DATABASE SET Options (Transact-SQL)
資料庫引擎預存(程式 transact-sql) Database Engine Stored Procedures (Transact-SQL)
CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL) DROP STATISTICS (Transact-SQL)
sp_createstats (Transact-SQL) sp_createstats (Transact-SQL)
UPDATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL)
系統預存程序 (Transact-SQL)System Stored Procedures (Transact-SQL)