SE の雑記

-- 列ストアインデックス + メモリ最適化テーブル
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

-- 列ストアインデックス
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>
[sourcecode language='sql' ]
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)

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>
[sourcecode language='sql' ]
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

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

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