Azure Synapse Analytics 中專用 SQL 集區的資料表統計資料

發行項
06/01/2023

在本文中，您會看到專用 SQL 集區中，建立和更新資料表查詢最佳化統計資料的建議與範例。

為何使用統計資料

專用 SQL 集區越了解您的資料，執行查詢的速度就越快。在 SQL 集區載入資料後，若要讓查詢最佳化，最重要的是收集資料的統計資料。

專用 SQL 集區查詢最佳化工具是成本型最佳化工具。它會比較各種查詢方案的成本，然後選擇成本最低的方案。在大部分的情況下，它會選擇執行最快的方案。

例如，如果最佳化工具評估您的查詢篩選的日期會傳回一個資料列，則會選擇一個方案。如果最佳化工具評估所選取的日期將傳回 100 萬個資料列，則會傳回不同的方案。

自動建立統計資料

當資料庫 AUTO_CREATE_STATISTICS 選項為開啟時，專用 SQL 集區會分析傳入的使用者查詢是否缺少統計資料。

如果遺漏統計資料，查詢最佳化工具會在查詢述詞或聯結條件中建立個別資料行的統計資料，以改善查詢計劃的基數估計值。

注意

自動建立統計資料目前依預設開啟。

您可以執行下列命令，檢查專用 SQL 集區是否設定 AUTO_CREATE_STATISTICS：

SELECT name, is_auto_create_stats_on
FROM sys.databases

如果專用 SQL 集區未設定 AUTO_CREATE_STATISTICS，建議您執行下列命令啟用屬性：

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

下列陳述式會觸發統計資料的自動建立程序：

SELECT
INSERT-SELECT
CTAS
UPDATE
刪除
當偵測到包含聯結或述詞存在時，EXPLAIN

注意

不會在暫存或外部資料表上建立自動建立統計資料。

自動建立統計資料是同步進行的，因此，如果您的資料行缺少統計資料，查詢效能可能會略為降低。為單一資料行建立統計資料的時間，取決於資料表的大小。

若要避免測量效能降低，請在分析系統前，先執行基準測試的工作負載，以確定會先建立統計資料。

注意

統計資料的建立會記錄在不同使用者內容下的 sys.dm_pdw_exec_requests。

自動統計資料建立後，會採用下列格式：WA_Sys< 十六進位的 8 位數資料行識別碼>_<十六進位的 8 位數資料表識別碼>。您可以執行 DBCC SHOW_STATISTICS 命令來檢視已建立的統計資料：

DBCC SHOW_STATISTICS (<table_name>, <target>)

table_name 是資料表，包含顯示的統計資料。資料表不得為外部資料表。目標是用來顯示統計資料資訊的目標索引、統計資料或資料行名稱。

更新統計資料

其中一個最佳做法，是隨著新增新的日期，每天在日期資料行上更新統計資料。專用 SQL 集區每次載入新的資料列，就會加入新的載入日期或交易日期。這些增加項目會造成資料散發變更，並使統計資料過時。

客戶資料表中國家/地區資料行的統計資料，可能永遠不需更新，因為值的散發通常不會變更。假設客戶間的散發固定不變，將新資料列加入至資料表變化並不會改變資料散發情況。

但如果專用 SQL 集區只有一個國家/地區，您卻帶入新的國家/地區資料，導致集區儲存多個國家/地區的資料時，即須更新國家/地區資料行的統計資料。

以下為更新統計資料的相關建議：

統計資料屬性	建議
統計資料更新的頻率	保守：載入或轉換資料後，每天
取樣	小於 10 億個資料列，使用預設取樣 (20%)。大於 10 億個資料列，使用 2% 的取樣。

為查詢疑難排解時，首先要詢問的問題之一就是「統計資料是最新的嗎？」

這個問題不是可依資料存留期回答的問題。如果基礎資料並沒有任何實質變更，最新的統計資料物件可能會是舊的。當資料列數目已顯著變更，或資料行的值散發有實質變更時，就應該更新統計資料。

無動態管理檢視可判斷上次統計資料更新後，資料表中的資料是否變更。下列兩個查詢有助判斷統計資料是否過時。

