専用 SQL プールでクラスター化列ストアインデックスの正常性を評価して修正する

[アーティクル]
11/20/2024

適用対象:Azure Synapse Analytics

この記事では、クラスター化列ストアインデックス (CCI) の正常性を評価する方法が若干異なります。次のセクションの手順に従うか、Azure Data Studio を使用してノートブックの手順を実行します。

Note

このノートブックを開く前に、Azure Data Studio がローカルコンピューターにインストールされていることを確認してください。インストールするには、「 Azure Data Studio をインストールする方法を学びます。

一般に、CCI の品質に影響を与える主な要因は 2 つあります。

コンパクトな行グループとメタデータ - 実際の行グループの数は、行グループ内の行数の理想的な数に近い値です。
圧縮された行グループ - 行グループは列ストア圧縮を使用しています。

小さいテーブル、過剰にパーティション分割されたテーブル、パーティション分割されていないテーブルなど、その他の条件は、間違いなく品質や正常性が低下しています。ただし、これらの条件は、 Step 4 で評価できる設計改善の機会としてより適切に分類されます。

手順 1: CCI の正常性の概要を分析する

次のクエリを使用して、1 行のメトリックを取得します。

WITH cci_detail AS (
    SELECT t.object_id,
          rg.partition_number,
          COUNT(*) AS total_rowgroup_count,
          SUM(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroup_count,
          CEILING((SUM(rg.[total_rows]) - SUM(rg.deleted_rows))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
          SUM(rg.size_in_bytes/1024/1024.) AS size_in_mb,
          SUM(CASE WHEN rg.state = 1 THEN rg.size_in_bytes END /1024/1024.) AS open_size_in_mb
   FROM sys.pdw_nodes_column_store_row_groups rg
   JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id
       AND rg.pdw_node_id = nt.pdw_node_id
       AND rg.distribution_id = nt.distribution_id
   JOIN sys.pdw_table_mappings mp ON nt.name = mp.physical_name
   JOIN sys.tables t ON mp.object_id = t.object_id
   GROUP BY t.object_id,
            rg.partition_number
)
SELECT COUNT(DISTINCT object_id) AS tables_assessed_count,
       COUNT(*) AS partitions_assessed_count,
       SUM(total_rowgroup_count) AS actual_rowgroup_count,
       SUM(ideal_rowgroup_count) AS ideal_rowgroup_count,
       SUM(open_rowgroup_count) AS uncompressed_rowgroup_count,
       CAST(SUM(size_in_mb) AS DECIMAL(19, 4)) AS actual_size_in_mb,
       CAST(SUM(open_size_in_mb) AS DECIMAL(19, 4)) AS uncompressed_size_in_mb,
       CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 100. AS DECIMAL(9, 4)) AS excess_pct,
       CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 1. AS DECIMAL(9, 4)) * SUM(size_in_mb) AS excess_size_in_mb
FROM cci_detail

その結果から、専用 SQL プールの CCI 正常性の概要を確認できます。この情報は直接操作可能ではありませんが、理想的な状態を実現するためのメンテナンスルーチンの重要性を理解するのに役立ちます。

列名	説明
`tables_assessed_count`	CCI テーブルの数
`partitions_assessed_count`	パーティションの数注: パーティション分割されていないテーブルは 1 としてカウントされます。
`actual_rowgroup_count`	行グループの物理数
`ideal_rowgroup_count`	行数に最適な行グループの計算された数
`uncompressed_rowgroup_count`	非圧縮データを含む行グループの数。 (開いている行とも呼ばれます)
`actual_size_in_mb`	CCI データの物理サイズ (MB)
`uncompressed_size_in_mb`	非圧縮データの物理サイズ (MB)
`excess_pct`	さらに最適化できる行グループの割合
`excess_size_in_mb`	最適化されていない行グループからの推定 MB

手順 2: CCI の詳細情報を分析する

次のクエリは、再構築の候補となるテーブルパーティションの詳細なレポートを提供します。 CCI の詳細は、メンテナンスの恩恵を最も受けるテーブルまたはパーティションを特定して優先順位を付けるのに役立つ 3 つのメトリックで提供されます。 WHERE 句でこれらのメトリックに適切なしきい値を設定し、ORDER BY 句で最も関心のあるメトリックを使用します。また、この詳細情報は、専用 SQL プールが多数の小さなフラグメント化されたテーブルの影響を受けているかどうかを判断するのにも役立ちます。これにより、コンパイルに遅延が生じる恐れがあります。

Note

