Azure SQL Database への Azure Stream Analytics の出力Azure Stream Analytics output to Azure SQL Database

この記事では、Azure Stream Analytics を使用して Azure SQL Database にデータを読み込むときに、よりよい書き込みスループット パフォーマンスを実現するためのヒントについて説明します。This article discusses tips to achieve better write throughput performance when you're loading data into Azure SQL Database using Azure Stream Analytics.

Azure Stream Analytics の SQL 出力では、オプションとして並列書き込みがサポートされます。SQL output in Azure Stream Analytics supports writing in parallel as an option. このオプションでは完全に並列なジョブ トポロジが可能で、複数の出力パーティションが書き込み先テーブルに並列で書き込まれます。This option allows for fully parallel job topologies, where multiple output partitions are writing to the destination table in parallel. ただし、Azure Stream Analytics でこのオプションを有効にするだけでは、より高いスループットを実現するのに十分ではありません。スループットは、データベースの構成とテーブル スキーマに大きく依存しているためです。Enabling this option in Azure Stream Analytics however may not be sufficient to achieve higher throughputs, as it depends significantly on your database configuration and table schema. インデックス、クラスタリング キー、インデックスの FILL FACTOR、および圧縮の選択が、テーブルを読み込む時間に影響します。The choice of indexes, clustering key, index fill factor, and compression have an impact on the time to load tables. 内部ベンチマークに基づいてデータベースを最適化してクエリと読み込みのパフォーマンスを向上させる方法について詳しくは、SQL Database のパフォーマンス ガイダンスに関するページをご覧ください。For more information about how to optimize your database to improve query and load performance based on internal benchmarks, see SQL Database performance guidance. SQL Database に並列で書き込む場合、書き込みの順序は保証されません。Ordering of writes is not guaranteed when writing in parallel to SQL Database.

ここでは、ソリューションの全体的なスループットの向上に役立つ各サービス内のいくつかの構成を示します。Here are some configurations within each service that can help improve overall throughput of your solution.

Azure Stream AnalyticsAzure Stream Analytics

  • パーティション分割の継承 – この SQL 出力構成オプションを使用すると、前のクエリ ステップや入力のパーティション構成を継承できます。Inherit Partitioning – This SQL output configuration option enables inheriting the partitioning scheme of your previous query step or input. これを有効にして、ディスク ベースのテーブルに書き込み、ジョブを完全並列トポロジにすると、スループットの向上を期待できます。With this enabled, writing to a disk-based table and having a fully parallel topology for your job, expect to see better throughputs. 他の多くの出力に対しては、このパーティション分割は既に自動的に行われています。This partitioning already automatically happens for many other outputs. このオプションで行われる一括挿入に対しては、テーブル ロック (TABLOCK) も無効になります。Table locking (TABLOCK) is also disabled for bulk inserts made with this option.


入力パーティションが 8 個より多い場合、入力パーティション構成の継承は適切な選択ではない可能性があります。When there are more than 8 input partitions, inheriting the input partitioning scheme might not be an appropriate choice. この上限は、ID 列とクラスター化インデックスが 1 つだけのテーブルにおいて観察されたものです。This upper limit was observed on a table with a single identity column and a clustered index. この場合、出力ライターの数を明示的に指定するために、クエリで INTO 8 を使用することを検討してください。In this case, consider using INTO 8 in your query, to explicitly specify the number of output writers. スキーマとインデックスの選択によっては、結果が異なる場合があります。Based on your schema and choice of indexes, your observations may vary.

  • バッチ サイズ - SQL の出力構成では、書き込み先テーブル/ワークロードの特性に基づいて、Azure Stream Analytics SQL 出力の最大バッチ サイズを指定することができます。Batch Size - SQL output configuration allows you to specify the maximum batch size in an Azure Stream Analytics SQL output based on the nature of your destination table/workload. バッチ サイズは、すべての一括挿入トランザクションで送信されるレコードの最大数です。Batch size is the maximum number of records that sent with every bulk insert transaction. クラスター化された列ストア インデックスでは、バッチ サイズを約 100 K にすると、並列化を高くし、ログ記録を最小にし、ロックを最適にできます。In clustered columnstore indexes, batch sizes around 100K allow for more parallelization, minimal logging, and locking optimizations. ディスク ベースのテーブルでは、10 K (既定値) 以下にするとソリューションに最適な場合があります。バッチ サイズを大きくすると、一括挿入中にロックのエスカレーションがトリガーされる可能性があります。In disk-based tables, 10K (default) or lower may be optimal for your solution, as higher batch sizes may trigger lock escalation during bulk inserts.

  • 入力メッセージのチューニング – パーティション分割の継承とバッチ サイズの使用を最適化している場合、1 つのパーティションのメッセージあたりの入力イベントの数を増やすと、書き込みスループットをさらに上げるのに役立ちます。Input Message Tuning – If you've optimized using inherit partitioning and batch size, increasing the number of input events per message per partition helps further pushing up your write throughput. 入力メッセージのチューニングにより、Azure Stream Analytics 内のバッチ サイズを指定したバッチ サイズまで上げることができ、それによってスループットが向上します。Input message tuning allows batch sizes within Azure Stream Analytics to be up to the specified Batch Size, thereby improving throughput. これは、圧縮を使用するか、または EventHub または BLOB での入力メッセージ サイズを増やすことによって実現できます。This can be achieved by using compression or increasing input message sizes in EventHub or Blob.

