読者です 読者をやめる 読者になる 読者になる

行レベル セキュリティ を使ってるテーブルで SSDT の配置がエラーになるときの回避方法

SSDT のバージョンが低いと出ないかも。

行レベル セキュリティ(Row-Level Security) を設定しているテーブルを SSDT で配置をすると以下の警告が出る場合があります。

SR0111:Microsoft.Rules.Data.DeploymentValidation:現在の操作はテーブル ~ に対するデータ モーションを引き起こします。このテーブルでは、ポリシー ~ によって行レベルのセキュリティが有効になっているた.め、このテーブルに対してデータ モーションを実行することはできません。

SQL Server Data Tools 16.3 Release | SQL Server Data Tools Team Blog
の Tip に載ってました。

To prevent an accidental data loss, deployment that requires data motion on a table with Row Level Security is blocked by default. To override the default behavior, use  SqlPackage.exe with option /p:AllowUnsafeRowLevelSecurityDataMovement=true.

英語のメッセージやったら検索しやすいけど、日本語のエラーメッセージはつらい。。
詳細設定の「安全でない行レベルのセキュリティ データ移動を許可する」をチェック入れるとOKです。
f:id:odashinsuke:20170407223816p:plain

4/18(火) SQLWorld★大阪#41 開催します

SqlWorld :: SQLWorld★大阪#41 開催します。23回目の平日夜開催で、前回同様 ハンズオン 形式行う予定です。

【日時】
2017年4月28日(火曜日) 19:00~21:00
 

【イベント概要】
SQLWorld 23回目の平日夜開催~。今回も、みんなで SQL を書いてみようというハンズオン企画です!ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
 

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB(オフィス)
 

【参加費】
無料
 

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
 

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を~。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。

列ストアインデックスの Column Elimination / Rowgroup Elimination

日本語だと「列の削除」、「行グループの削除」です。

set statistics io on

で、IO の情報から内容を確認します。

列の削除

全ての列を使うクエリと一部の列を使うクエリで比較します。

set statistics io on
select 
max(c1), max(c2), count(c3), max(c4), max(c5), 
max(c6), min(c7), max(c8), max(c9), max(c10), max(c11), 
max(c12), max(c13), max(c14), max(c15), max(c16), max(c17), 
max(c18), max(c19), max(c20), max(c21), max(c22), max(c23), max(c24)
from cci_ordered

select 
max(c1), max(c2), count(c3), max(c4), max(c5)
from cci_ordered
set statistics io off
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)
Table 'cci_ordered'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 27656, lob physical reads 0, lob read-ahead reads 0.
Table 'cci_ordered'. Segment reads 3, segment skipped 0.

(1 row(s) affected)
Table 'cci_ordered'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 7757, lob physical reads 0, lob read-ahead reads 0.
Table 'cci_ordered'. Segment reads 3, segment skipped 0.

1度データをキャッシュに乗せてから実行したので、physical reads はありませんが、lob logical reads の数が、上と下では結構違います。

全ての列 一部の列
lob logical reads 27656 lob logical reads 7757

これが、Column Elimination(列の削除) です。

ちなみに Segment reads 3, segment skipped 0. って出てますが、これ行グループのことで、列セグメント(column segment) のことではありません。 sys.column_store_segments (Transact-SQL) | Microsoft Docs
の segment_id の説明を見てもらうとわかりますが、「行グループIDのことを指してる、互換性のため列名は segment_id のままにしてる」と書いてます。
要は行グループのことを昔は segment と呼んでたってことですかね。

行グループの除去

全く同じデータを順不同で CCI(Clustered Columnstore Index) を作成した cci テーブルと、
c1 列でソートしてCCIを作成した cci_ordered というテーブルで比較します。
※ちなみにソートした列ストアインデックスを作るにはちょっとしたコツが要ります。

まずはどの条件なら読む行グループが減るかを確認するため、sys.column_store_segments から情報を取ります。

select
  object_name(p.object_id) as table_name
  , c.name as column_name
  , css.segment_id, css.row_count, css.min_data_id, css.max_data_id
from
  sys.column_store_segments css
  left join sys.partitions p on p.hobt_id = css.hobt_id and p.partition_id = css.partition_id
  left join sys.indexes ind on p.index_id = ind.index_id and p.object_id = ind.object_id
  left join sys.index_columns indc on indc.object_id = ind.object_id and indc.index_id = ind.index_id and indc.index_column_id = css.column_id
  left join sys.columns c on c.column_id = indc.column_id and c.object_id = p.object_id
where object_name(p.object_id) in ('cci', 'cci_ordered') and c.name = 'c1'
order by
  p.object_id, css.column_id, css.segment_id

f:id:odashinsuke:20170331235239p:plain
min_data_id、max_data_id に注目し、cci_ordered は1個の行グループ、cci は3個の行グループになる条件を付けます。
今回は c1 <= 1028864 にします。
これで、cci テーブルは、全行グループが、cci_orderedテーブルは、segment_id = 0 の 1行グループが読み込まれるはずです。