コメント化された fnMs_GenerateIndexMaintenanceScript 関数は、インデックスを維持するための共通スクリプトを生成できるテーブル値関数 (TVF) です。結果でメンテナンススクリプトを取得する場合は、37 行目と 39 行目のコメントを解除します。クエリを実行する前に、 Generate index maintenance scripts セクションのスクリプトを使用して関数を作成します。結果から取得したメンテナンススクリプトを実行するときは、largerc や xlargerc などの適切なサイズの resource クラスを使用してください。

列名	品質の特性	説明
`excess_pct`	コンパクトさ	さらに圧縮できる行グループの割合
`excess_size_in_mb`	コンパクトさ	最適化されていない行グループからの推定 MB
`OPEN_rowgroup_size_in_mb`	圧縮	インデックス内の非圧縮データの実際の MB

WITH cci_info AS(
    SELECT t.object_id AS [object_id],
          MAX(schema_name(t.schema_id)) AS [schema_name],
          MAX(t.name) AS [table_name],
          rg.partition_number AS [partition_number],
          COUNT(DISTINCT rg.distribution_id) AS [distribution_count],
          SUM(rg.size_in_bytes/1024/1024) AS [size_in_mb],
          SUM(rg.[total_rows]) AS [row_count_total],
          COUNT(*) AS [total_rowgroup_count],
          CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 1 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [OPEN_rowgroup_size_in_mb],
          SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 2 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [CLOSED_size_in_mb],
          SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 3 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [COMPRESSED_size_in_mb],
          SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
   FROM sys.[pdw_nodes_column_store_row_groups] rg
   JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
       AND rg.[pdw_node_id] = nt.[pdw_node_id]
       AND rg.[distribution_id] = nt.[distribution_id]
   JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
   JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
   GROUP BY t.object_id,
            rg.partition_number
)
, calc_excess AS(
    SELECT *,
        CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 100. AS DECIMAL(9, 4)) AS [excess_pct],
        CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 1. AS DECIMAL(9, 4)) * size_in_mb AS [excess_size_in_mb]
   FROM cci_info
)
SELECT calc_excess.* 
    -- , script.*
FROM calc_excess
-- CROSS APPLY dbo.fnMs_GenerateIndexMaintenanceScript(object_id, partition_number) AS script
WHERE -- set your own threshold(s) for the following; 0 is the ideal, but usually not practical
  calc_excess.[excess_size_in_mb] > 300
  OR calc_excess.excess_pct > 0.1
  OR calc_excess.OPEN_rowgroup_size_in_mb > 100
ORDER BY calc_excess.[excess_size_in_mb] DESC;

手順 3: メンテナンスによって CCI の正常性が向上しない場合の対処

テーブル/パーティションに対してメンテナンスを実行すると、次のいずれかのシナリオが発生する可能性があります。

excess_pct または excess_size_in_mb がメンテナンス前よりも大きくなる。
メンテナンスステートメントがメモリ不足で失敗する。

一般的な原因

リソースが不足している。
サービスレベル (DWU) が不十分です。
テーブルが大きく、パーティション分割されていない。

推奨される軽減策

実行中のユーザーのリソースクラスまたはワークロードグループを変更して、メンテナンスステートメントのリソースを増やします。
メンテナンスを実行するために DWU レベルを一時的に上げます。
問題のあるテーブルのパーティション分割方法を実装し、パーティションに対してメンテナンスを実行します。

手順 4: 設計改善の機会を確認する

包括的なものではありませんが、次のクエリは、CCI に関するパフォーマンスやメンテナンスの問題を引き起こす場合がある機会を特定するのに役立ちます。

機会のタイトル	説明	Recommendations
小さいテーブル	テーブルに含まれる行数が 15M 未満	インデックスを CCI から次に変更することを検討してください。ステージングテーブルのヒープディメンションまたはその他の小さい参照用の標準クラスター化インデックス (行ストア)
パーティション分割の機会またはパーティション分割されていないテーブル	計算された理想的な行グループ数が 180M (または最大 188M 行) を超えています	パーティション分割戦略を実装するか、既存のパーティション分割戦略を変更して、パーティションあたりの行数を 188M 未満に減らします (ディストリビューションごとにパーティションごとに約 3 行グループ)
過剰にパーティション分割されたテーブル	テーブルに含まれる最も大きなパーティションの行数が 15M 未満	検討事項: インデックスを CCI から標準のクラスター化インデックス (行ストア) に変更しますパーティションごとに 60M 行に近づくようパーティショングレインを変更します

