UPDATE STATISTICS (Transact-SQL)

適用于:SQL ServerAzure SQL資料庫Azure SQL 受控執行個體Azure Synapse Analytics AnalyticsPlatform System (PDW)

針對資料表或索引檢視表更新查詢最佳化統計資料。 根據預設,查詢最佳化工具已經會視需要更新統計資料來改善查詢計劃。在某些情況下,您可以使用 UPDATE STATISTICSsp_updatestats 預存程序,讓統計資料的更新頻率高於預設更新頻率,以改善查詢效能。

更新統計資料可確保查詢使用最新的統計資料進行編譯。 透過任何進程更新統計資料可能會導致查詢計劃自動重新編譯。 建議您不要太頻繁地更新統計資料,因為改善查詢計劃與重新編譯查詢所需的時間之間會有效能取捨。 特定的權衡取捨完全取決於您的應用程式。 UPDATE STATISTICStempdb可用來排序用於建置統計資料的資料列範例。

Transact-SQL 語法慣例

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 ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]  

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

注意

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

引數

table_or_indexed_view_name

這是包含統計資料物件的資料表或索引檢視表名稱。

index_or_statistics_name

這是要更新統計資料之索引的名稱,或是要更新之統計資料的名稱。 如果未指定 index_or_statistics_name ,查詢最佳化工具會更新資料表或索引檢視表的所有統計資料。 這包括使用 CREATE STATISTICS 陳述式所建立的統計資料、開啟 AUTO_CREATE_STATISTICS 時所建立的單一資料行統計資料,以及針對索引所建立的統計資料。

如需AUTO_CREATE_STATISTICS的詳細資訊,請參閱 ALTER DATABASE SET Options (Transact-SQL) 。 若要檢視資料表或檢視表的所有索引,您可以使用 sp_helpindex

FULLSCAN

掃描資料表或索引檢視表中的所有資料列,藉以計算統計資料。 FULLSCAN 和 SAMPLE 100 PERCENT 的結果相同。 FULLSCAN 不能搭配 SAMPLE 選項一起使用。

SAMPLE number { PERCENT | ROWS }

指定當查詢最佳化工具更新統計資料時,要在資料表或索引檢視表中使用的近似百分比或資料列數目。 針對 PERCENT,number 可以介於 0 到 100 之間;針對 ROWS,number 可以介於 0 到總資料列數目之間。 查詢最佳化工具所取樣的實際百分比或資料列數目可能會與指定的百分比或數目不符。 例如,查詢最佳化工具會掃描資料頁面上的所有資料列。

SAMPLE 適用于以預設取樣為基礎的查詢計劃不是最佳情況的特殊案例。 在大部分情況下,不需要指定 SAMPLE,因為查詢最佳化工具會使用取樣,並預設判斷統計顯著樣本大小,因為需要建立高品質的查詢計劃。

從 2016 SQL Server (13.x) 開始,使用相容性層級 130 時,會平行完成建立統計資料的資料取樣,以改善統計資料收集的效能。 每當資料表大小超過某個臨界值時,查詢最佳化工具將使用平行樣本統計資料。

SAMPLE 不能和 FULLSCAN 選項一起使用。 如果 SAMPLE 或 FULLSCAN 都未指定,查詢最佳化工具會依預設使用取樣資料並計算取樣大小。

我們建議您不要指定 0 PERCENT 或 0 ROWS。 指定 0 PERCENT 或 ROWS 時,統計資料物件會更新,但不包含統計資料資料。

對於大部分的工作負載而言,不需要完整掃描,而且預設取樣就足夠。 不過,某些會隨廣泛變化資料分佈波動的工作負載可能需要提高取樣的大小,甚至進行完整掃描。 如需詳細資訊,請參閱 CSS SQL 呈報服務部落格

RESAMPLE

使用最新的取樣率更新每一項統計資料。

使用 RESAMPLE 可產生完整資料表掃描。 例如,索引的統計資料會將完整資料表掃描用於其取樣率。 如果未指定任何取樣選項 (SAMPLE、FULLSCAN、RESAMPLE),查詢最佳化工具依預設會取樣資料並計算取樣大小。

