UPDATE STATISTICS (Transact-SQL)UPDATE STATISTICS (Transact-SQL)

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

針對資料表或索引檢視表更新查詢最佳化統計資料。Updates query optimization statistics on a table or indexed view. 根據預設,查詢最佳化工具已經會視需要更新統計資料來改善查詢計劃。在某些情況下,您可以使用 UPDATE STATISTICSsp_updatestats 預存程序,讓統計資料的更新頻率高於預設更新頻率,以改善查詢效能。By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

更新統計資料可確保查詢使用最新的統計資料進行編譯。Updating statistics ensures that queries compile with up-to-date statistics. 不過,更新統計資料會導致查詢重新編譯。However, updating statistics causes queries to recompile. 我們建議您不要太頻繁地更新統計資料,因為改善查詢計劃與重新編譯查詢所花費的時間之間具有效能權衡取捨。We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. 特定的權衡取捨完全取決於您的應用程式。The specific tradeoffs depend on your application. UPDATE STATISTICS 可以使用 tempdb 來排序資料列的範例,以建立統計資料。UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

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

語法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ] 
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]  

引數Arguments

table_or_indexed_view_nametable_or_indexed_view_name
這是包含統計資料物件的資料表或索引檢視表名稱。Is the name of the table or indexed view that contains the statistics object.

index_or_statistics_nameindex_or_statistics_name
這是要更新統計資料之索引的名稱,或是要更新之統計資料的名稱。Is the name of the index to update statistics on or name of the statistics to update. 如果沒有指定 index_or_statistics_name,查詢最佳化工具就會更新資料表或索引檢視表的所有統計資料。If index_or_statistics_name is not specified, the query optimizer updates all statistics for the table or indexed view. 這包括使用 CREATE STATISTICS 陳述式所建立的統計資料、開啟 AUTO_CREATE_STATISTICS 時所建立的單一資料行統計資料,以及針對索引所建立的統計資料。This includes statistics created using the CREATE STATISTICS statement, single-column statistics created when AUTO_CREATE_STATISTICS is on, and statistics created for indexes.

如需 AUTO_CREATE_STATISTICS 的詳細資訊,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For more information about AUTO_CREATE_STATISTICS, see ALTER DATABASE SET Options (Transact-SQL). 若要檢視資料表或檢視表的所有索引,您可以使用 sp_helpindexTo view all indexes for a table or view, you can use sp_helpindex.

FULLSCANFULLSCAN
掃描資料表或索引檢視表中的所有資料列,藉以計算統計資料。Compute statistics by scanning all rows in the table or indexed view. FULLSCAN 和 SAMPLE 100 PERCENT 的結果相同。FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN 不能搭配 SAMPLE 選項一起使用。FULLSCAN cannot be used with the SAMPLE option.

SAMPLE number { PERCENT | ROWS }SAMPLE number { PERCENT | ROWS }
指定當查詢最佳化工具更新統計資料時,要在資料表或索引檢視表中使用的近似百分比或資料列數目。Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. 針對 PERCENT,number 可以介於 0 到 100 之間;針對 ROWS,number 可以介於 0 到總資料列數目之間。For PERCENT, number can be from 0 through 100 and for ROWS, number can be from 0 to the total number of rows. 查詢最佳化工具所取樣的實際百分比或資料列數目可能會與指定的百分比或數目不符。The actual percentage or number of rows the query optimizer samples might not match the percentage or number specified. 例如,查詢最佳化工具會掃描資料頁面上的所有資料列。For example, the query optimizer scans all rows on a data page.

在特殊情況下,根據預設取樣的查詢計劃並非最佳化,此時 SAMPLE 便非常有用。SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. 通常,查詢最佳化工具會依預設使用取樣並決定具有統計價值的取樣大小,因此不需要使用 SAMPLE 便可以建立高品質的查詢計劃。In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,使用相容性層級 130 時,對資料進行取樣以建置統計資料將會以平行方式完成,來改善收集統計資料的效能。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. 每當資料表大小超過某個臨界值時,查詢最佳化工具將使用平行樣本統計資料。The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.