WITH cci_info AS (
    SELECT t.object_id AS [object_id],
          MAX(SCHEMA_NAME(t.schema_id)) AS [schema_name],
          MAX(t.name) AS [table_name],
          rg.partition_number AS [partition_number],
          SUM(rg.[total_rows]) AS [row_count_total],
          CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count]
   FROM sys.[pdw_nodes_column_store_row_groups] rg
   JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
       AND rg.[pdw_node_id] = nt.[pdw_node_id]
       AND rg.[distribution_id] = nt.[distribution_id]
   JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
   JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
   GROUP BY t.object_id,
            rg.partition_number
)
SELECT object_id,
       MAX(SCHEMA_NAME),
       MAX(TABLE_NAME),
       COUNT(*) AS number_of_partitions,
       MAX(row_count_total) AS max_partition_row_count,
       MAX(ideal_rowgroup_count) partition_ideal_row_count,
       CASE
           -- non-partitioned tables
           WHEN COUNT(*) = 1 AND MAX(row_count_total) < 15000000 THEN 'Small table'
           WHEN COUNT(*) = 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Partitioning opportunity'
           -- partitioned tables
           WHEN COUNT(*) > 1 AND MAX(row_count_total) < 15000000 THEN 'Over-partitioned table'
           WHEN COUNT(*) > 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Under-partitioned table'
       END AS warning_category
FROM cci_info
GROUP BY object_id

インデックスメンテナンススクリプトを生成する

次のクエリを実行して、専用 SQL プールに dbo.fnMs_GenerateIndexMaintenanceScript 関数を作成します。この関数は、CCI を 3 つの方法で最適化するスクリプトを生成します。この関数を使用すると、CCI だけでなく、クラスター化 (行ストア) インデックスも保持できます。

パラメーター

パラメーター名	Required	説明
`@object_id`	年	`object_id` ターゲットとなるテーブルの
`@partition_number`	年	`partition_number` を `sys.partitions` からターゲットに変換します。テーブルがパーティション分割されていない場合は、1 を指定します。

[出力テーブル]

列名	説明
`rebuild_script`	指定されたテーブル/パーティションに対して生成された `ALTER INDEX ALL ... REBUILD` ステートメント。パーティション分割されていないヒープは、 `NULL`を返します。
`reorganize_script`	指定されたテーブル/パーティションに対して生成された `ALTER INDEX ALL ... REORGANIZE` ステートメント。パーティション分割されていないヒープは、 `NULL`を返します。
`partition_switch_script`	パーティションテーブルにのみ適用されます。は、テーブルがパーティション分割されていない場合、または無効なパーティション番号が指定されている場合に `NULL` されます。 CCI が `ORDER` 句で作成された場合は、その CCI がレンダリングされます。

