いよいよ2012年第一四半期のリリースに向けてラストスパートに入ったみたいですね。
SQL Server 2012 RC0は以下のサイトからダウンロードできるようになっています。
Download:Microsoft SQL Server 2012 Release Candidate 0(RC0)
さて、今回はこのRC0を使用して、SQL Server 2012から追加されるカラムストアインデックス(列ストアインデックス)を試してみることにしました。
カラムストアインデックスはインデックスに含まれる列のデータを列単位でページに格納する様にすることで、効率的なデータの格納を実現しパフォーマンスを向上させる技術の様です。
カラムストアインデックスに関する詳細はMSDNの以下のサイトを参照して下さい。
MSDN:製品ドキュメント:SQL Server 2012 RC0:列ストアインデックス
パフォーマンス測定で、今回は次のようなテーブルを用意してみました。
----------------------------------------------------------------------
CREATE TABLE [dbo].[RetailTransaction](
[StoreCode] [nchar](4) NOT NULL,
[BusinessDate] [datetime] NOT NULL,
[TranCode] [nvarchar](4) NOT NULL,
[TranType] [tinyint] NOT NULL,
[GoodsCode] [nvarchar](10) NOT NULL,
[OperaterCode] [nvarchar](10) NULL,
[MemberCode] [nvarchar](10) NULL,
[ItemCount] [int] NOT NULL,
[Amount] [money] NOT NULL,
[TaxAmount] [money] NOT NULL,
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
[StoreCode] ASC,
[BusinessDate] ASC,
[TranCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
------------------------------------------------------------------------
小売店の1日の取引を格納するというシナリオのテーブルで、StoreCode(店舗番号)、BusinessDate(営業日)、TranCode(取引番号)の3つのフィールドが主キーとなっており、主キーにはクラスタードインデックスを張っています。
このテーブルに対して以下のデータ集計を行うSQLを実行します。
------------------------------------------------------------------------
SELECT SUM(ItemCount),SUM(Amount) FROM RetailTransaction
WHERE GoodsCode = '0000000001'
------------------------------------------------------------------------
RetailTransactionテーブルには1000万行のレコードを入れ、"WHERE GoodsCode='0000000001'"の条件には200万行のレコードが該当するようにしました。
この時に、次のインデックスのそれぞれを設定した時の実行速度を計測してみました。
(使用ハードウェア：CPU Intel Core2Duo 1.4GHz、搭載メモリ 1GB)
1. 通常のノンクラスータードインデックスを設定した場合
まずは条件句に使用されいているGoodsCodeにノンクラースタードインデックスを設定してみました。
--------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX IX_RetailTransaction_1
ON RetailTransaction(GoodsCode)
--------------------------------------------------------------------------
このインデックスを張った状態でのSQLの実行時間は5分15秒でした。
ただし、実行プランを確認すると、このノンクラスタードインデックスは使用されておらず
主キーに設定したクラスタードインデックスが使用されていました。
まず、これを基準にします。
2. 付加列インデックス(カバーリングインデックス)を設定した場合
次に1のノンクラスタードインデックスに抽出対象のフィールードであるItemCount、Amountを追加した付加列インデックスを設定しました。
--------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX IX_RetailTransaction_1
ON RetailTransaction(GoodsCode) INCLUDE(ItemCount,Amount)
--------------------------------------------------------------------------
このインデックスを張った状態でのSQLの実行時間は7秒でした。
さすがに速いですね。
3. カラムストアインデックスを設定した場合
次に条件句のGoodsCodeのみを含めたカラムストアインデックスを作成しました。
--------------------------------------------------------------------------
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_RetailTransaction_1
ON RetailTransaction(GoodsCode)
--------------------------------------------------------------------------
このインデックスを張った状態でのSQLの実行時間は5分30秒でした。
む、一番遅くなってしまった？ということで実行プランを見るとこのインデックスはほとんど使われておらず、主キーに張られたクラスタードインデックスを使用して検索が行われていました。
4. カラムストアインデックスをカバーリングインデックスとして設定した場合
これでは納得いかん！ということで、カラムストアインデックスがカバーリングインデックスになるように設定してみました。
--------------------------------------------------------------------------
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_RetailTransaction_1
ON RetailTransaction(GoodsCode,ItemCount,Amount)
--------------------------------------------------------------------------
このインデックスを張った状態でのSQLの実行時間は7秒でした。
実行時間、および実行プランの中身をみても2.付加列インデックスの場合とほとんど同じでした。
この結果だけみると、カラムストアインデックスにすることによるメリットはあまり感じられないというのが正直なところです。
2.付加列インデックスのケースと比べて、インデックスページが使用するディスクサイズは抑えられるかもしれませんが、更新ができなくなるだけ不便を感じます。
もちろん、テーブルのデータの分布、実行するSQL、ハードウェアのスペックによって結果は変わるかもしれませんので、もう少しテストを続けていきたいと思います。
データの更新はできないの？INSERT、DELETEはもちろん、カラムストアインデックスに含まれていない列へのUPDATE文でもメッセージ35350のエラーが発生しました。更新する時は必ず一度カラムストアインデックスをドロップする必要がありそうです。
今回、カラムストアインデックスを設定する時に、メッセージ8658のエラーに遭遇しました。
クエリー実行時に使用可能なメモリ量はサーバー構成オプションの"min memory per query (KB)"、"min server memory (MB)"で決まる最大使用量とリソース ガバナーの"request_max_memory_grant_percent"(既定:25%)の掛け合わせで決まる様ですが、それがカラムストアインデックスを作成するだけの容量が確保されていないとエラーとなる様です。
以下の方法で対処しました。
①サーバー構成オプションの"min memory per query (KB)"、"min server memory (MB)"の設定値を最大値まで拡大します。
--------------------------------------------------------------------------
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure "min memory per query (KB)",2147483647
GO
RECONFIGURE
sp_configure "min server memory (MB)",2147483647
GO
RECONFIGURE
--------------------------------------------------------------------------
②次にリソース ガバナーのdefaultの"request_max_memory_grant_percent"を25%から必要量を確保できる割合に引き上げます。(今回のケースでは30%)
--------------------------------------------------------------------------
USE [master]
GO
ALTER WORKLOAD GROUP [default] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=30,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [default]
GO
--------------------------------------------------------------------------
これでカラムストアインデックスの実行が正常に行える様になりました。