SE の雑記

以前、SQL Server 2016 CTP 2.0 の列ストアインデックスの変更点 を投稿しましたが、今回の投稿では実際に使ってみたいと思います。
詳細については Columnstore Indexes Versioned Feature Summary を参照してください。

■Batch execution for single-threaded queries

SQL Server 2014 ではシングルスレッド動作の場合は行モードで動作していました。
以下のようなクエリを 2014 / 2016 で実行してみます。

SELECT O_CUSTKEY,COUNT(*) FROM ORDERS GROUP BY O_CUSTKEY OPTION (MAXDOP 1)

左が 2014 / 右が 2016 になるのですが、2016 ではバッチモードで実行されていることが確認できますね。
2014 の場合は並列クエリの場合にバッチ処理になっていたかと思いますがこの辺の制限が緩和されているようです。

 

■Snapshot isolation and read-committed snapshot isolation

以下のクエリでスナップショット系の設定を有効にした状態にします。

USE [master]
GO
ALTER DATABASE [DemoDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE [DemoDB] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

 
この状態で、以下のクエリを実行して、ロックを取得した状態とします。

BEGIN TRAN
UPDATE ORDERS SET O_ORDERSTATUS = 'A' WHERE O_ORDERKEY = 41368868

 
別のセッションから、

SELECT O_CUSTKEY,COUNT(*) FROM ORDERS GROUP BY O_CUSTKEY

を実行した場合の動作が 2014 と 2016 では異なってきます。
READ_COMMITTED_SNAPSHOT が有効になっていますので、行ストアのテーブルであれば、ロック競合が発生せずに読み取りができますが、列ストアインデックスの場合、これらの分離レベルがサポートされていなかったため有効な状態でもロック競合が発生し読み取りがブロックされます。
2016 の場合は分離レベルがサポートされていますので、読み取りがブロックされることなく処理を実行することができます。
 

■Specify columnstore index when creating a table.

SQL Server 2014 まではテーブルを作成した後に列ストアインデックスを作成していました。
実際の記述としては以下のようになります。

-- 2014 までの記述方法
CREATE TABLE CCITable(
Col1 int,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_CCITable ON CCITable

 
SQL Server 2016 ではテーブル作成時にインラインで指定が可能となりました。

-- インラインで列ストアインデックスを作成
CREATE TABLE CCITable(
Col1 int,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier,
INDEX CCIX_CCITable CLUSTERED COLUMNSTORE
)

詳細については CREATE TABLE (Transact-SQL) を参照していただくとよいかと。

■Updateable nonclustered columnstore index on heap or btree

SQL Server 2014 で、クラスター化列ストアインデックスが追加されましたが、非クラスター化列ストアインデックスについては更新を行うことができませんでした。
# パーティションのスイッチによるデータメンテナンスのシナリオが必要でした。

CREATE TABLE CCITable(
Col1 int,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX CCIX_CCITable ON CCITable (Col1)
INSERT INTO CCITable VALUES(1, NEWID(), NEWID(), NEWID() ,NEWID())

SQL Server 2014 では非クラスター化列ストアインデックスが設定されていても更新が行えるようになります。

CREATE TABLE CCITable(
Col1 int,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier,
INDEX NCCIX_CCITable NONCLUSTERED COLUMNSTORE (Col1)
)
GO
INSERT INTO CCITable VALUES(1, NEWID(), NEWID(), NEWID() ,NEWID())

■Btree index on a clustered columnstore index.

Btree インデックスのテーブルに対してクラスター化列ストアインデックスが作成できるようになりました。
2014 では以下のようなテーブルに対しては、クラスター化列ストアインデックスを作成することができませんでした。

CREATE TABLE CCITable(
Col1 int,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier,
INDEX NCIX_CCITable NONCLUSTERED(Col1)
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_CCITable ON CCITable

SQL Server 2016 では非クラスター化インデックスとクラスター化列ストアインデックスの組み合わせが使用できるようになります。

これにより、以下のようなテーブルを作成することができるようになります。

CREATE TABLE CCITable(
Col1 int PRIMARY KEY NONCLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_CCITable ON CCITable

 
SQL Server 2014 までのクラスター化列ストアインデックスの制限は、CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) に記載されています。
# 非クラスター化列ストアインデックスの制限は CREATE COLUMNSTORE INDEX (Transact-SQL)

一意の制約、主キー制約、または外部キー制約を含めることはできません。

を緩和させることができそうですね。
特定の行データの取得については、行グループのセグメントでアクセスする列ストアと比較して、行ストアの方が効率が良いという話もありますので、使い方はいろいろとありそうですね。
 

■Columnstore index on a memory-optimized table.

これについては In-Memory OLTP で調べようかと。
 

■Nonclustered columnstore index definition supports using a filtered condition.

非クラスター化列ストアインデックスをフィルターしたインデックスとして作成することができるようになりました。
CREATE COLUMNSTORE INDEX (Transact-SQL) に記載されていますが、WHERE 句がサポートされています。

DROP TABLE CCITable
CREATE TABLE CCITable(
Col1 int PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 uniqueidentifier,
Col5 uniqueidentifier,
)
GO
CREATE COLUMNSTORE INDEX NCCIX_CCITable ON CCITable (Col1,Col2,Col3,Col4,Col5)
WHERE Col1 >= 1 AND Col1 <= 5000
SET NOCOUNT ON
DECLARE @i int = 1
BEGIN TRAN
WHILE (@i <100000)
BEGIN
	INSERT INTO CCITable VALUES(@i, NEWID(), NEWID(), NEWID(), NEWID())
	SET @i += 1
END
COMMIT TRAN
SELECT COUNT(*) FROM CCITable SELECT COUNT(*) FROM CCITable WHERE Col1 BETWEEN 1 AND 2000

 
上記のようなクエリを実行した場合は以下のような実行プランとなります。
行ストアと列ストアが組み合わされた実行プランというものが今後は生成できるようになるようですね。

どのようなインデックスを設定すればよいかは Columnstore Indexes for Operational Analytics のシナリオを見て検討する必要がありそうですね。
 

■New batch mode execution support for queries using any of these operations

Columnstore Indexes Versioned Feature Summary に書かれている通りですが、バッチモードで処理できるものが増えています。
SQL Server 2012 では、OUTER JOIN がバッチ処理ではサポートされていませんでしたが、2014 になってサポートがされるようになったかと思います。
SQL Server 2016 では、SORT や集計関数でのバッチモードのサポートが増えているようですね。
# 互換性レベルは 130 にする必要があるようですが。
互換性レベル 130 には、Memory optimized table queries can have parallel plans. も入っており、互換性レベルは意識しておいた方がよさそうですね。
互換性レベルによる影響については ALTER DATABASE Compatibility Level (Transact-SQL) を確認するとよいかと思います。
120 から 130 への変更で

• The Insert in an Insert-select statement is multi-threaded or can have a parallel plan.
• Data Masking is enabled
• Memory Optimized Table queries can now have parallel plans
• Further cardinality estimation ( CE) Improvements with the Cardinality Estimation Model 130 which is visible from a Query plan. CardinalityEstimationModelVersion=”130″※ https://support.microsoft.com/ja-jp/kb/974006/ja も合わせて確認するとよいかと
• Batch mode v/s Row Mode changes with Columnstore indexes

がありますので、性能面で気を付けるポイントがいくつかありそうです。
 
列ストアインデックス、変更点が山盛りですね。