CREATE STATISTICS (Transact-SQL)

文章
05/23/2024

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

在資料表、索引檢視表或外部資料表的一或多個資料行建立查詢最佳化統計資料。對於大部分的查詢，查詢優化器已經為高品質的查詢計劃產生必要的統計數據;在少數情況下，您需要使用或修改查詢設計來建立額外的統計數據 CREATE STATISTICS ，以改善查詢效能。

若要深入了解，請參閱統計資料。

注意

如需 Microsoft Fabric 中統計數據的詳細資訊，請參閱網狀架構數據倉儲中的統計數據。

Transact-SQL 語法慣例

Syntax

SQL Server、Azure SQL 資料庫和 Azure SQL 受控執行個體的語法。

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

Azure Synapse Analytics 和分析平台系統（PDW）的語法。

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Microsoft Fabric 的語法。

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

引數

statistics_name

要建立的統計數據名稱。

table_or_indexed_view_name

要在其中建立統計數據的數據表、索引檢視表或外部數據表的名稱。若要在另一個資料庫上建立統計資料，請指定限定的資料表名稱。

column [ ,...n]

要在統計資料中包含的一或多個資料行。資料行應該由左至右依優先順序排列。只有第一個資料行用來建立色階分佈圖。所有資料行都是用於名為密度的跨資料行相互關聯統計資料。

您可以指定任何可指定為索引鍵資料行的資料行，但下列例外狀況除外：

無法指定 xml、全文檢索和 FILESTREAM 數據行。
只有在和 QUOTED_IDENTIFIER 資料庫設定為 ON時ARITHABORT，才能指定計算數據行。
如果 CLR 使用者定義型別可支援二進位排序，您可以指定這個類型的資料行。如果方法標示為具決定性，就能指定從使用者定義型別資料行中定義為方法引動過程的計算資料行。

WHERE <filter_predicate>

指定運算式，以便選取在建立統計資料物件時要包含的資料列子集。使用篩選述詞所建立的統計資料稱為篩選的統計資料。篩選述詞使用簡單的比較邏輯，而且無法參考計算數據行、UDT 資料行、空間數據類型數據行或 hierarchyID 資料類型數據行。比較運算子不允許使用 NULL 常值進行比較。請改用 IS NULL 和 IS NOT NULL 運算子。

下面是一些 Production.BillOfMaterials 資料表之篩選述詞的範例：

WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

如需篩選述詞的詳細資訊，請參閱建立篩選的索引。

FULLSCAN

適用於：SQL Server 2016 （13.x） SP 1 CU 4、SQL Server 2017 （14.x） CU 1 和更新版本

透過掃描所有資料列來計算統計資料。 FULLSCAN 和 SAMPLE 100 PERCENT 具有相同的結果。 FULLSCAN 無法搭配 SAMPLE 選項使用。

省略時，SQL Server 會使用取樣來建立統計數據，並判斷建立高品質查詢計劃所需的樣本大小。

在 Microsoft Fabric 的倉儲中，僅支援單一數據行 FULLSCAN 和單一數據行 SAMPLE型統計數據。如果未包含任何選項， SAMPLE 則會建立統計數據。

SAMPLE number { PERCENT | ROWS }

指定數據表或索引檢視表中的近似百分比或數據列數目，以便查詢優化器在建立統計數據時使用。針對，number 可以是從 0 到 100，而針對 PERCENTROWS，number 可以從 0 到數據列總數。查詢最佳化工具所取樣的實際百分比或資料列數目可能會與指定的百分比或數目不符。例如，查詢最佳化工具會掃描資料頁面上的所有資料列。

SAMPLE 對於以預設取樣為基礎的查詢計劃不是最佳的特殊案例，會很有用。在大部分情況下，不需要指定 SAMPLE ，因為查詢優化器已經使用取樣，並預設會根據預設判斷統計顯著樣本大小，以建立高品質的查詢計劃。

SAMPLE 無法與 FULLSCAN 選項搭配使用。指定或FULLSCAN未指定時SAMPLE，查詢優化器會使用取樣的數據，並預設計算樣本大小。

我們建議不要指定 0 PERCENT 或 0 ROWS。指定或 0 ROWS 時0 PERCENT，會建立統計數據物件，但不包含統計數據數據。

在 Microsoft Fabric 的倉儲中，僅支援單一數據行 FULLSCAN 和單一數據行 SAMPLE型統計數據。如果未包含任何選項， FULLSCAN 則會建立統計數據。

PERSIST_SAMPLE_PERCENT = { ON | OFF }

當為時 ON，統計數據會針對未明確指定取樣百分比的後續更新保留建立取樣百分比。當時 OFF，統計數據取樣百分比會在未明確指定取樣百分比的後續更新中重設為默認取樣。預設值為 OFF。

注意

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

STATS_STREAM = stats_stream

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

NORECOMPUTE

