Stream Analytics は Azure SQL Database を出力としてサポートしています。
Event Hub に連携されたデータを SQL Database に出力する場合などがこのケースに当てはまると思いますが、Strean Analytics を使用して SQL Database にデータを書き込む際には、書き込み時のストレージの使用領域について、考慮する内容がありますので、その情報について記載しておきたいと思います。
Stream Analytics でデータを書き込む際に実行されているステートメント
SQL Database の監査ログは Event Hub に書き込むことができます。
今回はこのデータを Stream Analytics で SQL Database に書き込むように設定しています。
SQL Database のテーブルとしては次のような定義となっています。
CREATE TABLE [dbo].[SqlAudit]( [PartitionId] [smallint] NULL, [EventEnqueuedUtcTime] [datetime2](3) NULL, [EventProcessedUtcTime] [datetime2](3) NULL, [record_id] [int] NULL, [record] [json] NULL ) ON [PRIMARY] WITH ( DATA_COMPRESSION = PAGE ) GO
このテーブルに Event Hub に書き込まれた SQL Database の監査ログを Stream Analytics でデータを書き込む際には次のようなステートメントでデータの投入が行われます。
insert bulk [dbo].[SqlAudit] ([PartitionId] SmallInt, [EventEnqueuedUtcTime] DateTime2(3), [EventProcessedUtcTime] DateTime2(3), [record_id] Int, [record] VarChar(max)) with (TABLOCK, CHECK_CONSTRAINTS, FIRE_TRIGGERS)
Stream Analytics からのデータ投入は バルク インサート によって実行されていることが確認できます。
高速挿入による問題
以前、当ブログで SQL Server の「高速挿入」についての覚書 という投稿を書きました。
Stream Analytics によるデータ投入はバルクインサートが使用されているため、この高速挿入の影響を受けます。
どのような影響を受ける可能性があるかというと、「未使用領域の増加」です。
未使用領域は「ページは割り当てられているが、データは書き込まれていない領域」となります。
どの程度未使用領域があるかは次のクエリで確認ができます。
SELECT
row_count,
used_page_count,
reserved_page_count
FROM
sys.dm_db_partition_stats
WHERE
object_Id = OBJECT_ID('dbo.SqlAudit')
今回のワークロードで実際に取得した情報が次の画像となります。
801 ページ確保 (予約) されていますが、実際の使用量は 219 ページとなっています。
(801 – 219) × 8KB = 4,656 KB となり、4.6 MB 程度が未使用の状態となっています。確保されているページの 3/4 程度が未使用の状態となっており、データ格納の効率が悪いですね。
これは以前書いた投稿でも触れていますが、トレースフラグ 692 (TF692) の説明の内容に起因しています。
ヒープまたはクラスター化インデックスへのデータの一括読み込みの間に高速挿入を無効にします。 SQL Server 2016 (13.x) 以降では、データベースが単純復旧モデルまたは一括ログ復旧モデルにある場合に最小限のログ記録を使用して、新しいページに挿入されたレコードの挿入パフォーマンスを最適化するため、高速挿入が既定で有効になります。 高速挿入では、挿入のパフォーマンスを最適化するため、各一括読み込みバッチは新しいエクステントを取得し、使用可能な空き領域を持つ既存エクステントの割り当ての参照をバイパスします。
高速挿入では、バッチ サイズが小さい一括読み込みによって、オブジェクトによって消費される未使用領域が増える可能性があるため、各バッチに大きなバッチサイズを使用してエクステントを完全に埋めるようにすることをお勧めします。 バッチ サイズを大きくできない場合は、このトレース フラグを使うと、パフォーマンスは低下しますが、確保される未使用領域を減らすのに役立ちます。適用対象: SQL Server 2016 (13.x) 以降のバージョン。
スコープ: グローバルまたはセッション。
SQL Databsae ではトレースフラグの設定ができないため、TF692 が有効化できません。そのためバルクインサートが実行される際には、高速挿入のロジックが使用されてデータの投入が行われます。
これにより、バルクインサートをする際には、既存のエクステントの未使用領域は再利用されず、バルクインサートを実行する際には必ず、新しいエクステント (64KB) の確保が行われます。
バルクインサートされるデータが 64KB の倍数に近いサイズであれば、この挙動は問題ないのですが、「毎回のバルクインサートでは 64KB 未満のデータしか投入されない」というような場合は、未使用の領域が多いケースがあります。
極端な例ですが「毎回 1KB しかバルクインサートされない」というようなケースでは「処理ごとに 63KB の未使用領域が増加」するということになります。
「大量のデータを投入」するようなワークロードであれば、高速挿入は処理性能と格納効率のバランスがとれている可能性が高いですが、「少量のデータを投入」するようなワークロードの場合は、処理性能と格納効率のバランスが悪く、格納効率が低いことのデメリットがストレージコストとして顕著に出てくる可能性があります。
未使用の領域は「インデックスの再構成 (REORGANIZE) または、再構築 (REBUILD)」で解消することができますが、これらの処理は大量のデータを操作するものとなりますので、実行時の性能へのオーバーヘッドについても考慮しておく必要があります。
実際に、REBUILD を実施した後の状態が以下となりますが、「reserved_page_count」が減少し、未使用領域が解放されていることが確認できます。
バルクインサートを実施する場合には、使用領域と未使用領域の比率が想定された状態となっているのかについては、意識しておくとよいのではないでしょうか。
少量のデータを投入するケースが多いようなワークロードでは、SQL Databsae の場合、バルクインサートではなく、INSERT で複数のVALUES 句を指定してまとめたデータ投入をし、大量のデータを投入する場合にバルクインサートを使用するというような考慮をすると、データ格納領域の使用効率を最適インできる可能性があるかと。