SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書 DWH 関連の新機能

SQL Server 2012 自習書シリーズ（HTML 版）
新機能編 No.3「DWH（データウェアハウス）関連の新機能」

松本美穂と松本崇博が執筆した SQL Server 2012 自習書シリーズの「新機能編 No.3 DWH 関連の新機能」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2014年12月26日]

目次へ | 前のページへ | 次のページへ

2.2　列ストアインデックスの作成方法

◆ 列ストアインデックスの作成方法

列ストアインデックスの作成方法は、非常に簡単です。Management Studio を利用する場合は、次のように［インデックス］フォルダーを右クリックして［新しいインデックス］の［非クラスター化 Columnstore インデックス］をクリックします。

00018

［新しいインデックス］ダイアログが表示されたら、次のように［追加］ボタンをクリックします。

00019

［"テーブル名" から列を選択］ダイアログが表示されるので、列ストアインデックスに含めたい列を選択して、［OK］ボタンをクリックします。

［新しいインデックス］ダイアログへ戻ったら、［OK］ボタンをクリックします。

00020

以上の操作だけで列ストアインデックスの作成が完了です。

CREATE INDEX ステートメントを利用して作成したい場合にも、次のように COLUMNSTORE キーワードを追加するだけで列ストアインデックスを作成することができます。

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

従来の非クラスター化インデックスを作成するときと同様、列名には、インデックスに含めたい列をカンマ区切りで指定するだけです。

なお、［新しいインデックス］ダイアログでは、次のように［スクリプト］ボタンをクリックすれば、GUI で操作したものをスクリプト化（CREATE INDEX を生成）することも可能です。

00021

◆ Let's Try

それでは、列ストアインデックスを試してみましょう。

1．まずは、列ストアインデックスをテストするためのデータベースを作成するために、次のように CREATE DATABASE ステートメントを実行します。

-- データベース「CSItestDB」の作成。C:\CSItest フォルダーへ作成
CREATE DATABASE CSItestDB
ON PRIMARY ( NAME = 'CSItestDB'
        , FILENAME = 'C:\CSItest\CSItestDB.mdf'
        , SIZE = 5120MB )
  LOG ON    ( NAME = 'CSItestDB_log'
        , FILENAME = 'C:\CSItest\CSItestDB.ldf'
        , SIZE = 300MB )
go

データベース名は「CSItestDB」として、データファイル（.mdf）は 5GB、ログファイル（.ldf）は 300MB で作成しています。データベースの作成先には「C:\CSItest」フォルダーを指定していますが、このフォルダーは任意のパスへ変更してください。

2．次に、データベース内にテーブル「t1」を作成して、1,000万件のデータを追加します

-- テーブル「t1」の作成
USE CSItestDB
CREATE TABLE t1
( a int IDENTITY PRIMARY KEY
, b int
, c varchar(3) DEFAULT DATEPART(ms, GETDATE())
, d char(200) DEFAULT 'dummy1'
, e char(200) DEFAULT 'dummy2'
)
-- 10,000,000（1000万件）のデータを追加
SET NOCOUNT ON
DECLARE @i int = 1, @b int = 1
WHILE @i <= 10000000
BEGIN
    IF @i % 10000 = 0 SET @b = @i
    INSERT INTO t1(b) VALUES(@b)
    SET @i += 1
END
SET NOCOUNT OFF

1,000万件のデータを追加しているので、環境にもよりますが、実行には 30分～2時間くらいの時間がかかります（ディスクが低速な場合には、さらに実行時間が長くなります）。

3．データの追加が完了したら、次のように SELECT ステートメントを実行して、追加されたデータを確認しておきましょう。

-- 上位 10万件を確認
SELECT TOP 100000 * FROM t1

00022

t1 テーブルには a列（IDENTITY による連番）、b列、c列（現在時刻のミリ秒の部分を抜き出したものを格納 07:55.333（7分55.333秒）なら 333 の部分を格納）を用意して、WHILE ループで 1,000万回 INSERT ステートメントを実行しています。b列には、10,000件ごとに、10,000、20,000、30,000 という値が入るようにしています。

4．次に、COUNT関数を利用して、データ件数が1,000万件であることを確認しておきましょう。

SELECT COUNT(*) FROM t1

00023

◆ 列ストアインデックスの作成

次に、列ストアインデックスを作成してみましょう。

1．ここでは、次のように a列、b列、c列を含めた列ストアインデックスを「cidx1」という名前で作成します。

CREATE NONCLUSTERED COLUMNSTORE INDEX cidx1
ON t1(a, b, c)

00024

「コマンドは正常に完了しました」と表示されれば、列ストアインデックスの作成が完了です。