set statistics io on
select 
max(c1), max(c2), count(c3), max(c4), max(c5)
from cci where c1 <= 1028864

select 
max(c1), max(c2), count(c3), max(c4), max(c5)
from cci_ordered where c1 <= 1028864
set statistics io off
(1 row(s) affected)
Table 'cci'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 7767, lob physical reads 0, lob read-ahead reads 0.
Table 'cci'. Segment reads 3, segment skipped 0.

(1 row(s) affected)
Table 'cci_ordered'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 3773, lob physical reads 0, lob read-ahead reads 0.
Table 'cci_ordered'. Segment reads 1, segment skipped 2.

lob logical reads と Segement reads に注目します。

順不同 (cci) ソート済 (cci_ordered)
lob logical reads 7767 lob logical reads 3773
Table ‘cci’. Segment reads 3, segment skipped 0. Table ‘cci_ordered’. Segment reads 1, segment skipped 2.

ソート済の cci_ordered は segment skiped 2 とでて、logical reads の数も減っています。
これが RowGroup Elimination(行グループの削除)です。

ソート済の列ストアインデックスを作成する

列ストア インデックスのクエリ パフォーマンス
に記載されていますが、

  1. 時系列でデータを追加する
  2. 行ストアクラスター化インデックスを作成し、データを並び替えたあとに列ストアインデックスに差し替える(MAXDOP=1 で実行)

2番目の方法が曲者で、MAXDOP=1 で動かさないとパラレルで行ストアが作成されるます。
そのため複数の行ストアにデータが分散してしまい、ソートが効かなくなってしまいます。

今回使った cci_ordered ではこんな感じで作成してます。

create clustered index cci_ordered_index 
  on cci_ordered(c1)
create clustered columnstore index cci_ordered_index 
  on cci_ordered with (drop_existing=ON,MAXDOP=1)

ただ MAXDOP=1 なので、時間がかかる!

列ストアを使う位デカいテーブルなのに MAXDOP=1は時間かかるよーって意見が Connect にも上がってます。
Multi-threaded rebuilds of Clustered Columnstore Indexes break the sequence of pre-sorted segment ordering (Order Clustering) | Microsoft Connect
2014 の時に登録されて、次のリリースで直すみたいな感じでしたが、2016 でも直ってませんね。vNext だと直ってるのかな?

SQL Server 2016 列ストアの更新について

OLTP 的な、1件更新について書きます。

sys.internal_partitions と sys.column_store_row_groups で見ると更新処理の動きが見えてきます。
以降このクエリで情報を見ていきます。

-- <tablename> は適宜読み替えてください。
-- internal_partition
select object_name(object_id), internal_object_type, internal_object_type_desc, row_group_id, rows 
from sys.internal_partitions
where object_name(object_id) = '<tablename>'
order by object_id, internal_object_type

-- column_store_row_gourps
select object_name(object_id), row_group_id, state, state_description, total_rows, deleted_rows, size_in_bytes
from sys.column_store_row_groups
where object_name(object_id) = '<tablename>'
order by object_id, row_group_id

クラスター化列ストアインデックス Clustered Columnstore Index (CCI) について

2014 から変わったという記述を見つけれなかったので、多分 2014 と同じ。
クラスター化列ストア インデックスの使用

ざっくりとした動きはこちら

  • INSERT
    • デルタストアに INSERT
  • DELETE
    • 削除ビットマップ を立てる
  • UPDATE
    • 列ストアにデータがあるときは、上の DELETE、INSERT の操作
    • デルタストアにデータがあるときは、UPDATE

実際に試してみましょう。
まず更新前の状態です。200万行が列ストアとして圧縮済です。
f:id:odashinsuke:20170331225723p:plain

INSERT

insert into cci (c1) values (-1)

f:id:odashinsuke:20170331225803p:plain
internal_partitions には、COLUMN_STORE_DELTA_STORE が追加されていますね。
column_store_row_gourps には、OPEN な新しい行グループが増えて、そこに1行追加されたのがわかります。

DELETE

delete from cci where c1 = 1000000

f:id:odashinsuke:20170331155318p:plain
COLUMN_STORE_DELETE_BITMAP の rows が 1になり、
データがいたと思われる行グループは、deleted_rows が 1 になりました。

UPDATE

update cci set c2 = 100 where c1 = 1000

f:id:odashinsuke:20170331225851p:plain
DELETE と INSERT の動きを合わせた結果になってますね。

最後に、デルタストアにある行を DELETE してみます。

delete from cci where c1 = -1

f:id:odashinsuke:20170331225904p:plain
これは、デルタストアの行が減ってることが確認出来ます。

クラスター化列ストアインデックス Nonclustered Columnstore Index (NCCI) について

2016 から更新可能に。
Real-Time Operational Analytics: DML operations and nonclustered columnstore index (NCCI) in SQL Server 2016 | SQL Server Database Engine Blog

  • INSERT
    • デルタストアに INSERT
  • DELETE
    • 削除バッファに INSERT
  • UPDATE
    • DELETE、INSERT の操作(デルタストアにあっても UPDATEしない。*1 )