查詢 1：找出統計資料的資料列計數 (stats_row_count) 與實際資料列計數 (actual_row_count) 的差異。

select 
objIdsWithStats.[object_id], 
actualRowCounts.[schema], 
actualRowCounts.logical_table_name, 
statsRowCounts.stats_row_count, 
actualRowCounts.actual_row_count,
row_count_difference = CASE
    WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count THEN actualRowCounts.actual_row_count - statsRowCounts.stats_row_count
    ELSE statsRowCounts.stats_row_count - actualRowCounts.actual_row_count
END,
percent_deviation_from_actual = CASE
    WHEN actualRowCounts.actual_row_count = 0 THEN statsRowCounts.stats_row_count
    WHEN statsRowCounts.stats_row_count = 0 THEN actualRowCounts.actual_row_count
    WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count THEN CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (actualRowCounts.actual_row_count - statsRowCounts.stats_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
    ELSE CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (statsRowCounts.stats_row_count - actualRowCounts.actual_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
END
from
(
    select distinct object_id from sys.stats where stats_id > 1
) objIdsWithStats
left join
(
    select object_id, sum(rows) as stats_row_count from sys.partitions group by object_id
) statsRowCounts
on objIdsWithStats.object_id = statsRowCounts.object_id 
left join
(
    SELECT sm.name [schema] ,
        tb.name logical_table_name ,
        tb.object_id object_id ,
        SUM(rg.row_count) actual_row_count
    FROM sys.schemas sm
         INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
         INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
         INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
         INNER JOIN sys.dm_pdw_nodes_db_partition_stats rg     ON rg.object_id = nt.object_id
            AND rg.pdw_node_id = nt.pdw_node_id
            AND rg.distribution_id = nt.distribution_id
    WHERE rg.index_id = 1
    GROUP BY sm.name, tb.name, tb.object_id
) actualRowCounts
on objIdsWithStats.object_id = actualRowCounts.object_id

查詢 2：檢查各資料表上次更新統計資料的時間，並找出統計資料的存留期。

注意

如果資料行的值散發有實質變更，無論統計資料上一次更新的時間為何，您都應該更新統計資料。

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

例如，專用 SQL 集區資料倉儲中的日期資料行，通常需要定期更新統計資料。專用 SQL 集區每次載入新的資料列，就會加入新的載入日期或交易日期。這些增加項目會造成資料散發變更，並使統計資料過時。

相反地，客戶資料表上性別資料行的統計資料可能永遠不需要更新。假設客戶間的散發固定不變，將新資料列加入至資料表變化並不會改變資料散發情況。

如果專用 SQL 集區只包含一種性別，而新的需求導致多種性別時，即需要更新性別資料行的統計資料。

如需詳細資訊，請參閱統計資料的一般指引。

實作統計資料管理

建議您擴充資料載入流程，確保結束載入後更新統計資料，並避免/減少同時查詢引發的封鎖或資源爭用。

當資料表變更其大小和/或其值散發時，資料載入最為頻繁。資源載入是實作部分管理程序的邏輯位置。

以下提供指導原則，以便更新您的統計資料：

請確定每個載入的資料表至少有一個統計資料物件已更新。這會在統計資料更新過程中更新資料表大小 (資料列計數和頁面計數) 資訊。
著重於參與 JOIN、GROUP BY、ORDER BY 和 DISTINCT 子句的資料行。
考慮較頻繁更新「遞增索引鍵」資料行 (例如交易日期)，因為這些值不會包含在統計資料長條圖中。
請考慮較不要頻繁更新靜態散發資料行。
請記住，每個統計資料物件會依序更新。僅只實作 UPDATE STATISTICS <TABLE_NAME> 不一定理想，尤其是對具有許多統計資料物件的寬型資料表而言。

如需詳細資訊，請參閱基數估計。

範例：建立統計資料

下列範例顯示如何使用各種選項來建立統計資料。您用於每個資料行的選項取決於您的資料特定以及在查詢中使用資料行的方式。

使用預設選項建立單一資料行統計資料

若要建立資料行的統計資料，請提供統計資料物件的名稱和資料行的名稱。

此語法會使用所有預設選項。根據預設，建立統計資料時，會取樣資料表的 20%。

CREATE STATISTICS [statistics_name] ON [schema_name].[table_name]([column_name]);

例如：

CREATE STATISTICS col1_stats ON dbo.table1 (col1);

檢查每個資料列以建立單一資料行統計資料

20% 的預設取樣率足以應付大部分的情況。不過，您可以調整取樣率。

若要取樣整個資料表，請使用此語法：

CREATE STATISTICS [statistics_name] ON [schema_name].[table_name]([column_name]) WITH FULLSCAN;

例如：

CREATE STATISTICS col1_stats ON dbo.table1 (col1) WITH FULLSCAN;

指定取樣大小以建立單一資料行統計資料

或者，您可以以百分比指定取樣大小：

CREATE STATISTICS col1_stats ON dbo.table1 (col1) WITH SAMPLE = 50 PERCENT;

只對某些資料列建立單一資料行統計資料

您也可以對資料表中部分的資料列建立統計資料。這稱為篩選的統計資料。

例如，當您計劃查詢大型分割資料表的特定分割時，可以使用篩選的統計資料。只對分割值建立統計資料，統計資料的精確度將會改善，並因而改善查詢效能。

這個範例會建立某個值範圍的統計資料。您可以輕鬆地定義這些值以符合分割中的值範圍。

CREATE STATISTICS stats_col1 ON table1(col1) WHERE col1 > '2000101' AND col1 < '20001231';

注意

若要讓查詢最佳化工具在選擇分散式查詢計劃時考慮使用篩選的統計資料，查詢必須符合統計資料物件的定義。使用上述範例，查詢的 WHERE 子句需要指定介於 2000101 和 20001231 之間的 col1 值。

使用所有選項建立單一資料行統計資料

您也可以將選項結合在一起。以下範例會使用自訂樣本大小建立篩選的統計資料物件：

CREATE STATISTICS stats_col1 ON table1 (col1) WHERE col1 > '2000101' AND col1 < '20001231' WITH SAMPLE = 50 PERCENT;

如需完整參考，請參閱 CREATE STATISTICS。

建立多重資料行統計資料

若要建立多重資料行統計資料物件，請利用上述範例，但要指定更多資料行。

注意

用來估計查詢結果中資料列數目的長條圖，只適用於統計資料物件定義中所列的第一個資料行。

在此範例中，長條圖位於 product_category。跨資料行統計資料會依據 product_category 與 product_sub_category 計算：

CREATE STATISTICS stats_2cols ON table1 (product_category, product_sub_category) WHERE product_category > '2000101' AND product_category < '20001231' WITH SAMPLE = 50 PERCENT;

因為 product_category 與 product_sub_category 相互關聯，所以多重資料行統計資料物件適用於同時存取這些資料行。

對資料表中的所有資料行建立統計資料

建立統計資料的其中一個方法是在建立資料表後發出 CREATE STATISTICS 命令：

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

使用預存程序在 SQL 集區建立所有資料行的統計資料

專用 SQL 集區沒有相當於 SQL Server 中 sp_create_stats 的系統預存程序。此預存程序會在仍無統計資料的 SQL 集區，建立各資料行的單一資料行統計資料物件。

下列範例會協助您開始 SQL 集區設計。請放心地依照您的需求進行調整。

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default 2 Fullscan 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

若要使用預設值為資料表中的所有資料行建立統計資料，請執行預存程序。

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

若要使用 fullscan 建立資料表中所有資料行的統計資料，請呼叫下列程序：

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

若要為資料表中的所有資料行建立取樣的統計資料，請輸入 3 和取樣百分比。此程序使用的取樣率為 20%。

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

範例：更新統計資料

若要更新統計資料，您可以：

更新一個統計資料物件。指定您要更新的統計資料物件名稱。
更新資料表上的所有統計資料物件。指定資料表名稱，而不是一個特定統計資料物件。

更新一個特定統計資料物件

使用下列語法來更新特定統計資料物件：

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

例如：

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

藉由更新特定統計資料物件，即可減少管理統計資料所需的時間和資源。這需要經過思考，才能選擇更新的最佳統計資料物件。

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

更新資料表上所有統計資料物件的簡單方法為：

UPDATE STATISTICS [schema_name].[table_name];

例如：

UPDATE STATISTICS dbo.table1;

UPDATE STATISTICS 陳述式易於使用。只要記住這會更新資料表上的所有統計資料，因此可能會執行超出所需的更多工作。如果效能不成問題，這是最簡單和完整保持最新統計資料的方式。

注意

更新資料表所有的統計資料後，專用 SQL 集區會掃描資料表，並針對統計資料物件取樣。如果資料表很大，而且有許多資料行以及許多統計資料，則根據需求來更新個別統計資料可能比較有效率。

如需 UPDATE STATISTICS 程序的實作，請參閱暫存資料表。實作方法與上述的 CREATE STATISTICS 程序有點不同，但結果相同。

如需完整語法，請參閱更新統計資料。

統計資料中繼資料

您可利用數個系統檢視和函式來尋找統計資料相關資訊。例如，使用 stats-date 函式來查看最後建立或更新統計資料的時間，即可查看統計資料物件是否可能過期。

統計資料的目錄檢視

這些系統檢視提供統計資料的相關資訊：

目錄檢視	描述
sys.columns	每個資料行有一個資料列。
sys.objects	資料庫中每個物件有一個資料列。
sys.schemas	資料庫中每個結構描述有一個資料列。
sys.stats	每個統計資料物件有一個資料列。
sys.stats_columns	統計資料物件中每個資料行有一個資料列。連結回到 sys.columns。
sys.tables	每個資料表 (包括外部資料表) 有一個資料列。
sys.table_types	每個資料類型有一個資料列。

統計資料的系統函式

這些系統函式很適合用於處理統計資料：

系統函式	描述
STATS_DATE	上次更新統計資料物件的日期。
DBCC SHOW_STATISTICS	有關統計資料物件所理解之值散發的摘要層級和詳細資訊。

將統計資料資料行和函式結合成一個檢視

此檢視會一起顯示與統計資料相關的資料行，以及 STATS_DATE() 函式的結果。

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON  co.[object_id]        = tb.[object_id]
JOIN    sys.schemas         AS sm ON  tb.[schema_id]        = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

DBCC SHOW_STATISTICS() 範例

DBCC SHOW_STATISTICS() 顯示統計資料物件中保存的資料。此資料來自三個部分：

頁首
密度向量
長條圖

有關統計資料的標頭中繼資料。此長條圖會顯示統計資料物件的第一個索引鍵資料行中的值散發。密度向量可測量跨資料行關聯性。

注意

專用 SQL 集區會使用統計資料物件中的任何資料來計算基數估計值。

顯示標頭、密度和長條圖

這個簡單範例顯示統計資料物件的所有三個部分：

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

例如：

DBCC SHOW_STATISTICS (dbo.table1, stats_col1);

顯示 DBCC SHOW_STATISTICS() 的一或多個部分

如果您只想要檢視特定部分，請使用 WITH 子句並指定您要查看哪些部分：

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>) WITH stat_header, histogram, density_vector

例如：

DBCC SHOW_STATISTICS (dbo.table1, stats_col1) WITH histogram, density_vector

DBCC SHOW_STATISTICS() 差異

DBCC SHOW_STATISTICS() 在專用 SQL 集區的執行比在 SQL Server 確切：

不支援未記載的功能。
無法使用 Stats_stream。
無法聯結特定統計資料子集的結果。例如，STAT_HEADER JOIN DENSITY_VECTOR。
無法針對訊息隱藏項目設定 NO_INFOMSGS。
無法使用統計資料名稱前後的方括弧。
無法使用資料行名稱來識別統計資料物件。
不支援自訂錯誤 2767。

下一步

如需進一步改善查詢效能，請參閱監視工作負載

共用方式為