針對 statistics_name 停用自動統計數據更新選項 AUTO_STATISTICS_UPDATE。如果您指定了這個選項，查詢最佳化工具就會針對 statistics_name 完成任何進行中的統計資料更新並停用未來的更新。

若要重新啟用統計數據更新，請使用DROP STATISTICS 移除統計數據，然後執行而不NORECOMPUTE使用 CREATE STATISTICS 選項。

警告

如果您停用統計數據的自動更新，可能會防止查詢優化器為涉及數據表的查詢挑選最佳執行計劃。您應該謹慎使用這個選項，而且只能由合格的資料庫管理員使用。

如需選項 AUTO_STATISTICS_UPDATE 的詳細資訊，請參閱 ALTER DATABASE SET 選項。如需停用及重新啟用統計資料更新的詳細資訊，請參閱統計資料。

INCREMENTAL = { ON | OFF }

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

當為時 ON，所建立的統計數據是每個數據分割統計數據。當為時 OFF，會合併所有數據分割的統計數據。預設值為 OFF。

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

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

MAXDOP = max_degree_of_parallelism

適用於：SQL Server 2016 （13.x） SP 2、SQL Server 2017 （14.x） CU 3 和更新版本

在 統計數據作業期間覆寫平行處理原則 組態選項的最大程度。如需詳細資訊，請參閱設定平行處理原則的最大程度（伺服器組態選項）。使用 MAXDOP 來限制平行計劃執行中使用的處理器數目。最大值是 64 個處理器。

max_degree_of_parallelism 可以是：

1：隱藏平行計劃產生。
>1：將平行索引作業中使用的處理器數目上限限製為指定的數位。
0 （預設值）：根據目前的系統工作負載，使用實際處理器數目或更少數目。

update_stats_stream_option

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

AUTO_DROP = { ON | OFF }

適用於：SQL Server 2022 （16.x）和更新版本，以及 Azure SQL 資料庫、Azure SQL 受控執行個體

在 SQL Server 2022 （16.x）之前，如果使用者或第三方工具在用戶資料庫上手動建立統計數據，這些統計數據物件可能會封鎖或干擾客戶可能想要的架構變更。

從 SQL Server 2022 （16.x）開始， AUTO_DROP 此選項預設會在所有新的和已移轉的資料庫上啟用。屬性AUTO_DROP允許在模式中建立統計數據物件，讓統計數據物件不會封鎖後續的架構變更，而是視需要卸除統計數據。如此一來，以手動方式建立已啟用 AUTO_DROP 的統計數據的行為就像自動建立的統計數據。

注意

嘗試在自動建立的統計數據上設定或取消設定 Auto_Drop 屬性可能會引發錯誤。自動建立的統計資料一律會使用自動卸除。某些備份在還原時可能會設定此屬性不正確，直到下次更新統計數據物件時（手動或自動）。不過，自動建立的統計資料一律會以和自動卸除統計資料相同的方式運作。從舊版還原資料庫至 SQL Server 2022 （16.x）時，建議在資料庫上執行 sp_updatestats ，設定統計數據 AUTO_DROP 功能的適當元數據。

如需詳細資訊，請參閱 AUTO_DROP 選項。

權限

需要下列權限其中一個權限：

ALTER TABLE
使用者是資料表擁有者
db_ddladmin 固定資料庫角色中的成員資格

備註

在建置統計資料之前，SQL Server 可以使用 tempdb 將取樣的資料列排序。

外部資料表的統計資料

建立外部資料表統計資料時，SQL Server 會將外部資料表匯入暫存的 SQL Server 資料表，然後建立統計資料。針對範例統計資料，只有取樣資料列會匯入。如果您有大型外部數據表，則使用預設取樣的速度會比較快，而不是完整掃描選項。

當外部數據表使用 DELIMITEDTEXT、 CSV、 PARQUET或 DELTA 做為資料類型時，外部數據表僅支援每個 CREATE STATISTICS 命令一個數據行的統計數據。

具有已篩選條件的統計資料

對於從定義完善的資料子集中選取的查詢而言，篩選的統計資料可以改善查詢效能。篩選的統計資料會在 WHERE 子句中使用篩選述詞來選取統計資料中所含的資料子集。

使用 CREATE STATISTICS 的時機

如需 CREATE STATISTICS 之使用時機的詳細資訊，請參閱統計資料。

篩選統計資料的參考相依性

sys.sql_expression_dependencies目錄檢視會將篩選統計資料述詞中的每個資料行當做參考相依性來追蹤。在建立篩選的統計數據之前，請考慮您在數據表數據行上執行的作業。您無法卸除、重新命名或改變篩選統計數據述詞中定義的數據表數據行定義。

限制