SQL AzureSQL Azure

  • パーティション テーブルとパーティション インデックス – パーティション キー (たとえば PartitionId) と同じ列を含むテーブルでパーティション分割された SQL テーブルとパーティション分割されたインデックスを使用すると、書き込み中のパーティション間の競合を大幅に減らすことができます。Partitioned Table and Indexes – Using a partitioned SQL table and partitioned indexes on the table with the same column as your partition key (for example, PartitionId) can significantly reduce contentions among partitions during writes. パーティション テーブルの場合は、プライマリ ファイル グループにパーティション関数パーティション構成を作成する必要があります。For a partitioned table, you'll need to create a partition function and a partition scheme on the PRIMARY filegroup. これにより、新しいデータの読み込み中の既存データの可用性も向上します。This will also increase availability of existing data while new data is being loaded. パーティションの数によってはログ IO の上限に達する可能性があり、これは SKU をアップグレードすることで増やすことができます。Log IO limit may be hit based on number of partitions, which can be increased by upgrading the SKU.

  • 一意キー違反の回避 – Azure Stream Analytics のアクティビティ ログで複数キー違反の警告メッセージが発生する場合は、復旧の間に発生する可能性がある一意制約違反によってジョブが影響を受けていないことを確認します。Avoid unique key violations – If you get multiple key violation warning messages in the Azure Stream Analytics Activity Log, ensure your job isn't impacted by unique constraint violations which are likely to happen during recovery cases. インデックスに対して IGNORE_DUP_KEY オプションを設定することでこれを回避できます。This can be avoided by setting the IGNORE_DUP_KEY option on your indexes.

Azure Data Factory とインメモリ テーブルAzure Data Factory and In-Memory Tables

  • 一時テーブルとしてのインメモリ テーブルインメモリ テーブルを使用すると、非常に高速のデータ読み込みが可能になりますが、データをメモリ内に収める必要があります。In-Memory Table as temp tableIn-Memory tables allow for very high-speed data loads but data needs to fit in memory. ベンチマークでは、インメモリ テーブルからディスク ベースのテーブルへの一括読み込みの方が、ID 列とクラスター化インデックスのあるディスク ベースのテーブルへの単一ライターを使用した一括挿入より、約 10 倍速いことが示されています。Benchmarks show bulk loading from an in-memory table to a disk-based table is about 10 times faster than directly bulk inserting using a single writer into the disk-based table with an identity column and a clustered index. この一括挿入のパフォーマンスを利用するには、インメモリ テーブルからディスク ベースのテーブルにデータをコピーする、Azure Data Factory を使用するコピー ジョブを設定します。To leverage this bulk insert performance, set up a copy job using Azure Data Factory that copies data from the in-memory table to the disk-based table.

パフォーマンスの落とし穴の回避Avoiding Performance Pitfalls

データの一括挿入は、データの転送、挿入ステートメントの解析、ステートメントの実行、トランザクション レコードの発行などの繰り返しのオーバーヘッドが回避されるため、単一の挿入でのデータの読み込みよりはるかに高速です。Bulk inserting data is much faster than loading data with single inserts because the repeated overhead of transferring the data, parsing the insert statement, running the statement, and issuing a transaction record is avoided. 代わりに、データをストリーミングするために、ストレージ エンジンへのより効率的なパスが使用されます。Instead, a more efficient path is used into the storage engine to stream the data. ただし、このパスの設定コストは、ディスク ベースのテーブル内の 1 つの挿入ステートメントよりはるかに高くなります。The setup cost of this path is however much higher than a single insert statement in a disk-based table. この損益分岐点は一般に約 100 行であり、それを超えると、一括読み込みの方がほぼ常に効率的です。The break-even point is typically around 100 rows, beyond which bulk loading is almost always more efficient.

受信イベントのレートが低いと、100 行より小さいバッチ サイズが容易に作成される可能性があり、それによって一括挿入が非効率的になり、大量のディスク領域が使用されます。If the incoming events rate is low, it can easily create batch sizes lower than 100 rows, which makes bulk insert inefficient and uses too much disk space. この制限を回避するには、次のいずれかのアクションを実行できます。To work around this limitation, you can do one of these actions:

  • すべての行に単純な挿入を使用するために INSTEAD OF トリガーを作成します。Create an INSTEAD OF trigger to use simple insert for every row.
  • 前のセクションで説明されているインメモリ一時テーブルを使用します。Use an In-Memory temp table as described in the previous section.

このような別のシナリオは、非クラスター化列ストア インデックス (NCCI) に書き込む場合に発生します。ここでは、小さい一括挿入で作成されるセグメントが多くなりすぎて、インデックスがクラッシュする可能性があります。Another such scenario occurs when writing into a non-clustered columnstore index (NCCI), where smaller bulk inserts can create too many segments, that can crash the index. この場合は、代わりにクラスター化列ストア インデックスを使用することをお勧めします。In this case, the recommendation is to use a Clustered Columnstore index instead.


まとめると、SQL 出力に対する Azure Stream Analytics でのパーティション分割された出力機能では、SQL Azure 内のパーティション テーブルに合わせて、ジョブの並列化を調整することにより、スループットが大幅に向上します。In summary, with the partitioned output feature in Azure Stream Analytics for SQL output, aligned parallelization of your job with a partitioned table in SQL Azure should give you significant throughput improvements. インメモリ テーブルからディスク ベースのテーブルへのデータの移動の調整に Azure Data Factory を利用すると、1 桁高いスループットが得られます。Leveraging Azure Data Factory for orchestrating data movement from an In-Memory table into Disk-based tables can give order of magnitude throughput gains. 可能であれば、メッセージの密度を上げることも、全体的なスループットの向上において重要な要素になる可能性があります。If feasible, improving message density can also be a major factor in improving overall throughput.