数十倍の性能改善を実現させうる列ストアインデックス

進化したSQL Server 2012の新機能紹介（1）

列ストアインデックスの作成とクエリヒント

　列ストアインデックスは、SQL Server Managment Studio 2012の画面操作でも、T-SQLの発行でも作成できます。

　SQL Server Management Studio 2012で列ストアインデックスを作成するには、オブジェクトエクスプローラーを使用します。テーブルツリーの下にあるインデックスで右クリックをし、コンテキストメニューから［新しいインデックス］-［非クラスタ化Columnstoreインデックス］を選択します。

SQL Server Management Studio 2012での列ストアインデックスの作成

　T-SQLで作成する場合には、次のようなクエリを発行します。列ストアインデックスは、クラスタ化インデックス（※注2）にできないため、必ず非クラスタ化インデックスになるため、クエリも「NONCLUSTERED」になります。

※注2

　SQL Serverの従来のインデックスには、クラスタ化インデックスと非クラスタ化インデックスの2種類あります。クラスタ化インデックスは、実データを保持しますが、非クラスタ化インデックスはデータへのポインタを保持しています。

［リスト］列ストアインデックスを作成するT-SQL

CREATE NONCLUSTERED COLUMNSTORE INDEX インデックス名 ON テーブル名 (列名,列名...)

　列ストアインデックスを作成するのに必要な時間は、クラスタ化インデックスを作成するのに必要な時間のおよそ1.5倍です。100万行以下のテーブルの場合は、シングルスレッドで列ストアインデックスを作成します。100万行以上の場合は、列ストアインデックスを作成するのに使用できるメモリ容量とMaxDOPの設定に従って並列化して作成されます（※SQL Serverはクエリを並列処理したほうが効率が良いと判断した場合に並列処理をします。MaxDOPは最大並列処理の指定です。例えば、MaxDOPが5の場合、5つのスレッドで並列処理をします）。

　列ストアインデックスを作成すると、後は通常通りSELECT文を発行すると、クエリオプティマイザが必要に応じて列ストアインデックスを使用します。クエリヒントを使用することで、列ストアインデックスを使用した実行計画を作成するように指定できます。

［リスト］列ストアインデックスの使用を指定するクエリヒント

SELECT 列名 FROM テーブル名 WITH( INDEX (列ストアインデックス名) )

　逆に、列ストアインデックスを使用しないようにすることもできます。列ストアインデックスを使用したくない場合は、次のように「OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)」句を指定することで、列ストアインデックスを使用しない実行計画が作成されます。

［リスト］列ストアインデックスを使用しないように指定するクエリヒント

SELECT 列名 FROM テーブル名 OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

列ストアインデックスのあるテーブルの更新

　SQL Server 2012 RC0時点では、列ストアインデックスがあるテーブルは更新できません。列ストアインデックスのあるテーブルにInsert、Update、Deleteなどの更新処理をすると、「columnstoreインデックスを含むテーブルのデータを更新することはできません。INSERTステートメントを実行する前にいったんcolumnstoreインデックスを無効にし、INSERTの完了後に再構築することを検討してください。」というエラーが発生します。

列ストアインデックスのあるテーブルを更新しようとした時のエラー

　列ストアインデックスがあるテーブルを更新するには、エラーメッセージにて示されているように、列ストアインデックスを一度無効にしてから更新し、また列ストアインデックスを作成する必要があります。無効にした後、再度列ストアインデックスを作成する際には、最初に列ストアインデックスを作成する時と同じ時間がかかります。

　MSDNライブラリでは、無効にして更新する方法とは別に、パーティションの切り替えでの対応、UNIONALLを使用した対応が紹介されているので、検討したい場合はMSDNライブラリを参照してください。

まとめ

　列ストアインデックスは、大容量のテーブルに作成すると性能向上が期待できます。大容量で、更新頻度が少ないテーブルの場合は列ストアインデックスの作成を検討すると良いでしょう。列ストアインデックスを作成する場合には、テーブルのすべての列を追加します。列ストアインデックスを作成すると更新ができなくなるので、更新方法や更新タイミングについては別途検討が必要です。

　一般的に、25～100倍の性能向上が期待できるので、大容量データを扱う際には、ぜひ列ストアインデックスの使用を検討してください。

参考情報

• MSDNライブラリ列ストアインデックス
• SQL Server 2012自習書「SQL Server "Denali" DWH (データ ウェアハウス) 関連の新機能」Step 2」
• SQL Server Columnstore Index FAQ
• SQL Server Columnstore Performance Tuning
• Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0（PDF）
• Microsoft SQL Server 2012 Release Candidate (RC) のダウンロード