不支援在外部資料表上更新統計資料。若要更新外部資料表上的統計資料，請卸除並重新建立統計資料。
您最多可以針對每個統計資料物件列出 64 個資料行。
選項MAXDOP與、 ROWCOUNT和 PAGECOUNT 選項不相容STATS_STREAM。
MAXDOP 選項受限於 Resource Governor 工作負載 MAX_DOP 設定 (如果已使用)。
CREATEAzure SQL 資料庫不支援在外部資料表上使用和 DROP STATISTICS 。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 搭配 SAMPLE 數目 PERCENT 使用 CREATE STATISTICS

下列範例會 ContactMail1 使用 AdventureWorks2022 資料庫數據表之 5% BusinessEntityID 和 EmailPromotion Person 數據行的隨機樣本建立統計數據。

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. 搭配 FULLSCAN 和 NORECOMPUTE 使用 CREATE STATISTICS

下列範例會針對 NamePurchase 資料表的 BusinessEntityID 和 EmailPromotion 資料行中的所有資料列來建立 Person 統計資料，且會停用統計資料的自動重新計算。

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. 使用 CREATE STATISTICS 來建立經篩選的統計資料

下列範例會建立篩選的統計資料 ContactPromotion1。資料庫引擎會取樣 50% 的資料，然後選取 EmailPromotion 等於 2 的所有資料列。

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. 在外部資料表上建立統計資料

在外部資料表上建立統計資料時，除了提供資料行清單之外，您唯一要做的決定是要透過為資料列進行取樣或透過掃描所有資料列來建立統計資料。 CREATEAzure SQL 資料庫不支援在外部資料表上使用和 DROP STATISTICS 。

由於 SQL Server 會將外部數據表的數據匯入臨時表以建立統計數據，因此完整掃描選項需要更長的時間。針對大型資料表，預設的取樣方法通常就已足夠。

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. 搭配 FULLSCAN 和 PERSIST_SAMPLE_PERCENT 使用 CREATE STATISTICS

下列範例會 NamePurchase 建立數據表和 BusinessEntityID EmailPromotion 數據行中所有數據列的 Person 統計數據，併為未明確指定取樣百分比的所有後續更新設定 100% 取樣百分比。

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

使用 AdventureWorksDW 資料庫的範例

F. 建立兩個資料行的統計資料

下列範例會根據 DimCustomer 資料表的 CustomerKey 和 EmailAddress 資料行，建立 CustomerStats1 統計資料。該統計資料是根據 Customer 資料表中統計上很重要的資料列取樣而建立的。

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. 使用完整掃描建立統計資料

下列範例會根據掃描 DimCustomer 資料表中的所有資料列來建立 CustomerStatsFullScan 統計資料。

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. 透過指定取樣百分比來建立統計資料

下列範例會根據掃描 DimCustomer 資料表中 50% 的資料列來建立 CustomerStatsSampleScan 統計資料。

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. 搭配 AUTO_DROP 使用 CREATE STATISTICS

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

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

如需評估現有統計資料的自動卸除設定，請使用 sys.stats 中的 auto_drop 資料行：

SELECT object_id, [name], auto_drop
FROM sys.stats;

分享方式：

CREATE STATISTICS (Transact-SQL)

Syntax

引數

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

權限

備註

外部資料表的統計資料

具有已篩選條件的統計資料

使用 CREATE STATISTICS 的時機

篩選統計資料的參考相依性

限制

範例

A. 搭配 SAMPLE 數目 PERCENT 使用 CREATE STATISTICS

B. 搭配 FULLSCAN 和 NORECOMPUTE 使用 CREATE STATISTICS

C. 使用 CREATE STATISTICS 來建立經篩選的統計資料

D. 在外部資料表上建立統計資料

E. 搭配 FULLSCAN 和 PERSIST_SAMPLE_PERCENT 使用 CREATE STATISTICS

使用 AdventureWorksDW 資料庫的範例

F. 建立兩個資料行的統計資料

G. 使用完整掃描建立統計資料

H. 透過指定取樣百分比來建立統計資料

I. 搭配 AUTO_DROP 使用 CREATE STATISTICS

意見反映

更多資源

分享方式：

CREATE STATISTICS (Transact-SQL)

Syntax

引數

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

權限

備註

外部資料表的統計資料

具有已篩選條件的統計資料

使用 CREATE STATISTICS 的時機

篩選統計資料的參考相依性

限制

範例

A. 搭配 SAMPLE 數目 PERCENT 使用 CREATE STATISTICS

B. 搭配 FULLSCAN 和 NORECOMPUTE 使用 CREATE STATISTICS

C. 使用 CREATE STATISTICS 來建立經篩選的統計資料

D. 在外部資料表上建立統計資料

E. 搭配 FULLSCAN 和 PERSIST_SAMPLE_PERCENT 使用 CREATE STATISTICS

使用 AdventureWorksDW 資料庫的範例

F. 建立兩個資料行的統計資料

G. 使用完整掃描建立統計資料

H. 透過指定取樣百分比來建立統計資料

I. 搭配 AUTO_DROP 使用 CREATE STATISTICS

相關內容

意見反映

更多資源