アーキテクチャガイド

SQL Server Columnstore Indexes: 実践ガイド

columnstore は大規模な分析ワークロードを大きく改善できますが、rowstore 設計を万能に置き換えるものではありません。重要なのは columnstore が新しいかどうかではなく、テーブルとアクセスパターンがそのトレードオフに見合うかどうかです。

SQL Server設計をレビューする DBAハブを見る

なぜ columnstore はブラックボックスに感じられるのか

多くのチームはベンチマークや成功したデータウェアハウス案件で columnstore を知り、その成功を広く一般化しすぎます。columnstore は大きなデータセットの圧縮や、分析のために大量の行を高速に読む用途に非常に強い一方、singleton lookup、狭い OLTP アクセス、小規模テーブルにはあまり向きません。

重要な整理: clustered columnstore は一部の分析中心テーブルで clustered rowstore レイアウトを置き換えられますが、主キー制約の実装には nonclustered rowstore index が必要なことがあります。

Columnstore ???????

????????clustered ? nonclustered ? columnstore ??????????????????? DMV ????????

fact ????? clustered columnstore index ?????

sql

????scan ? aggregation ??????????????????? warehouse ???????

CREATE TABLE dbo.FactSales
(
    SalesKey bigint NOT NULL,
    OrderDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    ProductKey int NOT NULL,
    Quantity int NOT NULL,
    NetAmount decimal(18,2) NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

rowstore ????? nonclustered columnstore index ?????

sql

OLTP ????? rowstore ????????reporting ??????????????????????????

CREATE TABLE dbo.SalesOrderDetail
(
    SalesOrderDetailID bigint NOT NULL PRIMARY KEY,
    SalesOrderID bigint NOT NULL,
    ProductID int NOT NULL,
    OrderQty int NOT NULL,
    UnitPrice money NOT NULL,
    ModifiedDate datetime2 NOT NULL
);

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrderDetail_Analytics
ON dbo.SalesOrderDetail (SalesOrderID, ProductID, OrderQty, UnitPrice, ModifiedDate);

??????? rowgroup ????????

sql

columnstore ??????????????????????????rowgroup ???????????

SELECT
    OBJECT_NAME(object_id) AS table_name,
    row_group_id,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024.0 / 1024.0 AS size_mb
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.FactSales')
ORDER BY row_group_id;

各インデックスモデルが適する場所

Clustered columnstore

大きな fact 系テーブル、scan と aggregation が支配的な分析ワークロード、圧縮効果が重要な場面に向いています。行単位のトランザクションアクセスが主ではないことが前提です。

Nonclustered columnstore

主要なトランザクションアクセスには rowstore が必要だが、分析クエリには追加の圧縮投影が有効なテーブルで役立ちます。

従来の rowstore

選択性の高い predicate、singleton lookup、頻繁な update、予測可能なトランザクション遅延を必要とする多くの OLTP テーブルで依然として正しい標準です。

実践的な設計パターン

分析中心の fact テーブル

- scan と aggregation が支配的なら clustered columnstore を検討する
- ロード経路と partitioning をデータ移動と保持方針に合わせる
- staging だけでなく本番相当ロード後に rowgroup 品質と圧縮を測定する

運用とレポートが混在するテーブル

- トランザクション lookup が重要なら主アクセスは rowstore のままにする
- レポート高速化のために nonclustered columnstore を選択的に追加する
- 書き込みオーバーヘッドと保守影響を確認してから拡大する

ライフサイクルと保守計画

- partitioning、バッチサイズ、アーカイブを一体で見直す
- 制約や運用クエリを支えるインデックス戦略が維持されているか確認する
- columnstore を単独のチューニングスイッチではなくテーブル設計の一部として扱う

よくある columnstore の失敗

小さなテーブルに columnstore を使う

テーブルが小さすぎると、圧縮やscanの利点がほとんど出ません。複雑さだけが増え、効果は限定的です。

点アクセス経路を忘れる

ホットな point lookup や高選択性 seek を支えるテーブルは、レポートクエリがあっても rowstore-first 設計が必要なことが多いです。

ロードパターンを無視する

不適切なバッチサイズ、扱いづらい partition 移動、書き込み負荷の高いパターンは rowgroup 品質を落とし、期待した効果を消してしまいます。

columnstore を workload の判断として扱う

columnstore が強いのは、テーブルが分析資産として振る舞う時です。行数が多く、圧縮価値が高く、大規模 scan と集計中心のアクセスがある場合です。新しいから、あるいは1つの benchmark で勝ったからという理由だけで自動的に優れるわけではありません。

正しい設計はアクセスパターンから始まり、マーケティング上の分類から始まりません。大半のクエリがトランザクションかつ選択的なら、rowstore が依然として安全です。テーブルが fact table として振る舞うなら、columnstore を真剣に検討する価値があります。

FAQ

SQL Serverで clustered columnstore index を使うべきなのはいつですか？

一般的には、大規模な分析テーブルや warehouse 系テーブルで、scan と aggregation が支配的であり、トランザクション point lookup が主ワークロードでない場合です。

columnstore index は主キーの clustered index を置き換えられますか？

clustered columnstore は一部テーブルで clustered rowstore レイアウトを置き換えられますが、主キー制約のために nonclustered rowstore index が引き続き必要になることがあります。

OLTP では rowstore より columnstore が優れていますか？

一般論としては違います。多くの OLTP テーブルでは、選択的 lookup、頻繁な update、予測可能なトランザクションアクセスのため、従来の rowstore がより適切です。