UPDATE STATISTICS (Transact-SQL)

文章
02/13/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

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

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

注意

如需 Microsoft Fabric 中統計數據的詳細資訊，請參閱 Microsoft Fabric 中的統計數據。

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   
        }  
    ]  
[;]

-- Syntax for Microsoft Fabric

UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
        }  
    ]  
[;]

注意

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

引數

table_or_indexed_view_name

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

index_or_statistics_name或statistics_name | index_name或statistics_name

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

如需AUTO_CREATE_STATISTICS的詳細資訊，請參閱 ALTER DATABASE SET 選項。若要檢視資料表或檢視表的所有索引，您可以使用 sp_helpindex。

FULLSCAN

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

SAMPLE number { PERCENT | ROWS }

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

在特殊情況下，根據預設取樣的查詢計劃並非最佳化，此時 SAMPLE 便非常有用。通常，查詢最佳化工具會依預設使用取樣並決定具有統計價值的取樣大小，因此不需要使用 SAMPLE 便可以建立高品質的查詢計劃。

注意

在 SQL Server 2016 （13.x）中使用資料庫相容性層級 130 時，會平行進行數據取樣來建置統計數據，以改善統計數據收集的效能。每當數據表大小超過特定閾值時，查詢優化器就會使用平行範例統計數據。從 SQL Server 2017 （14.x）開始，不論資料庫相容性層級為何，行為都會變更回使用序列掃描，以避免發生過多 LATCH 等候的潛在效能問題。更新統計數據時，其餘的查詢計劃將會在限定時維持平行執行。

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

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

大部分的工作負載都不需要進行完整掃描，且預設取樣就已足夠。不過，某些會隨廣泛變化資料分佈波動的工作負載可能需要提高取樣的大小，甚至進行完整掃描。雖然估計值可能比取樣掃描更精確，但複雜的計劃可能沒有大幅受益。

如需詳細資訊，請參閱統計數據的元件和概念。

RESAMPLE

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

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

在 Microsoft Fabric 的倉儲中，不支援 RESAMPLE。

PERSIST_SAMPLE_PERCENT = { ON | OFF }

適用於：SQL Server 2016 （13.x） Service Pack 1 CU4、SQL Server 2017 （14.x） Service Pack 1 或 SQL Server 2019 （15.x）和更新版本、Azure SQL 資料庫、Azure SQL 受控執行個體

若為 ON，統計資料將針對未明確指定取樣百分比的後續更新，保留設定取樣百分比。若為 OFF，統計資料取樣百分比將重設為未明確指定取樣百分比之後續更新中的預設取樣。預設值為 OFF。

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

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

如果資料表遭到截斷，則以截斷堆積或 B 型樹狀結構（HoBT）為基礎的所有統計資料都會使用預設取樣百分比還原為。

注意

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

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

適用於：SQL Server 2014 (12.x) 及更新版本

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

ALL | COLUMNS | INDEX

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

NORECOMPUTE

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

若要重新啟用AUTO_UPDATE_STATISTICS選項行為，請再次執行 UPDATE STATISTICS 而不使用 NORECOMPUTE 選項或執行 sp_autostats。

警告

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

如需AUTO_STATISTICS_UPDATE選項的詳細資訊，請參閱 ALTER DATABASE SET 選項。

INCREMENTAL = { ON | OFF }

適用於：SQL Server 2014 (12.x) 及更新版本

若設定為 ON，會依據每個資料分割統計資料重新建立統計資料。若為 OFF，則會卸除統計資料樹狀結構，且 SQL Server 會重新計算統計資料。預設值為 OFF。

如果不支援依據每個分割區的統計資料，則會產生錯誤。針對下列統計資料類型，不支援累加統計資料：

建立統計資料時，所使用的索引未與基底資料表進行分割區對齊。
在 AlwaysOn 可讀取次要資料庫上建立的統計資料。
在唯讀資料庫上建立的統計資料。
在篩選的索引上建立的統計資料。
在檢視上建立的統計資料。
在內部資料表上建立的統計資料。
使用空間索引或 XML 索引建立的統計資料。

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的詳細資訊，請參閱何時更新統計數據。

限制

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

使用 sp_updatestats 更新所有統計數據

如需如何更新資料庫中所有使用者定義和內部數據表統計數據的相關信息，請參閱預存程式 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 AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO

B. 更新索引的統計資料

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

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

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

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

USE AdventureWorks2022;
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 AdventureWorks2022;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO

範例：Azure Synapse Analytics 和 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. 搭配 AUTO_DROP 使用 CREATE STATISTICS

如需使用自動卸除統計資料，只要將下列內容新增至統計資料建立或更新的 "WITH" 子句即可。

UPDATE STATISTICS Customer (CustomerStats1) WITH AUTO_DROP = ON

分享方式：

UPDATE STATISTICS (Transact-SQL)

Syntax

引數

table_or_indexed_view_name

index_or_statistics_name或statistics_name | index_name或statistics_name

FULLSCAN

SAMPLE number { PERCENT | ROWS }

RESAMPLE

PERSIST_SAMPLE_PERCENT = { ON | OFF }

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

ALL | COLUMNS | INDEX

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

備註

更新統計數據的時機

限制

使用 sp_updatestats 更新所有統計數據

自動索引與統計資料管理

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

PDW / Azure Synapse Analytics

權限

範例

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

B. 更新索引的統計資料

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

D. 使用 FULLSCAN 和 NORECOMPUTE 來更新統計資料

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

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

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

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

H. 搭配 AUTO_DROP 使用 CREATE STATISTICS

意見反映

更多資源

分享方式：

UPDATE STATISTICS (Transact-SQL)

Syntax

引數

table_or_indexed_view_name

index_or_statistics_name或statistics_name | index_name或statistics_name

FULLSCAN

SAMPLE number { PERCENT | ROWS }

RESAMPLE

PERSIST_SAMPLE_PERCENT = { ON | OFF }

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

ALL | COLUMNS | INDEX

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

備註

更新統計數據的時機

限制

使用 sp_updatestats 更新所有統計數據

自動索引與統計資料管理

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

PDW / Azure Synapse Analytics

權限

範例

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

B. 更新索引的統計資料

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

D. 使用 FULLSCAN 和 NORECOMPUTE 來更新統計資料

範例：Azure Synapse Analytics 和 Analytics Platform System (PDW)

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

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

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

H. 搭配 AUTO_DROP 使用 CREATE STATISTICS

相關內容

意見反映

更多資源