順序付き列ストア インデックスを使用したパフォーマンス チューニング
適用対象:
SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric SQL Database
効率的なセグメントの削除を有効にすると、順序付けされた列ストア インデックスは、クエリ述語と一致しない大量の順序付きデータをスキップすることで、パフォーマンスが向上します。 順序付けされた列ストア インデックスにデータを読み込み、インデックスの再構築を使用して順序付けを維持すると、データの並べ替え操作のために非順序付きインデックスよりも時間がかかる場合があります。ただし、順序付けされた列ストア インデックスのクエリは、後で高速に実行できます。
ユーザーが列ストア テーブルに対してクエリを実行すると、オプティマイザーは各セグメントに格納されている最小値と最大値をチェックします。 クエリ述語の範囲外にあるセグメントは、ディスクからメモリに読み取られません。 読み取るセグメントの数が少なく、その合計サイズが小さい場合、クエリはより速く終了できます。
順序付き列ストア インデックスの可用性については、「順序付き列ストア インデックスの可用性 」を参照してください。
列ストア インデックスの最近追加された機能の詳細については、「列ストア インデックスの新機能」を参照してください。
順序指定と非順序指定の列ストア インデックス
列ストア インデックスでは、各行グループの各列のデータが個別のセグメントに圧縮されます。 各セグメントには最小値と最大値を記述するメタデータが含まれているため、クエリ述語の範囲外にあるセグメントは、クエリの実行中にディスクから読み取られません。
列ストア インデックスが順序付けされていない場合、インデックス ビルダーはデータをセグメントに圧縮する前に並べ替えません。 つまり、値の範囲が重複するセグメントが発生し、クエリがディスクからより多くのセグメントを読み取り、完了するまでに時間がかかる可能性があります。
順序付け列ストア インデックスを作成すると、データベース エンジンは、インデックス ビルダーがセグメントに圧縮する前に、指定した順序キーで既存のデータを並べ替えます。 並べ替えられたデータでは、セグメントの重複が減少または排除されるため、ディスクから読み取るセグメントが少なくなるため、クエリのセグメントを効率的に削除し、パフォーマンスを向上させることができます。
使用可能なメモリ、データ サイズ、並列処理の度合い、インデックスの種類 (クラスター化と非クラスター化)、インデックスビルドの種類 (オフラインとオンライン) に応じて、順序付けられた列ストア インデックスの並べ替えは完全 (セグメントの重複なし) または部分的 (セグメントの重複) である可能性があります。 たとえば、部分的な並べ替えは、使用可能なメモリが完全な並べ替えに不十分な場合に発生します。 順序付き列ストア インデックスを使用するクエリは、多くの場合、部分的な並べ替えを使用して順序付けインデックスが作成された場合でも、非順序付きインデックスよりも高速に実行されます。
完全な並べ替えは、ONLINE = ON と MAXDOP = 1 の両方のオプションを使用して作成または再構築された順序付けされたクラスター化列ストア インデックスに対して提供されます。 この場合、並べ替えは tempdb データベースを使用してメモリに収まらないデータをスピルするため、使用可能なメモリによって制限されることはありません。 これにより、tempdb I/O が追加されるため、インデックスのビルド プロセスが遅くなる可能性があります。 ただし、オンライン インデックス再構築では、新しい順序付きインデックスの再構築中に、クエリで既存のインデックスを引き続き使用できます。
並べ替えるデータの量が使用可能なメモリに完全に収まるのに十分に小さい場合は、ONLINE = OFF と MAXDOP = 1 の両方のオプションを使用して作成または再構築された、順序付けされたクラスター化列ストア インデックスと非クラスター化列ストア インデックスに対しても、完全な並べ替えが提供される場合があります。
他のすべての場合、順序付きの列ストアインデックスのソートは部分的です。
注記
現在、順序付けられた列ストア インデックスは、Always-up-to-date 更新ポリシーを使用して、Azure SQL Database と Azure SQL Managed Instance でのみオンラインで作成または再構築できます。
列のセグメント範囲を確認し、セグメントの重複があるかどうかを判断するには、次のクエリを使用して、プレースホルダーをスキーマ、テーブル、列名に置き換えます。
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
o.name AS table_name,
cols.name AS column_name,
pnp.index_id,
cls.row_count,
pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id,
cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
AND
cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
AND
o.name = '<Table Name>'
AND
cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;
たとえば、完全に並べ替えられた列ストア インデックスに対するこのクエリからの出力は、次のようになります。 異なるセグメントの min_data_id 列と max_data_id 列には重複がないことに注意してください。
schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo Table1 Column1 1 479779 COLUMNSTORE 0 1 -17 1469515
dbo Table1 Column1 1 887658 COLUMNSTORE 1 1 1469516 2188146
dbo Table1 Column1 1 930144 COLUMNSTORE 2 1 2188147 11072928
注
順序付けられた列ストア インデックスでは、DML またはデータ読み込み操作の同じバッチから得られる新しいデータは、そのバッチ内でのみ並べ替えられます。 テーブル内の既存のデータを含むグローバル並べ替えはありません。
新しいデータを挿入した後、または既存のデータを更新した後にインデックス内のデータを並べ替えるには、インデックスを再構築します。
パーティション分割された列ストア インデックスをオフラインで再構築する場合、再構築は一度に 1 つのパーティションで実行されます。 再構築中のパーティション内のデータは、そのパーティションの再構築が完了するまで使用できません。
データは、オンラインリビルド中も引き続き使用できます。 詳細については、「オンラインでインデックス操作を実行する」を参照してください。
クエリ パフォーマンス
順序付けられた列ストア インデックスによるパフォーマンスの向上は、クエリ パターン、データのサイズ、データの並べ替えの程度、セグメントの物理的な構造、クエリ実行に使用できるコンピューティング リソースによって異なります。
通常、次のパターンのクエリは、順序付けられた列ストア インデックスを使用して高速に実行されます。
- 等値、不等値、または範囲述語を持つクエリ。
- 述語列と順序付けられた CCI 列が同じであるクエリ。
この例では、テーブル T1 には、 Col_C、 Col_B、および Col_Aのシーケンスで並べ替えられたクラスター化列ストア インデックスがあります。
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);
クエリ 1 とクエリ 2 のパフォーマンスは、すべての順序付き列を参照するため、クエリ 3 と 4 より多くの順序付き列ストア インデックスの利点を得ることができます。
-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';
-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';
-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';
-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';
データ読み込みのパフォーマンス
順序付き列ストア インデックスを持つテーブルへのデータ読み込みのパフォーマンスは、パーティション テーブルに似ています。 データの並べ替え操作により、データの読み込みには順序付けされていない列ストア インデックスよりも長い時間がかかる場合があります。ただし、後でクエリを実行する方が高速な場合があります。
セグメントの重複の抑制
重複するセグメントの数は、並べ替えるデータのサイズ、使用可能なメモリ、および順序付けられた列ストア インデックスのビルド中の並列処理の最大次数 (MAXDOP) の設定によって異なります。 次の方法では、セグメントの重複を減らしますが、インデックスの作成プロセスに時間がかかる場合があります。
- オンライン インデックス ビルドを使用できる場合は、順序付けされたクラスター化列ストア インデックスを作成するときに、
ONLINE = ONとMAXDOP = 1の両方のオプションを使用します。 これにより、完全に並べ替えられたインデックスが作成されます。 - オンライン インデックス ビルドを使用できない場合は、
MAXDOP = 1オプションを使用します。 - 読み込み前に、並べ替えキーでデータを事前に並べ替えます。
MAXDOP が 1 より大きい場合、順序付けられた列ストア インデックスのビルドに使用される各スレッドは、データのサブセットで動作し、ローカルで並べ替えられます。 異なるスレッドによって並べ替えられたデータ全体での並べ替えは行われません。 並列スレッドを使用すると、インデックスを作成する時間を短縮できますが、1 つのスレッドを使用する場合よりも重複するセグメントが生成されます。 シングル スレッド操作を使用すると、圧縮品質が最も高くなります。 CREATE INDEX コマンドを使用して MAXDOP を指定できます。
例
順序指定された列と序数を確認する
SELECT object_name(c.object_id) AS table_name,
c.name AS column_name,
i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
AND
c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;
順序付き列ストア インデックスを作成する
順序指定クラスター化列ストア インデックス:
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);
非クラスター化順序付き列ストア インデックス:
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);
注文列を追加または削除し、既存の順序付き列ストア インデックスを再構築する
クラスター化された順序付き列ストア インデックス
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
非クラスター化順序付き列ストア インデックス:
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
ヒープ テーブルで完全な並べ替えを使用して、順序指定クラスター化列ストア インデックスをオンラインで作成する
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);
順序付けされたクラスター化列ストア インデックスを完全な並べ替えでオンラインで再構築する
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);