SAMPLE 不能和 FULLSCAN 選項一起使用。SAMPLE cannot be used with the FULLSCAN option. 如果 SAMPLE 或 FULLSCAN 都未指定,查詢最佳化工具會依預設使用取樣資料並計算取樣大小。When neither SAMPLE nor FULLSCAN is specified, the query optimizer uses sampled data and computes the sample size by default.

我們建議您不要指定 0 PERCENT 或 0 ROWS。We recommend against specifying 0 PERCENT or 0 ROWS. 將 PERCENT 或 ROWS 指定為 0 時,雖會更新統計資料物件,但是不會包含統計資料。When 0 PERCENT or ROWS is specified, the statistics object is updated but does not contain statistics data.

大部分的工作負載都不需要進行完整掃描,且預設取樣就已足夠。For most workloads, a full scan is not required, and default sampling is adequate.
不過,某些會隨廣泛變化資料分佈波動的工作負載可能需要提高取樣的大小,甚至進行完整掃描。However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan.
如需詳細資訊,請參閱 CSS SQL 擴大服務部落格 (英文)。For more information, see the CSS SQL Escalation Services blog.

RESAMPLERESAMPLE
使用最新的取樣率更新每一項統計資料。Update each statistic using its most recent sample rate.

使用 RESAMPLE 可產生完整資料表掃描。Using RESAMPLE can result in a full-table scan. 例如,索引的統計資料會將完整資料表掃描用於其取樣率。For example, statistics for indexes use a full-table scan for their sample rate. 如果未指定任何取樣選項 (SAMPLE、FULLSCAN、RESAMPLE),查詢最佳化工具依預設會取樣資料並計算取樣大小。When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimizer samples the data and computes the sample size by default.

PERSIST_SAMPLE_PERCENT = { ON | OFF }PERSIST_SAMPLE_PERCENT = { ON | OFF }
若為 ON,統計資料將針對未明確指定取樣百分比的後續更新,保留設定取樣百分比。When ON, the statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage. 當設定為 OFF 時,統計資料取樣百分比將重設為未明確指定取樣百分比之後續更新中的預設取樣。When OFF, statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage. 預設值為 OFFThe default is OFF.

注意

若執行 AUTO_UPDATE_STATISTICS,它會在可用的情況下使用保存的取樣百分比,否則則會使用預設取樣百分比。If AUTO_UPDATE_STATISTICS is executed, it uses the persisted sampling percentage if available, or use default sampling percentage if not. RESAMPLE 行為不會受此選項影響。RESAMPLE behavior is not affected by this option.

提示

DBCC SHOW_STATISTICSsys.dm_db_stats_properties 會針對選取的統計資料公開保存的取樣百分比值。DBCC SHOW_STATISTICS and sys.dm_db_stats_properties expose the persisted sample percent value for the selected statistic.

適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x) (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4 開始) 至 SQL Server 2017SQL Server 2017 (從 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1 開始)。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4) through SQL Server 2017SQL Server 2017 (starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU1).

ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ] 強制重新計算包含 ON PARTITIONS 子句所指定之資料分割的分葉層級統計資料,然後合併來建置全域統計資料。ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ] Forces the leaf-level statistics covering the partitions specified in the ON PARTITIONS clause to be recomputed, and then merged to build the global statistics. 由於無法將使用不同取樣率建立的分割區區統計資料合併在一起,因此需要 WITH RESAMPLE。WITH RESAMPLE is required because partition statistics built with different sample rates cannot be merged together.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017

ALL | COLUMNS | INDEXALL | COLUMNS | INDEX
更新所有現有的統計資料、針對一或多個資料行所建立的統計資料,或是針對索引所建立的統計資料。Update all existing statistics, statistics created on one or more columns, or statistics created for indexes. 如果沒有指定任何選項,UPDATE STATISTICS 陳述式就會更新資料表或索引檢視表的所有統計資料。If none of the options are specified, the UPDATE STATISTICS statement updates all statistics on the table or indexed view.