PERSIST_SAMPLE_PERCENT = { ON | OFF }

當開啟時,統計資料會保留未明確指定取樣百分比的後續更新的設定取樣百分比。 當關閉時,統計資料取樣百分比會在未明確指定取樣百分比的後續更新中重設為預設取樣。 預設值為 OFF

注意

若執行 AUTO_UPDATE_STATISTICS,它會在可用的情況下使用保存的取樣百分比,否則則會使用預設取樣百分比。 RESAMPLE 行為不受此選項影響。

注意

如果資料表遭到截斷,則所有以遭截斷 HoBT 為基礎建置的統計資料都會還原至使用預設取樣百分比。

注意

在SQL Server中,重建先前已使用 PERSIST_SAMPLE_PERCENT 更新統計資料的索引時,保存的範例百分比會重設為預設值。 從 2016 SQL Server 2016 (13.x) SP2 CU17 開始,SQL Server 2017 (14.x) CU26 和 SQL Server 2019 (15.x) CU10,即使重建索引時,仍會保留保存的樣本百分比。

提示

DBCC SHOW_STATISTICSsys.dm_db_stats_properties 會針對選取的統計資料公開保存的取樣百分比值。

適用于SQL Server (:從 2016 SQL Server 2016 (13.x) SP1 CU4 和 SQL Server 2017 (14.x) CU1) 、Azure SQL Database 和 Azure SQL 受控執行個體

ON PARTITIONS ( { < partition_number > | <range > } [, ...n] ) ]

強制重新計算包含 ON PARTITIONS 子句所指定之分割區區的分葉層級統計資料,然後合併以建立全域統計資料。 需要 WITH RESAMPLE,因為以不同取樣率建置的資料分割統計資料無法合併在一起。

適用于:SQL Server 2014 (12.x) 和更新版本

ALL | COLUMNS | INDEX

更新所有現有的統計資料、針對一或多個資料行所建立的統計資料,或是針對索引所建立的統計資料。 如果沒有指定任何選項,UPDATE STATISTICS 陳述式就會更新資料表或索引檢視表的所有統計資料。

NORECOMPUTE

針對指定的統計資料停用自動統計資料更新選項 AUTO_UPDATE_STATISTICS。 如果您指定了這個選項,查詢最佳化工具就會完成這項統計資料更新並停用未來的更新。

若要重新啟用 AUTO_UPDATE_STATISTICS 選項行為,請再次執行不含 NORECOMPUTE 選項的 UPDATE STATISTICS 或執行 sp_autostats

警告

使用這個選項可能會產生次佳查詢計劃。 我們建議您盡量少用這個選項,而且只有合格的系統管理員可以使用。

如需AUTO_STATISTICS_UPDATE選項的詳細資訊,請參閱 ALTER DATABASE SET Options (Transact-SQL)

INCREMENTAL = { ON | OFF }

若設定為 ON,會依據每個資料分割統計資料重新建立統計資料。 當 OFF時,會卸載統計資料樹狀結構,並SQL Server重新計算統計資料。 預設值為 OFF

如果不支援每個分割區統計資料,則會產生錯誤。 下列統計資料類型不支援累加統計資料:

  • 使用未與基表對齊之索引所建立的統計資料。
  • 在 AlwaysOn 可讀取次要資料庫上建立的統計資料。
  • 在唯讀資料庫上建立的統計資料。
  • 在篩選的索引上建立的統計資料。
  • 在檢視上建立的統計資料。
  • 在內部資料表上建立的統計資料。
  • 使用空間索引或 XML 索引建立的統計資料。

適用于:SQL Server 2014 (12.x) 和更新版本

MAXDOP = max_degree_of_parallelism

適用于:SQL Server (從 SQL Server 2016 (13.x) SP2 開始,SQL Server 2017 (14.x) CU3) 。

在統計作業期間,覆寫 max degree of parallelism 設定選項。 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項。 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。 最大值是 64 個處理器。

max_degree_of_parallelism 可以是:

1
隱藏平行計畫的產生。

>1
根據目前的系統工作負載,將平行統計作業所使用的處理器數目上限,限制為所指定的數目或更少的數目。

0 (預設值)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。

