適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)Microsoft Fabric SQL Database
列ストア インデックスは、パーティション分割と共に SQL Server データ ウェアハウスの構築に不可欠な機能です。 この記事では、SQL データベース エンジンを使用したデータ ウェアハウス設計の主なユース ケースと例について説明します。
データ ウェアハウスの主な機能
SQL Server 2016 (13.x) では、列ストアのパフォーマンスを強化するために次の機能が導入されました。
- Always On 可用性グループは、読み取り可能なセカンダリ レプリカでの列ストア インデックスのクエリをサポートします。
- 複数のアクティブな結果セット (MARS) は、列ストア インデックスをサポートしています。
- 新しい動的管理ビュー sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) は、パフォーマンスのトラブルシューティングに関する情報を行グループ レベルで提供します。
- 列ストア インデックスに対するすべてのクエリは、バッチ モードで実行できます。 以前は、並列クエリのみをバッチ モードで実行できました。
- 並べ替え、個別の並べ替え、および個別の演算子は、バッチ モードで実行されます。
- ウィンドウ集計が、データベース互換性レベル 130 以降のバッチ モードで実行されるようになりました。
- 集計を効率的に処理するための集計プッシュダウン。 これは、すべてのデータベース互換性レベルでサポートされています。
- 文字列述語の効率的な処理を実現するための文字列述語プッシュダウン。 これは、すべてのデータベース互換性レベルでサポートされています。
- データベース互換性レベル 130 以上でのスナップショット分離。
- 順序付けされたクラスター化列ストア インデックスは、SQL Server 2022 (16.x) で導入されました。 詳細については、「CREATE COLUMNSTORE INDEX」と「順序付けられた列ストア インデックスを使用したパフォーマンスチューニング」を参照してください。 順序付け列ストア インデックスの可用性については、「 順序付き列インデックスの可用性」を参照してください。
SQL Server と Azure SQL のバージョンとプラットフォームの新機能の詳細については、「列ストア インデックスの新機能」を参照してください。
非クラスター化インデックスと列ストア インデックスを組み合わせてパフォーマンスを改善する
SQL Server 2016 (13.x) 以降では、クラスター化列ストア インデックスに行ストア非クラスター化インデックスを作成できます。
例: 非クラスター化インデックスを使用してテーブルの検索効率を改善する
データ ウェアハウスでのテーブルの検索効率を改善するために、テーブルの検索でクエリが最高のパフォーマンスを発揮するように設計された非クラスター化インデックスを作成できます。 たとえば、一致する値を見つけるクエリや、値の小さな範囲を返すクエリは、列ストア インデックスではなく B ツリー インデックスに対して実行したほうが高いパフォーマンスを発揮します。 列ストア インデックスを完全にスキャンする必要がなく、B ツリー インデックスを使用してバイナリ検索を実行することで、正しい結果が高速に返されます。
--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.
--Create the table
CREATE TABLE t_account (
AccountKey int NOT NULL,
AccountDescription nvarchar (50),
AccountType nvarchar(50),
UnitSold int
);
--Store the table as a columnstore.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;
--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);
例: 非クラスター化インデックスを使用して列ストア テーブルに主キー制約を適用する
テーブルには最大 1 つのクラスター化インデックスを含めることができるため、クラスター化列ストア インデックスを持つテーブルにクラスター化主キー制約を設定することはできません。 列ストア テーブルに主キー制約を作成するには、非クラスター化として宣言する必要があります。
次の例では、非クラスター化主キー制約を持つテーブルを作成し、そのテーブルにクラスター化列ストア インデックスを作成します。 列ストア テーブルの挿入または更新によって非クラスター化インデックスも変更されるため、主キー制約に違反するすべての操作で操作全体が失敗します。
--Create a primary key constraint on a columnstore table.
--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
AccountKey int NOT NULL,
AccountDescription nvarchar (50),
AccountType nvarchar(50),
UnitSold int,
CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);
--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;
行レベルと行グループ レベルのロックを有効にしてパフォーマンスを向上させる
列ストア インデックス機能の非クラスター化インデックスを補完するために、SQL Server 2016 (13.x) では、 SELECT、 UPDATE、および DELETE 操作用の詳細なロック機能が提供されます。 クエリの実行では、非クラスター化インデックスに対するインデックス検索に行レベルのロックを使用できます。また、列ストア インデックスに対するテーブル全体のスキャンに行グループ レベルのロックを使用できます。 行レベルのロックと行グループ レベルのロックを適切に使用すると、読み取り/書き込みのコンカレンシー度を高めることができます。
--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account
--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);
--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN
-- The query plan chooses a seek operation on the nonclustered index
-- and takes the row lock
SELECT *
FROM t_account
WHERE AccountKey = 100;
COMMIT TRAN;
スナップショット分離と READ COMMITTED スナップショット分離
スナップショット分離 (SI) を使用してトランザクションの一貫性を保証し、読み取りコミットされたスナップショット分離 (RCSI) を使用して列ストア インデックスに対するクエリのステートメント レベルの一貫性を保証します。 これにより、データ ライターをブロックすることなくクエリを実行できるようになります。 また、ブロック不可の動作は、複雑なトランザクションのデッドロックの可能性を大幅に軽減します。 詳細については、「 データベース エンジンの行のバージョン管理ベースの分離レベル」を参照してください。