NORECOMPUTENORECOMPUTE
針對指定的統計資料停用自動統計資料更新選項 AUTO_UPDATE_STATISTICS。Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. 如果您指定了這個選項,查詢最佳化工具就會完成這項統計資料更新並停用未來的更新。If this option is specified, the query optimizer completes this statistics update and disables future updates.

若要重新啟用 AUTO_UPDATE_STATISTICS 選項行為,請再次執行不含 NORECOMPUTE 選項的 UPDATE STATISTICS 或執行 sp_autostatsTo re-enable the AUTO_UPDATE_STATISTICS option behavior, run UPDATE STATISTICS again without the NORECOMPUTE option or run sp_autostats.

警告

使用這個選項可能會產生次佳查詢計劃。Using this option can produce suboptimal query plans. 我們建議您盡量少用這個選項,而且只有合格的系統管理員可以使用。We recommend using this option sparingly, and then only by a qualified system administrator.

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

INCREMENTAL = { ON | OFF }INCREMENTAL = { ON | OFF }
若設定為 ON,會依據每個資料分割統計資料重新建立統計資料。When ON, the statistics are recreated as per partition statistics. 若為 OFF,則會卸除統計資料樹狀結構,且 SQL ServerSQL Server 會重新計算統計資料。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 預設值為 OFFThe default is OFF.

如果不支援依據每個分割區區的統計資料,則會產生錯誤。If per partition statistics are not supported an error is generated. 針對下列統計資料類型,不支援累加統計資料:Incremental stats are not supported for following statistics types:

  • 建立統計資料時,所使用的索引未與基底資料表進行分割區對齊。Statistics created with indexes that are not partition-aligned with the base table.
  • 在 AlwaysOn 可讀取次要資料庫上建立的統計資料。Statistics created on Always On readable secondary databases.
  • 在唯讀資料庫上建立的統計資料。Statistics created on read-only databases.
  • 在篩選的索引上建立的統計資料。Statistics created on filtered indexes.
  • 在檢視上建立的統計資料。Statistics created on views.
  • 在內部資料表上建立的統計資料。Statistics created on internal tables.
  • 使用空間索引或 XML 索引建立的統計資料。Statistics created with spatial indexes or XML indexes.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism
適用於SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3 開始)。Applies to: SQL ServerSQL Server (Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU3).

在統計作業期間,覆寫 max degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option for the duration of the statistic operation. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
根據目前的系統工作負載,將平行統計作業所使用的處理器數目上限,限制為所指定的數目或更少的數目。Restricts the maximum number of processors used in a parallel statistic operation to the specified number or fewer based on the current system workload.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

<update_stats_stream_option> 僅供參考之用。Identified for informational purposes only. 不支援。Not supported. 我們無法保證未來的相容性。Future compatibility is not guaranteed.<update_stats_stream_option> 僅供參考之用。Identified for informational purposes only. 不支援。Not supported. 我們無法保證未來的相容性。Future compatibility is not guaranteed.

RemarksRemarks

使用 UPDATE STATISTICS 的時機When to Use UPDATE STATISTICS

如需 UPDATE STATISTICS 之使用時機的詳細資訊,請參閱統計資料For more information about when to use UPDATE STATISTICS, see Statistics.

限制事項Limitations and Restrictions

  • 不支援更新外部資料表上的統計資料。Updating statistics is not supported on external tables. 若要更新外部資料表上的統計資料,請卸除並重新建立統計資料。To update statistics on an external table, drop and re-create the statistics.
  • MAXDOP 選項與 STATS_STREAMROWCOUNTPAGECOUNT 選項不相容。The MAXDOP option is not compatible with STATS_STREAM, ROWCOUNT and PAGECOUNT options.
  • MAXDOP 選項受限於 Resource Governor 工作負載 MAX_DOP 設定 (如果已使用)。The MAXDOP option is limited by the Resource Governor workload group MAX_DOP setting, if used.

使用 sp_updatestats 來更新所有統計資料Updating All Statistics with sp_updatestats

如需如何針對資料庫中所有使用者定義和內部資料表更新統計資料的詳細資訊,請參閱預存程序 sp_updatestats (Transact-SQL)For information about how to update statistics for all user-defined and internal tables in the database, see the stored procedure sp_updatestats (Transact-SQL). 例如,下列命令會呼叫 sp_updatestats 來更新資料庫的所有統計資料。For example, the following command calls sp_updatestats to update all statistics for the database.