2．次に、b 列に対して DISTINCT キーワードを付けて SELECT ステートメントを実行してみましょう。このとき、ツールバーの［実際の実行プランを含める］をクリックして、クエリ実行後に実行プラン（実行計画）を表示するようにします。

-- 実行プランの確認
SELECT DISTINCT b FROM t1

00025

b 列の重複値を除いた結果を取得できていることを確認できます。確認後、次のように［実行プラン］タブをクリックして、実行プランを確認します。

00026

一番右に「Columnstore インデックススキャン t1.cidx1」があることを確認できます。このアイコンは、列ストアインデックスがスキャンされたときに表示されるものです。

3．次に、クエリヒントとして「WITH(INDEX=0)」を付けて、全件スキャンをするように明示指定して（インデックスを利用しないようにして）、同じクエリを実行してみます。

-- 全件スキャンを明示指定
SELECT DISTINCT b FROM t1 WITH(INDEX=0)

00027

今度は、一番右に「Clustered Index Scan」と表示されて、クラスター化インデックスの全件スキャン（=テーブルの全スキャンと同等）が実行されていることを確認できます。

◆ IO 数や CPU 時間、実行時間の確認

次に、SET STATISTICS コマンドを利用して、クエリ実行時の IO 数や CPU 時間、実行時間などを比較してみましょう。

1．次のように SET STATISTICS コマンドで IO ON、TIME ON を実行して、前述のクエリを実行します（列ストアインデックスに関しては、クエリヒントで「WITH INDEX=cidx1」を付けて、確実に列ストアインデックスを利用するように指定して実行します）。

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT DISTINCT b FROM t1 WITH(INDEX=0) -- 全件スキャン
SELECT DISTINCT b FROM t1 WITH(INDEX=cidx1) -- 列ストアインデックス

00028

このクエリの実行時は、より正確な時間を計測するために、ツールバーの［実際の実行プランを含める］をクリックして、選択状態を外して、実行プランを表示しないようにしておいてください。

2．実行後、［メッセージ］タブを開くと、I/O 数や CPU 時間、実行時間などが表示されることを確認できます。

00029

全件スキャンでは、論理読み取り数（メモリ上のデータバッファキャッシュから読み取ったページ数）が 538,063ページ（約4.2GB）、列ストアインデックス利用時は、わずか 80ページ（約0.6MB）であることを確認できます。CPU 時間や実行時間（経過時間）に関しては、ハードウェア環境によって大きく異なりますが、桁違いの性能差が出ていることを確認できると思います（画面は、実行時間は 22倍、CPU 時間は 2000倍もの差が出ています。この結果のハードウェア環境は、Core i7-2600K、16GBメモリを搭載した PC 上の仮想マシンを利用していて、仮想マシン対しては 4コア、8GBメモリを割り当てています）。

Note：メモリが少ない場合は、さらに性能差が大きくなる
全件スキャンでは、約4.2GB の読み取りが発生するため、SQL Server に割り当て可能なメモリが 4.2GB 未満の場合には、ディスクからの読み取りが発生することになる（先行読み取りが発生してしまう）ので、列ストアインデックスとの性能差はさらに開くことになります。
たとえば、弊社環境で、仮想マシンへのメモリ割り当てを４GBへ減らした場合は、以下のような結果になります。
00030

全件スキャンでの実行時間が約 39秒かかっているのに対して、列ストアインデックスではわずか 149ミリ秒で完了していて、その差はなんと約265倍にもなっています。

Note：大量データになればなるほど、性能差が大きくなる
列ストアインデックスは、データ件数が大量になればなるほど、性能差が大きくなります（列ストアインデックスのほうが高速にデータを取得できます）。これは、列ストアインデックスの高度な圧縮によって、読み取り I/O 数の差が大きくなるためです。また、データベースを格納しているストレージが低速な場合にも、性能差が大きくなります（列ストアインデックスのほうが、より高速にデータを取得することができます）。

Note：列ストアインデックスは並列クエリに最適化されている
後述の Note で詳しく説明しますが、列ストアインデックスは、並列クエリ（パラレルクエリ）で最も効果を発揮するように設計／最適化されています。このため、CPU コアが 1つのマシンや仮想環境で上記のクエリを検証している場合は、本文中のような大きな性能効果を確認することができません。

なお、ベンチマーク結果の公開は、使用許諾契約書で禁じられていますが、本自習書では特別な許可を得て、実行結果を掲載しています。また、実行結果は、筆者のハードウェア環境に依存するものであり、すべての環境に当てはまるものではないことにこ注意ください。

◆ 非クラスター化インデックスの作成／比較

次に、列ストアインデックスではない通常の非クラスター化インデックスを作成して、列ストアインデックスと比較してみましょう。

1．次のように、非クラスター化インデックスを b列と c列に対して作成してみます。

