今年最初の関西DB勉強会です~。
今回は 初心者から上級者まで楽しめるよう勉強会をテーマにしています!
kansaidbstudy.connpass.com
そのため時間もいつもより長めで 12:00 ~ 19:00 となっています。
お時間あるかたは是非ご参加を~。
懇親会も是非ご参加ください~。
kansaidbstudy.connpass.com
今年最初の関西DB勉強会です~。
今回は 初心者から上級者まで楽しめるよう勉強会をテーマにしています!
kansaidbstudy.connpass.com
そのため時間もいつもより長めで 12:00 ~ 19:00 となっています。
お時間あるかたは是非ご参加を~。
懇親会も是非ご参加ください~。
kansaidbstudy.connpass.com
SQL Server の Date 型に ADO.NET の DbType.Date はうまく動かない - お だ のスペース の続き
SqlParameter.cs 見てみました。
corefx/SqlParameter.cs at v1.0.4 · dotnet/corefx · GitHub
Reference Source
set { MetaType metatype = _metaType; if ((null == metatype) || (metatype.DbType != value) || // Two special datetime cases for backward compat // DbType.Date and DbType.Time should always be treated as setting DbType.DateTime instead value == DbType.Date || value == DbType.Time) { PropertyTypeChanging(); _metaType = MetaType.GetMetaTypeFromDbType(value); } }
DbType.Date と DyType.Time の場合は、下位互換のために 毎回 MetaType を更新してますと。
で MetaType.GetMetaTypeFromDbType で何してるかというと、
corefx/SqlEnums.cs at v1.0.4 · dotnet/corefx · GitHub
Reference Source
internal static MetaType GetMetaTypeFromDbType(DbType target) { // if we can't map it, we need to throw switch (target) { case DbType.AnsiString: return MetaVarChar; case DbType.AnsiStringFixedLength: return MetaChar; case DbType.Binary: return MetaVarBinary; case DbType.Byte: return MetaTinyInt; case DbType.Boolean: return MetaBit; case DbType.Currency: return MetaMoney; case DbType.Date: case DbType.DateTime: return MetaDateTime; case DbType.Decimal: return MetaDecimal; case DbType.Double: return MetaFloat; case DbType.Guid: return MetaUniqueId; case DbType.Int16: return MetaSmallInt; case DbType.Int32: return MetaInt; case DbType.Int64: return MetaBigInt; case DbType.Object: return MetaVariant; case DbType.Single: return MetaReal; case DbType.String: return MetaNVarChar; case DbType.StringFixedLength: return MetaNChar; case DbType.Time: return MetaDateTime; case DbType.Xml: return MetaXml; case DbType.DateTime2: return MetaDateTime2; case DbType.DateTimeOffset: return MetaDateTimeOffset; case DbType.SByte: // unsupported case DbType.UInt16: case DbType.UInt32: case DbType.UInt64: case DbType.VarNumeric: default: throw ADP.DbTypeNotSupported(target, typeof(SqlDbType)); // no direct mapping, error out } }
DbDate と DbTime は MetaDateTime 扱いになってますと。
で、SqlDbType 指定した場合は、
corefx/SqlEnums.cs at v1.0.4 · dotnet/corefx · GitHub
Reference Source
internal static MetaType GetMetaTypeFromSqlDbType(SqlDbType target, bool isMultiValued) { // WebData 113289 switch(target) { case SqlDbType.BigInt: return MetaBigInt; case SqlDbType.Binary: return MetaBinary; case SqlDbType.Bit: return MetaBit; case SqlDbType.Char: return MetaChar; case SqlDbType.DateTime: return MetaDateTime; case SqlDbType.Decimal: return MetaDecimal; case SqlDbType.Float: return MetaFloat; case SqlDbType.Image: return MetaImage; case SqlDbType.Int: return MetaInt; case SqlDbType.Money: return MetaMoney; case SqlDbType.NChar: return MetaNChar; case SqlDbType.NText: return MetaNText; case SqlDbType.NVarChar: return MetaNVarChar; case SqlDbType.Real: return MetaReal; case SqlDbType.UniqueIdentifier: return MetaUniqueId; case SqlDbType.SmallDateTime: return MetaSmallDateTime; case SqlDbType.SmallInt: return MetaSmallInt; case SqlDbType.SmallMoney: return MetaSmallMoney; case SqlDbType.Text: return MetaText; case SqlDbType.Timestamp: return MetaTimestamp; case SqlDbType.TinyInt: return MetaTinyInt; case SqlDbType.VarBinary: return MetaVarBinary; case SqlDbType.VarChar: return MetaVarChar; case SqlDbType.Variant: return MetaVariant; case (SqlDbType)TdsEnums.SmallVarBinary: return MetaSmallVarBinary; case SqlDbType.Xml: return MetaXml; case SqlDbType.Udt: return MetaUdt; case SqlDbType.Structured: if (isMultiValued) { return MetaTable; } else { return MetaSUDT; } case SqlDbType.Date: return MetaDate; case SqlDbType.Time: return MetaTime; case SqlDbType.DateTime2: return MetaDateTime2; case SqlDbType.DateTimeOffset: return MetaDateTimeOffset; default: throw SQL.InvalidSqlDbType(target); } }
MetaDate と MetaTime になってますね。
下位互換のために、DbType.Date と DbType.Time が SqlDbType.Date と SqlDbType.Time と動作が違うのは良いんですが、
ならドキュメントに書いといてよー。
SQL Server データ型のマッピング
日付と時刻のデータ
これ見たら、SQL Server の Date 型には、DbType.Date でも使えるように見えるよ。。
Global Azure Bootcamp /w OzCode 2017@Kansai - connpass
で1枠頂いたので、Azure ネタで話します~。
なんと OzCode: Innovative debugging extension for Visual Studio の中の人とディスカッションも出来ます!
めったにない機会だと思いますので、ご都合の良いかたは是非ご参加を~。
懇親会はこちらです
【懇親会】Global Azure Bootcamp /w OzCode 2017@Kansai - connpass
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です。
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 を書いてみようという勉強会です。是非ご参加を~。
開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。
日本語だと「列の削除」、「行グループの削除」です。
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
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(行グループの削除)です。
列ストア インデックスのクエリ パフォーマンス
に記載されていますが、
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 だと直ってるのかな?
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
2014 から変わったという記述を見つけれなかったので、多分 2014 と同じ。
クラスター化列ストア インデックスの使用
ざっくりとした動きはこちら
実際に試してみましょう。
まず更新前の状態です。200万行が列ストアとして圧縮済です。
INSERT
insert into cci (c1) values (-1)
internal_partitions には、COLUMN_STORE_DELTA_STORE が追加されていますね。
column_store_row_gourps には、OPEN な新しい行グループが増えて、そこに1行追加されたのがわかります。
DELETE
delete from cci where c1 = 1000000
COLUMN_STORE_DELETE_BITMAP の rows が 1になり、
データがいたと思われる行グループは、deleted_rows が 1 になりました。
UPDATE
update cci set c2 = 100 where c1 = 1000
DELETE と INSERT の動きを合わせた結果になってますね。
最後に、デルタストアにある行を DELETE してみます。
delete from cci where c1 = -1
これは、デルタストアの行が減ってることが確認出来ます。
NCCI の場合は、削除ビットマップ ではなく、削除バッファーに行くのが違いですね。
削除バッファーから削除ビットマップへの変換は、バックグランドで定期的に行われるか、インデックスのメンテナンスのタイミングになります。
こちらも実際に試してみましょう。
まず更新前の状態です。200万行が列ストアとして圧縮済です。
CCI との違いは、COLUMN_STORE_DELETE_BUFFER があることですね。
INSERT
insert into ncci (c1) values (-1)
CCI と同様の動きですね。
DELETE
delete from ncci where c1 = 1000000
CCI とは異なり、COLUMN_STORE_DELETE_BUFFER の rows が 1になり、DELETE_BITMAP の方は変化なしです。
行グループも、deleted_rows が増えていません。
UPDATE
update ncci set c2 = 100 where c1 = 1000
CCI と同様、DELETE と INSERT の動きを合わせた結果になってますね。
CCI と違うのは、DELETE が DELETE_BUFFER に入るとこです。
デルタストアにある行を DELETE してみます。
delete from ncci where c1 = -1
CCI と同様の動きですね。
最後は インデックスのメンテナンスをしてみましょう。
alter index ncci_index ON ncci REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
DELETE_BUFFER から DELETE_BITMAP に変換されてますね。
DELETE_BITMAP に変換されたので、deleted_rows の値が増えてます。
あとは OPEN だった行グループが圧縮されて、新しい行グループになり、圧縮前のデルタストア(行グループ)は廃棄(TOMBSTONE)状態になりました。
TOMBSTONE の行グループはしばらくすると消えてなくなります。
NCCI は、フィルター条件を付けれるので、フィルターの条件にマッチする行の追加/更新/削除 は上と同じ動きをしますが、
フィルターの条件外の行の場合は、列ストアインデックスでは無いので列ストアとしての更新処理は行われません。
*1:解釈間違ってたらゴメン原文:There is no in place update even when the row is found in delta rowgroup.