EXEC sp_updatestats;  

自動索引與統計資料管理Automatic index and statistics management

利用自適性索引重組等解決方案,為一或多個資料庫自動管理索引重組以及統計資料更新。Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 這項程序會根據索引分散程度與其他參數,自動選擇要進行重建或是重新組織索引,並以線性閾值更新統計資料。This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

判斷上次更新統計資料的時間Determining the Last Statistics Update

若要判斷上次更新統計資料的時間,請使用 STATS_DATE 函數。To determine when statistics were last updated, use the STATS_DATE function.

PDW / SQL 資料倉儲PDW / SQL Data Warehouse

PDW / SQL 資料倉儲不支援下列語法The following syntax is not supported by PDW / SQL Data Warehouse

update statistics t1 (a,b);   
update statistics t1 (a) with sample 10 rows;  
update statistics t1 (a) with NORECOMPUTE;  
update statistics t1 (a) with INCREMENTAL=ON;  
update statistics t1 (a) with stats_stream = 0x01;  

權限Permissions

必須具備資料表或檢視的 ALTER 權限。Requires ALTER permission on the table or view.

範例Examples

A.A. 更新資料表的所有統計資料Update all statistics on a table

下列範例會針對 SalesOrderDetail 資料表上的所有索引更新統計資料。The following example updates the statistics for all indexes on the SalesOrderDetail table.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO  

B.B. 更新索引的統計資料Update the statistics for an index

下列範例會針對 AK_SalesOrderDetail_rowguid 資料表的 SalesOrderDetail 索引更新統計資料。The following example updates the statistics for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO  

C.C. 使用 50% 取樣來更新統計資料Update statistics by using 50 percent sampling

下列範例會建立再更新 Name 資料表中 ProductNumberProduct 資料行的統計資料。The following example creates and then updates the statistics for the Name and ProductNumber columns in the Product table.

USE AdventureWorks2012;  
GO  
CREATE STATISTICS Products  
    ON Production.Product ([Name], ProductNumber)  
    WITH SAMPLE 50 PERCENT  
-- Time passes. The UPDATE STATISTICS statement is then executed.  
UPDATE STATISTICS Production.Product(Products)   
    WITH SAMPLE 50 PERCENT;  

D.D. 使用 FULLSCAN 和 NORECOMPUTE 來更新統計資料Update statistics by using FULLSCAN and NORECOMPUTE

下列範例會更新 Products 資料表中的 Product 統計資料、強制執行 Product 資料表中所有資料列的完整掃描,並且關閉 Products 統計資料的自動統計資料更新。The following example updates the Products statistics in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics for the Products statistics.

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

E.E. 更新資料表上的統計資料Update statistics on a table

下列範例會更新 Customer 資料表上的 CustomerStats1 統計資料。The following example updates the CustomerStats1 statistics on the Customer table.

UPDATE STATISTICS Customer ( CustomerStats1 );  

F.F. 使用完整掃描更新統計資料Update statistics by using a full scan

下列範例會根據掃描 Customer 資料表中的所有資料列來更新 CustomerStats1 統計資料。The following example updates the CustomerStats1 statistics, based on scanning all of the rows in the Customer table.

UPDATE STATISTICS Customer (CustomerStats1) WITH FULLSCAN;  

G.G. 更新資料表的所有統計資料Update all statistics on a table

下列範例會更新 Customer 資料表上的所有統計資料。The following example updates all statistics on the Customer table.

UPDATE STATISTICS Customer;  

另請參閱See Also

統計資料 Statistics
ALTER DATABASE (Transact-SQL) ALTER DATABASE (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_autostats (Transact-SQL) sp_autostats (Transact-SQL)
sp_updatestats (Transact-SQL) sp_updatestats (Transact-SQL)
STATS_DATE (Transact-SQL)STATS_DATE (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL) sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)sys.dm_db_stats_histogram (Transact-SQL)