<update_stats_stream_option>

僅供參考之用。 不支援。 我們無法保證未來的相容性。

AUTO_DROP = { ON |OFF }

適用于:SQL Server 2022 (16.x) 和更新版本。

目前,如果統計資料是由客戶資料庫上的協力廠商工具所建立,這些統計資料物件可能會封鎖或干擾客戶可能想要的架構變更。

(從 SQL Server 2022 (16.x) ) | 開始此功能允許在模式中建立統計資料物件,讓統計資料不會封鎖架構變更,而是會卸載統計資料。 如此一來,自動卸載統計資料的行為就如同自動建立的統計資料。

注意

嘗試在自動建立的統計資料上設定或取消設定 Auto_Drop 屬性可能會引發錯誤 - 自動建立的統計資料一律會使用自動卸載。 還原時,某些備份可能會有此屬性設定不正確,直到下次統計資料物件手動或自動) 更新 (為止。 不過,自動建立的統計資料一律會像自動卸載統計資料一樣。

備註

使用 UPDATE STATISTICS 的時機

如需 UPDATE STATISTICS 之使用時機的詳細資訊,請參閱統計資料

限制事項

  • 外部資料表不支援更新統計資料。 若要更新外部資料表上的統計資料,請卸除並重新建立統計資料。
  • 選項 MAXDOP 與 和 ROWCOUNTPAGECOUNT 選項不相容 STATS_STREAM
  • MAXDOP 選項受限於 Resource Governor 工作負載 MAX_DOP 設定 (如果已使用)。

使用 sp_updatestats 更新所有統計資料

如需如何更新資料庫中所有使用者定義和內部資料表統計資料的資訊,請參閱 Transact-SQL) 預 存程式sp_updatestats (。 例如,下列命令會呼叫 sp_updatestats 來更新資料庫的所有統計資料。

EXEC sp_updatestats;  

自動索引與統計資料管理

利用自適性索引重組等解決方案,為一或多個資料庫自動管理索引重組以及統計資料更新。 這項程序會根據索引分散程度與其他參數,自動選擇要進行重建或是重新組織索引,並以線性閾值更新統計資料。

判斷上次更新統計資料的時間

若要判斷上次更新統計資料的時間,請使用 STATS_DATE 函數。

PDW / Azure Synapse Analytics

Analytics Platform System (PDW) / Azure Synapse Analytics 不支援下列語法

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;  

權限

必須具備資料表或檢視的 ALTER 權限。

範例

A. 更新資料表的所有統計資料

下列範例會更新 SalesOrderDetail 資料表上的所有統計資料。

USE AdventureWorks2012;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO  

B. 更新索引的統計資料

下列範例會針對 AK_SalesOrderDetail_rowguid 資料表的 SalesOrderDetail 索引更新統計資料。

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

C. 使用 50% 取樣來更新統計資料

下列範例會建立再更新 Name 資料表中 ProductNumberProduct 資料行的統計資料。

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. 使用 FULLSCAN 和 NORECOMPUTE 來更新統計資料

下列範例會更新 Products 資料表中的 Product 統計資料、強制執行 Product 資料表中所有資料列的完整掃描,並且關閉 Products 統計資料的自動統計資料更新。

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

範例:Azure Synapse Analytics and Analytics Platform System (PDW)

E. 更新資料表上的統計資料

下列範例會更新 Customer 資料表上的 CustomerStats1 統計資料。

UPDATE STATISTICS Customer (CustomerStats1);  

F. 使用完整掃描更新統計資料

下列範例會根據掃描 Customer 資料表中的所有資料列來更新 CustomerStats1 統計資料。

UPDATE STATISTICS Customer (CustomerStats1) WITH FULLSCAN;  

G. 更新資料表的所有統計資料

下列範例會更新 Customer 資料表上的所有統計資料。

UPDATE STATISTICS Customer;

H. 搭配使用 CREATE STATISTICS 與 AUTO_DROP

若要使用自動卸載統計資料,只要將下列內容新增至統計資料建立或更新的 「WITH」 子句即可。

UPDATE STATISTICS Customer (CustomerStats1) WITH AUTO_DROP = ON

另請參閱

下一步