NCCI の場合は、削除ビットマップ ではなく、削除バッファーに行くのが違いですね。
削除バッファーから削除ビットマップへの変換は、バックグランドで定期的に行われるか、インデックスのメンテナンスのタイミングになります。

こちらも実際に試してみましょう。
まず更新前の状態です。200万行が列ストアとして圧縮済です。
f:id:odashinsuke:20170331230129p:plain
CCI との違いは、COLUMN_STORE_DELETE_BUFFER があることですね。

INSERT

insert into ncci (c1) values (-1)

f:id:odashinsuke:20170331230146p:plain
CCI と同様の動きですね。

DELETE

delete from ncci where c1 = 1000000

f:id:odashinsuke:20170331230159p:plain
CCI とは異なり、COLUMN_STORE_DELETE_BUFFER の rows が 1になり、DELETE_BITMAP の方は変化なしです。
行グループも、deleted_rows が増えていません。

UPDATE

update ncci set c2 = 100 where c1 = 1000

f:id:odashinsuke:20170331230213p:plain
CCI と同様、DELETE と INSERT の動きを合わせた結果になってますね。
CCI と違うのは、DELETE が DELETE_BUFFER に入るとこです。

デルタストアにある行を DELETE してみます。

delete from ncci where c1 = -1

f:id:odashinsuke:20170331230239p:plain
CCI と同様の動きですね。

最後は インデックスのメンテナンスをしてみましょう。

alter index ncci_index ON ncci
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);  

f:id:odashinsuke:20170331231457p:plain
DELETE_BUFFER から DELETE_BITMAP に変換されてますね。
DELETE_BITMAP に変換されたので、deleted_rows の値が増えてます。
あとは OPEN だった行グループが圧縮されて、新しい行グループになり、圧縮前のデルタストア(行グループ)は廃棄(TOMBSTONE)状態になりました。
TOMBSTONE の行グループはしばらくすると消えてなくなります。

フィルター付き NCCI

NCCI は、フィルター条件を付けれるので、フィルターの条件にマッチする行の追加/更新/削除 は上と同じ動きをしますが、
フィルターの条件外の行の場合は、列ストアインデックスでは無いので列ストアとしての更新処理は行われません。

*1:解釈間違ってたらゴメン原文:There is no in place update even when the row is found in delta rowgroup.

メモ 列ストアインデックス デルタストアのページの中身

三木会でデモしたクエリのメモ

select
  OBJECT_NAME(d.object_id) as table_name
  , ip.internal_object_type_desc
  , d.allocated_page_page_id as page_id
  , d.page_type
  , d.page_type_desc
from
sys.dm_db_database_page_allocations(db_id(), object_id('cci'), 1, null, 'DETAILED') d 
inner join sys.internal_partitions ip on d.rowset_id = ip.hobt_id and d.object_id = ip.object_id
where ip.internal_object_type_desc = 'COLUMN_STORE_DELTA_STORE'

f:id:odashinsuke:20170320122859j:plain
これでとれた page_id を 次のクエリに渡す。

dbcc traceon(3604)
dbcc page('CCI_Sample', 1, 143616, 3) with tableresults
dbcc traceoff(3604)

select * from cci where c1 = 1000

f:id:odashinsuke:20170320123004j:plain
テーブルデータと並べてみる。

列ストアインデックスのメタ情報を取るクエリのメモ

幾つかメモ。

行グループ

select 
  object_name(object_id) as table_name
  , * 
from 
  sys.column_store_row_groups
where 
  OBJECT_NAME(object_id) = 'table_name'

sys.column_store_row_groups (Transact-SQL)

列セグメント

select
  object_name(p.object_id) as table_name
  , c.name as column_name
  , css.*
from
  sys.column_store_segments css
  left join sys.partitions p on p.hobt_id = css.hobt_id and p.partition_id = css.partition_id
  left join sys.indexes ind on p.index_id = ind.index_id and p.object_id = ind.object_id
  left join sys.index_columns indc on indc.object_id = ind.object_id and indc.index_id = ind.index_id and indc.index_column_id = css.column_id
  left join sys.columns c on c.column_id = indc.column_id and c.object_id = p.object_id
where
  object_name(p.object_id) = 'table_name'

sys.column_store_segments (Transact-SQL)

デルタストア、削除済ビットマップ、削除バッファーとか

select 
  object_name(object_id) as table_name 
  , * 
from sys.internal_partitions
where
  object_name(object_id) = 'table_name'

sys.internal_partitions (TRANSACT-SQL)

三木会で列ストアインデックスのお話しをしました

[2017/03/16(木): 三木会] SQL Server の 列ストアインデックス 入門 | Insight Technology, Inc. で、列ストアインデックス 入門のお話しをしてきました。
前半は列ストアインデックスとはどんなものかと、今までのテーブルと何が違うのかの概要で、後半は更新処理や検索処理の中の動きをデモを中心にお話ししました。

スライドはこちら

この勉強会で話すために、久しぶりに列ストアを調べましたが 2014 から 2016 で結構変わっててへーって感じでした。

調べた内容はちょくちょくブログに書いていこうと思います。