SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 CTP 2.2 の列ストアインデックス + メモリ最適化テーブルを使ってみる

leave a comment

SQL Server 2016 では列ストアインデックス (カラムストアインデックス : Columnstore Index) とメモリ最適化テーブル (In-Memory OLTP : Hekaton) を組み合わせて使用できるようになります。

シナリオとしては Columnstore Indexes for Operational Analytics となり、実用的なデータ分析をリアルタイムに実施するための仕組みになるかと思います。

SQL Server 2016 CTP Technical Deep Dive / Operational Analytics in SQL Server も参考になりますので、こちらも合わせて確認をするとよいかと。

このテーブルですが以下のようなクエリで作成することができます。

-- 列ストアインデックス + メモリ最適化テーブル
CREATE TABLE HK_CI(
	Col1 int IDENTITY,
	Col2 uniqueidentifier NOT NULL,
	CONSTRAINT PK_HK_CI PRIMARY KEY NONCLUSTERED (Col1),
	INDEX HASH_HK_CI HASH (Col2) WITH (BUCKET_COUNT = 10000000),
	INDEX CCIX_HK_CI CLUSTERED COLUMNSTORE
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

 

SQL Server 2014 では、実行することができないのですが、SQL Server 2016 であれば実行することができます。

列ストアインデックスと組み合わせる際には、SCHEMA_ONLY は使用できないようですね。

基本的な動作としては、「メモリ最適化テーブルを基本となるデータストアとして使用し、列ストアインデックスで使用していた行ストア部分としてメモリ最適化テーブルを使用する」というようなイメージになっているかと思います。

そのため、メモリ最適化テーブルと列ストアインデックスの 2 種類の形式でデータを保持しているイメージになるかと。

列ストアインデックスでは、大体 100 万件単位で列ストアとして圧縮していますが、それまでのデータについては行ストアとして格納されており、通常の行ストアベースの格納に近い形となっています。

そのため、行ストアへのデータ格納については、処理速度がそれほど高速ではない形となります。

列ストアインデックスとメモリ最適化テーブルを組み合わせた場合、列ストアインデックスの行ストア部分としては、メモリ最適化テーブルが使用されるイメージとなります。

# 列ストアに移行した後もメモリ最適化テーブルには情報は持っているようですが。

そのため行ストアの処理を高速に実行することができます。

試しに以下のような 3 種類のテーブルを用意してみます。


-- 列ストアインデックス
CREATE TABLE HK_CI(
	Col1 int IDENTITY,
	Col2 uniqueidentifier NOT NULL,
	INDEX CCIX_HK_CI CLUSTERED COLUMNSTORE
) 
GO

-- メモリ最適化テーブル
CREATE TABLE HK_CI(
	Col1 int IDENTITY,
	Col2 uniqueidentifier NOT NULL,
	CONSTRAINT PK_HK_CI PRIMARY KEY NONCLUSTERED (Col1)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO


-- 列ストアインデックス + メモリ最適化テーブル
CREATE TABLE HK_CI(
	Col1 int IDENTITY,
	Col2 uniqueidentifier NOT NULL,
	CONSTRAINT PK_HK_CI PRIMARY KEY NONCLUSTERED (Col1),
	INDEX CCIX_HK_CI CLUSTERED COLUMNSTORE
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

このテーブルに対して、以下のクエリでデータを入れた際の処理時間を計ってみます。

SET NOCOUNT ON
GO
DECLARE @i int = 1
BEGIN TRAN
WHILE (@i <= 1258291)
BEGIN
	INSERT INTO HK_CI(Col2) VALUES(NEWID())
	SET @i += 1
END
COMMIT TRAN
&#91;/sourcecode&#93;
</pre>
</div>
</p>

<table cellspacing="0" cellpadding="2" width="361" border="1"><tbody>
    <tr>
      <td valign="top" width="303">列ストアインデックス</td>

      <td valign="top" width="56">1:49</td>
    </tr>

    <tr>
      <td valign="top" width="303">メモリ最適化テーブル</td>

      <td valign="top" width="56">0:49</td>
    </tr>

    <tr>
      <td valign="top" width="303">列ストアインデックス + メモリ最適化テーブル</td>

      <td valign="top" width="56">1:03</td>
    </tr>
  </tbody></table>

<p>&#160;</p>

<p>メモリ最適化テーブル単体の場合と比較すると、列ストアインデックスを設定したことによるオーバーヘッドは発生しているようですが、単純な列ストアインデックスと比較すると処理時間が短くなっていますね。</p>

<p>&#160;</p>

<p>以下のようなクエリでメモリ最適化テーブルの情報を取得することができますが、列ストアインデックスとメモリ最適化テーブルを組み合わせた場合、メモリ最適化テーブルのインデックス情報として「index_id = 9999」の情報が追加されているようです。
  <br /></p>

<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:2771f7e4-23e9-454f-b25d-bdf9079ecd7f" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

DECLARE @object_name sysname = 'HK_CI'

select * from sys.dm_db_xtp_hash_index_stats WHERE object_id = OBJECT_ID(@object_name)
select * from sys.dm_db_xtp_nonclustered_index_stats WHERE object_id = OBJECT_ID(@object_name)
select * from sys.dm_db_xtp_index_stats WHERE object_id = OBJECT_ID(@object_name)
select * from sys.dm_db_xtp_object_stats WHERE object_id = OBJECT_ID(@object_name)
select * from sys.dm_db_xtp_memory_consumers WHERE object_id = OBJECT_ID(@object_name)

 

この構成のテーブルでは、「sp_memory_optimized_cs_migration」というストアドプロシージャを使用することができ、メモリ最適化テーブルのデータを列ストアインデックスとして圧縮して使用することができるようになります。

# CTP2 では、トレースフラグ 9975 を使用することで、この自動圧縮を無効にすることもできるようです。上記のストアドプロシージャは手動圧縮するためのものとなりますので、通常はバックグラウンドで自動圧縮されるはずです。

以下のようなクエリでデータの挿入を行ってみます。

SET NOCOUNT ON
GO
DECLARE @i int = 1
BEGIN TRAN
WHILE (@i <= 1258291)
BEGIN
	INSERT INTO HK_CI(Col2) VALUES(NEWID())
	SET @i += 1
END
COMMIT TRAN
&#91;/sourcecode&#93;
</pre>
</div>

<p>&#160;</p>

<p>この状態で列ストアインデックスの情報を以下のクエリで取得してみます。</p>

<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:84af0f20-88d8-4d91-8485-afc256021ba0" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

DECLARE @object_name sysname = 'HK_CI'

select * from sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID(@object_name)
select * from sys.column_store_row_groups WHERE object_id = OBJECT_ID(@object_name)
select * from sys.column_store_segments
select * from sys.column_store_dictionaries

 

image

データを挿入した直後は、OPEN 状態となっており、行ストア (列ストアとして圧縮されていない状態) となっているのが確認できます。

それでは、この状態で「sp_memory_optimized_cs_migration」を実行してみます。


DECLARE @object_name sysname = 'HK_CI'
DECLARE @object_id int = OBJECT_ID(@object_name)
EXEC sp_memory_optimized_cs_migration  @object_id

 

実行後の列ストアインデックスの情報が以下になります。

image

列ストアとして圧縮されていることが確認できますね。

この時のメモリ最適化テーブルのメモリの使用状況も確認をしておきたいと思います。


DECLARE @object_name sysname = 'HK_CI'
SELECT 
OBJECT_NAME(object_id) AS object_name,
index_id,
memory_consumer_desc,
SUM(allocated_bytes) / (1024 ^2) AS allocated_Mbytes ,
SUM(used_bytes) / (1024 ^2) AS used_Mbytes
FROM 
sys.dm_db_xtp_memory_consumers 
WHERE object_id = OBJECT_ID(@object_name)
GROUP BY object_id,index_id,memory_consumer_desc

 

上が sp_memory_optimized_cs_migration を事項していない状態、下が実行した状態となります。

image

image

index_id = 3 (メモリ最適化テーブルの非クラスター化インデックス) と index_id = 9999 のメモリ使用状況が変化していることが確認できますね。

動作を見ていると、この 9999 のインデックスがメモリ最適化テーブルから列ストアインデックスに送られる対象のデータを管理している部分となっており、ストアドプロシージャを実行して、列ストアインデックスにデータをマイグレーションしたことで、列ストアインデックスへの移行対象データ (index_id = 9999) が減ったのではと思います。

大量のデータを処理する場合、

  • 列ストアインデックスのセグメント単位で処理
  • メモリ最適化テーブルの行単位で処理

のどちらが処理に適しているかが変わってくるかと思います。

今回は検索はあまり検証できていないのですが、高速に処理を行いたい場合、

  • 列ストアインデックス + メモリ最適化テーブル
  • 列ストアインデックス + クラスター化 (非クラスター化) インデックス

のどちらが適しているかということを考慮して、テーブルの設計をする必要が出てくるのかもしれないですね。

Written by masayuki.ozawa

7月 26th, 2015 at 3:52 pm

Posted in SQL Server

Tagged with ,

Leave a Reply

*