CREATE FUNCTION dbo.fnMs_GenerateIndexMaintenanceScript (@object_id INT, @partition_number INT = 1)
RETURNS TABLE
AS
RETURN(
    WITH base_info AS (
        SELECT
            t.object_id
            , SCHEMA_NAME(t.schema_id) AS [schema_name]
            , t.name AS table_name
            , i.index_type
            , i.index_cols
            , i.index_type_desc
            , tdp.distribution_policy_desc
            , c.name hash_distribution_column_name
        FROM sys.tables t
            JOIN (
                SELECT
                    i.object_id
                    , i.index_id
                    , MAX(i.type) AS index_type
                    , MAX(CASE WHEN i.type = 5 AND ic.column_store_order_ordinal != 0 THEN ' ORDER ' ELSE '' END)
                        + '(' + STRING_AGG(
                        CASE
                            WHEN i.type IN (1, 5) 
                                AND (ic.key_ordinal != 0 OR ic.column_store_order_ordinal != 0)
                                THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
                        END
                        , ',') WITHIN GROUP(ORDER BY ic.column_store_order_ordinal, ic.key_ordinal) + ')' AS index_cols
                    , MAX(i.type_desc)
                        + CASE
                            WHEN MAX(i.type) IN (1, 5) THEN ' INDEX'
                            ELSE ''
                        END COLLATE SQL_Latin1_General_CP1_CI_AS AS index_type_desc
                FROM sys.indexes i
                    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE i.index_id <= 1
                GROUP BY i.object_id, i.index_id
            ) AS i
                ON t.object_id = i.object_id
            JOIN sys.pdw_table_distribution_properties tdp ON t.object_id = tdp.object_id
            LEFT JOIN sys.pdw_column_distribution_properties cdp ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
            LEFT JOIN sys.columns c ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
        WHERE t.object_id = @object_id
    )
    , param_data_type AS (
        SELECT
            pp.function_id
            , typ.name AS data_type_name
            , CAST(CASE
                WHEN typ.collation_name IS NOT NULL THEN 1
                WHEN typ.name LIKE '%date%' THEN 1
                WHEN typ.name = 'uniqueidentifier' THEN 1
                ELSE 0
            END AS BIT) AS use_quotes_on_values_flag
        FROM sys.partition_parameters pp
            JOIN sys.types typ ON pp.user_type_id = typ.user_type_id
    )
    , boundary AS (
        SELECT
            t.object_id
            , c.name AS partition_column_name
            , pf.boundary_value_on_right
            , prv.boundary_id
            , prv.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 ELSE 0 END AS [partition_number]
            , CASE
                WHEN pdt.use_quotes_on_values_flag = 1 THEN '''' + CAST(
                    CASE pdt.data_type_name
                        WHEN 'date' THEN CONVERT(char(10), prv.value, 120)
                        WHEN 'smalldatetime' THEN CONVERT(VARCHAR, prv.value, 120)
                        WHEN 'datetime' THEN CONVERT(VARCHAR, prv.value, 121)
                        WHEN 'datetime2' THEN CONVERT(VARCHAR, prv.value, 121)
                        ELSE prv.value
                    END    
                    AS VARCHAR(32)) + ''''
                ELSE CAST(prv.value AS VARCHAR(32))
            END AS boundary_value
        FROM sys.tables t
            JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
            JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal = 1
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
            JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
            JOIN param_data_type pdt ON pf.function_id = pdt.function_id
            JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE t.object_id = @object_id
    )
    , partition_clause AS (
        SELECT
            object_id
            , COUNT(*) - 1 -- should always be the 2nd to last partition in stage table
                + CASE WHEN MAX([partition_number]) = @partition_number THEN 1 ELSE 0 END -- except when last partition
                AS [source_partition_number]
            , 'WHERE ' + MAX(partition_column_name)
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 
                    ' >= ' + MIN(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
                    ELSE 
                    ' <= ' + MAX(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
                END
                + ' AND ' + MAX(partition_column_name)
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 
                    ' < ' + MAX(boundary_value)
                    ELSE
                    ' > ' + MIN(boundary_value)
                END AS filter_clause
            , ', PARTITION (' + MAX(partition_column_name) + ' RANGE ' 
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 'RIGHT' ELSE 'LEFT' END 
                + ' FOR VALUES(' + STRING_AGG(boundary_value, ',') + '))' AS [partition_clause]
        FROM boundary
        WHERE [partition_number] BETWEEN @partition_number - 1 AND @partition_number + 1
        GROUP BY object_id
    )
    SELECT
        CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REBUILD' 
            + CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END + ';' END AS [rebuild_script]
        , CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REORGANIZE' 
            + CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END
            + CASE WHEN index_type = 5 THEN ' WITH (COMPRESS_ALL_ROW_GROUPS = ON)' ELSE '' END + ';' END AS [reorganize_script]
        , 'CREATE TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] WITH(' + index_type_desc + ISNULL(index_cols, '')
            + ', DISTRIBUTION = ' + distribution_policy_desc + CASE WHEN distribution_policy_desc = 'HASH' THEN '(' + hash_distribution_column_name + ')' ELSE '' END
            + partition_clause.partition_clause + ') AS SELECT * FROM [' + [schema_name] + '].[' + [table_name] + '] ' + filter_clause + CASE WHEN index_type = 5 AND index_cols IS NOT NULL THEN ' OPTION(MAXDOP 1)' ELSE '' END +  ';'
            + ' ALTER TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] SWITCH PARTITION ' + CAST(source_partition_number AS VARCHAR(16))
            + ' TO [' + [schema_name] + '].[' + [table_name] + '] PARTITION ' + CAST(@partition_number AS VARCHAR(16))
            + ' WITH (TRUNCATE_TARGET = ON);'
            + ' DROP TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp];' AS [partition_switch_script]
    FROM base_info
        LEFT JOIN partition_clause
            ON base_info.object_id = partition_clause.object_id
);
GO

詳細

より詳細な理解を得て、専用 SQL プール上の CCI 用の追加の評価ツールを取得するには、次を参照してください。

次の方法で共有

専用 SQL プールでクラスター化列ストアインデックスの正常性を評価して修正する

手順 1: CCI の正常性の概要を分析する

手順 2: CCI の詳細情報を分析する

手順 3: メンテナンスによって CCI の正常性が向上しない場合の対処

一般的な原因

推奨される軽減策

手順 4: 設計改善の機会を確認する

インデックスメンテナンススクリプトを生成する

詳細

フィードバック

その他のリソース

次の方法で共有

専用 SQL プールでクラスター化列ストア インデックスの正常性を評価して修正する

手順 1: CCI の正常性の概要を分析する

手順 2: CCI の詳細情報を分析する

手順 3: メンテナンスによって CCI の正常性が向上しない場合の対処

一般的な原因

推奨される軽減策

手順 4: 設計改善の機会を確認する

インデックス メンテナンス スクリプトを生成する

詳細

フィードバック

その他のリソース

専用 SQL プールでクラスター化列ストアインデックスの正常性を評価して修正する

インデックスメンテナンススクリプトを生成する