Azure Stream Analytics SQL Azure Output and In-memory Tables

In the previous post, we discussed some ways to optimize Azure Stream Analytics SQL Output. In this post, let’s continue the discussion and have a look at some other optimization best practices.

Partitioned Table and Indexes: A good way to ensure that there are no bottlenecks during partition writes is to use partitioned SQL table with partitioned indexes. Also, it is recommended to use the same column as the partition key for both table and indexes.

Avoid Unique Key violations: Unique key violation will generate a warning message in the Azure Stream Analytics activity log. Most of the unique key violations occur during recovery cases. Recovery cases are instances where Azure Stream Analytics jobs have to recover after a stoppage or failure. This will be discussed further in a future post.

In-Memory Table as temp table: In-memory tables can be used as staging tables and improve the performance about 10 times compared to SQL tables. Azure Data Factory can then be used to move data from the in-memory table to SQL table. The main limitation of in-memory tables is the size. The in-memory tables need to fit in the memory, which makes them unsuitable for huge data loads.

Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-sql-output-perf