-- 非クラスター化インデックスの作成
CREATE NONCLUSTERED INDEX idx_bc
ON t1(b, c)

00031

2．作成後、クエリヒントを指定せずに同じクエリを実行して、実行プランを確認します。

SELECT DISTINCT b FROM t1

00032

非クラスター化インデックスを作成した場合にも、クエリオプティマイザーによって列ストアインデックスが選択されていることを確認できます。

なお、データ件数が少ない場合（100万件などで試している場合）には、クエリオプティマイザーが非クラスター化インデックス（idx_bc）を利用したほうが効率的だと判断して、非クラスター化インデックスの Index Scan が実行される場合もあります。

3．次に、クエリヒントを利用して、全件スキャンや非クラスター化インデックスを明示指定して、I/O 数や CPU時間、実行時間などを比較してみましょう（このクエリの実行時は、より正確な時間を計測するために、実行プランを表示しないようにしておいてください）。

SELECT DISTINCT b FROM t1 WITH(INDEX=0)        -- 全件スキャン
SELECT DISTINCT b FROM t1 WITH(INDEX=idx_bc)    -- 非クラスター化 Index Scan
SELECT DISTINCT b FROM t1 WITH(INDEX=cidx1)    -- 列ストアインデックス

00033

論理読み取り数は、全件スキャンでは 538,063ページ（約4.2GB）、非クラスター化インデックスでは 26,274ページ（約205MB）、列ストアインデックスでは 80ページ（約0.6MB）であることを確認できます。CPU 時間や実行時間に関しては、ハードウェア環境によっても大きく異なりますが、非クラスター化インデックスを作成することで、全件スキャンよりも速く実行することができるようになっていますが、列ストアインデックスと比べると、桁違いの大きな性能差が出ていることを確認できます。

◆ データバッファキャッシュをクリアした場合

次に、データバッファキャッシュをクリアした場合を比較してみましょう。

1．データバッファキャッシュをクリアするには、次のように「DBCC DROPCLEANBUFFERS」を実行します。

DBCC DROPCLEANBUFFERS
SELECT DISTINCT b FROM t1 WITH(INDEX=0)        -- 全件スキャン
DBCC DROPCLEANBUFFERS
SELECT DISTINCT b FROM t1 WITH(INDEX=idx_bc)    -- 非クラスター化 Index Scan
DBCC DROPCLEANBUFFERS
SELECT DISTINCT b FROM t1 WITH(INDEX=cidx1)    -- 列ストアインデックス

00034

データバッファキャッシュをクリアしたことで、ディスクからの読み取り（先行読み取り）が発生するため、全件スキャンでの結果が著しく低速になっていることを確認できます（画面では、全件スキャンでの実行時間が 39秒かかっているのに対して、列ストアインデックスではわずか 158ミリ秒で完了していて、その差は 247倍にもなります）。

◆ GROUP BY の場合

次に、GROUP BY 演算を行った場合を比較してみましょう。

1．次のように GROUP BY 句に b 列を指定して、b 列でグループ化し、a 列の MAX 値を取得してみます、

SELECT b, MAX(a) FROM t1 WITH(INDEX=0)        -- 全件スキャン
GROUP BY b
SELECT b, MAX(a) FROM t1 WITH(INDEX=idx_bc)    -- 非クラスター化 Index Scan
GROUP BY b
SELECT b, MAX(a) FROM t1 WITH(INDEX=cidx1)    -- 列ストアインデックス
GROUP BY b

00035

00036

論理読み取り数は、全件スキャンでは 538,063ページ（約4.2GB）、非クラスター化インデックスでは 26,274ページ（約205MB）、列ストアインデックスでは 10,091ページ（約78.8MB）であることを確認できます。CPU 時間や実行時間に関しては、ハードウェア環境によって大きく異なりますが、列ストアインデックスでは桁違いの性能が出ていることを確認できます。このように、列ストアインデックスは、GROUP BY 演算や DISTINCT 処理などで大きな効果を発揮する、性能向上に大変役立つ機能です。

Note：列ストアインデックスは、並列クエリに最適化されている。MAXDOP 1 を避ける
列ストアインデックスは、複数コアを利用した並列クエリ（パラレルクエリ）での処理で最も効果を発揮できるように設計／最適化されています。このため、MAXDOP 1 のように並列度を 1 に設定（つまり並列クエリを利用しない設定）をした場合は、列ストアインデックスが効果的に働きません。
これは次のような状況です。
00037

上記のように OPTION(MAXDOP 1) を指定して、前述の GROUP BY 演算を実行した場合は、列ストアインデックスによる性能向上の恩恵が低くなっていることを確認できると思います。
したがって、列ストアインデックスを利用する場合は、MAXDOP 1 を利用せずに、並列クエリで処理することをお勧めします。

目次へ | 前のページへ | 次のページへ