SQL Server のことなら SQL Quality 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
 int IDENTITY PRIMARY KEY
 int
 varchar(3DEFAULT DATEPART(msGETDATE())
 char(200DEFAULT 'dummy1'
 char(200DEFAULT 'dummy2'
 )
-- 10,000,0001000データ追加
SET NOCOUNT ON
DECLARE @i int = 1@b int = 1
WHILE @i <= 10000000
 BEGIN
    IF @i 10000 SET @b @i
    INSERT INTO t1(bVALUES(@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,00020,00030,000 という値が入るようにしています。

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

SELECT COUNT(*) FROM t1

00023

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

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

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

CREATE NONCLUSTERED COLUMNSTORE INDEX cidx1
 ON t1(a, bc)

00024

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

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

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

00025

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

00026

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

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

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

00027

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

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

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

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT DISTINCT FROM t1 WITH(INDEX=0)        -- 全件スキャン
SELECT DISTINCT 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 未満の場合には、ディスクからの読み取りが発生することになる(先行読み取りが発生してしまう)ので、列ストア インデックスとの性能差はさらに開くことになります。
たとえば、弊社環境で、仮想マシンへのメモリ割り当てを 4GBへ減らした場合は、以下のような結果になります。
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 FROM t1

00032

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

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

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

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

00033

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

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

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

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

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

00034

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

◆ GROUP BY の場合

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

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

SELECT bMAX(aFROM t1 WITH(INDEX=0)        -- 全件スキャン
 GROUP BY b
SELECT bMAX(aFROM t1 WITH(INDEX=idx_bc)    -- 非クラスター化 Index Scan
 GROUP BY b
SELECT bMAX(aFROM 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 のように並列度を に設定(つまり並列クエリを利用しない設定)をした場合は、列ストア インデックスが効果的に働きません。
これは次のような状況です。
00037
上記のように OPTION(MAXDOP 1) を指定して、前述の GROUP BY 演算を実行した場合は、列ストア インデックスによる性能向上の恩恵が低くなっていることを確認できると思います。
したがって、列ストア インデックスを利用する場合は、MAXDOP 1 を利用せずに、並列クエリで処理することをお勧めします。

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

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回:iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回:PASS Summit 2012 参加レポート
第31回:SQL Server 2012 Reporting Services 自習書のお知らせ
第30回:SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回:書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回:SQL Server 2012 の Power View 機能のご紹介
第25回:SQL Server 2012 の Data Quality Services
第24回:SQL Server 2012 自習書のご案内と初セミナー報告
第23回:Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)