CREATE STATISTICS (Transact-SQL)

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

在資料表、索引檢視表或外部資料表的一或多個資料行建立查詢最佳化統計資料。 對於大部分查詢而言,查詢最佳化工具已經產生高品質查詢計劃的必要統計資料。不過,在少數情況下,您必須使用 CREATE STATISTICS 來建立其他統計資料或修改查詢設計,以便改善查詢效能。

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

注意

如需 Microsoft Fabric 中統計資料的詳細資訊,請參閱 Microsoft Fabric 中的統計資料。

Transact-SQL 語法慣例

Syntax

-- Syntax for SQL Server and Azure SQL Database
-- 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 ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
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 | = | <> | != | > | >= | !> | < | <= | !<
-- Syntax for 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
      }
    ]
[;]

注意

若要檢視 SQL Server 2014 (12.x) 和舊版的 Transact-SQL 語法,請參閱 舊版檔

引數

statistics_name

這是要建立之統計資料的名稱。

table_or_indexed_view_name

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

column [ ,...n]

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

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

  • 您無法指定 XML、全文檢索和 FILESTREAM 資料行。

  • 只有在 ARITHABORT 和 QUOTED_IDENTIFIER 資料庫設定為 ON 時,才能指定計算資料行。

  • 如果 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) (從 SQL Server 2016 (13.x) SP1 CU4 開始) 及更新版本 (從 SQL Server 2017 (14.x) CU1 開始)

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

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

在 Microsoft Fabric 中的 Warehouse 中,僅支援單一資料行 FULLSCAN 和單一資料行 SAMPLE 型統計資料。 如果未包含任何選項,則會建立 FULLSCAN 統計資料。

SAMPLE number { PERCENT | ROWS }

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

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

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

我們建議不要指定 0 PERCENT0 ROWS。 指定 0 PERCENT0 ROWS 時,雖會建立統計資料物件,但是不會包含統計資料。

在 Microsoft Fabric 中的 Warehouse 中,僅支援單一資料行 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 選項 (Transact-SQL)。 如需停用及重新啟用統計資料更新的詳細資訊,請參閱統計資料

INCREMENTAL = { ON | OFF }

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

若設定為 ON,所建立的統計資料會以每個資料分割統計資料為依據。 設定為 OFF 時,會合併所有資料分割的統計資料。 預設值為 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 }

適用於:Azure SQL Database、Azure SQL 受控執行個體,以及從 SQL Server 2022 (16.x) 開始

在 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 資料表,然後建立統計資料。 針對範例統計資料,只有取樣資料列會匯入。 如果您有大型外部資料表,則使用預設取樣而不是完整掃描選項會比較快。

當外部資料表使用 DELIMITEDTEXTCSVPARQUETDELTA 做為資料類型時,外部資料表僅支援每個 CREATE STATISTICS 命令一個資料行的統計資料。

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

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

使用 CREATE STATISTICS 的時機

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

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

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

限制事項

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

範例

範例會使用 AdventureWorks 資料庫。

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

下列範例會 ContactMail1 使用 AdventureWorks2022 資料庫資料表之 5% BusinessEntityIDEmailPromotionPerson 資料行的隨機樣本來建立統計資料。

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

B. 搭配 FULLSCAN 和 NORECOMPUTE 使用 CREATE STATISTICS

下列範例會針對 NamePurchase 資料表的 BusinessEntityIDEmailPromotion 資料行中的所有資料列來建立 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. 在外部資料表上建立統計資料

在外部資料表上建立統計資料時,除了提供資料行清單之外,您唯一要做的決定是要透過為資料列進行取樣或透過掃描所有資料列來建立統計資料。 Azure SQL Database 不支援在外部資料表上使用 CREATE 和 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

下列範例會針對 BusinessEntityID 中所有資料列和 Person 資料表中的 EmailPromotion 資料行建立 NamePurchase 統計資料,並針對未明確指定取樣百分比的所有後續更新,設定 100% 取樣百分比。

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

使用 AdventureWorksDW 資料庫的範例

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

下列範例會根據 DimCustomer 資料表的 CustomerKeyEmailAddress 資料行,建立 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;

下一步