In late 2016, Microsoft announced the general availability (GA) of Azure SQL Database In-Memory technologies. In-Memory processing is only available in Azure Premium database tiers and provides performance improvements for On-line Transaction Processing (OLTP), Clustered Columnstore Indexes and Non-clustered Columnstore Indexes for Hybrid Transactional and Analytical Processing (HTAP) scenarios.
Azure SQL Database shares the same In-Memory technologies that exist in SQL Server. Microsoft first introduced In-Memory capabilities for both Columnstore Indexes and OLTP in SQL Server 2012 and SQL Server 2014 respectively.
Microsoft claims up to 30% improved performance for OLTP and up to 100x faster Analytics workloads and positions In-Memory use cases for organizations looking to:
Provide headroom to scale without changing their service tier. [This opportunity exists] because of the more efficient query and transaction processing, In-Memory technologies also help you to reduce cost. You typically don't need to upgrade the pricing tier of the database to achieve performance gains. In some cases, you might even be able reduce the pricing tier, while still seeing performance improvements with In-Memory technologies.
Microsoft has identified the following benefits of using In-Memory Technologies in Azure SQL Database:
- In-Memory OLTP increases throughput and reduces latency for transaction processing.
- Clustered columnstore indexes reduce your storage footprint (up to 10 times) and improve performance for reporting and analytics queries. You can use it with fact tables in your data marts to fit more data in your database and improve performance. Also, you can use it with historical data in your operational database to archive and be able to query up to 10 times more data.
- Nonclustered columnstore indexes for HTAP help you gain real-time insights into your business through querying the operational database directly, without the need to run an expensive extract, transform, and load (ETL) process and wait for the data warehouse to be populated. Nonclustered columnstore indexes allow very fast execution of analytics queries on the OLTP database, while reducing the impact on the operational workload.
- You can also combine In-Memory OLTP and columnstore indexes. You can have a memory-optimized table with a columnstore index. This allows you to both perform very fast transaction processing and run analytics queries very quickly on the same data.
In a recent Data Exposed episode, Scott Klein, senior technical evangelist, and Jos de Bruijn, senior program manager, from Microsoft provided a demonstration of Azure SQL Database In-Memory processing. The application they built simulated Internet of Things (IoT) device telemetry ingestion. In this demo, they performed a simulation of 1 Million meters publishing device reads. In the initial demo, the Azure SQL Database did not have In-Memory objects enabled. The result was that both CPU and Log IO were under considerable pressure with CPU % hovering around 89% and Log IO reaching 86%. Once In-Memory optimized objects were introduced, CPU % reduced to 10.47% and Log IO reduced down to 34%.
Image source: (screenshot) https://channel9.msdn.com/Shows/Data-Exposed/In-Memory-OTLP-in-Azure-SQL-DB
Developers may be tempted to increase their Azure SQL Database Throughput Units (DTU) when experiencing performance pressures. The answer, to performance challenges may now lie in optimization instead of scaling up. Mark Freydl, solution architect at Quorum Business Solutions, explains:
Scalable performance is critical with our IoT platform for oil and gas that must run 24/7/365. The addition of In-Memory OLTP tables and native-compiled stored procedures on Azure SQL Database for a few key operations immediately reduced our overall DTU consumption by seventy percent.
In addition to the IoT telemetry example, Microsoft has identified additional use cases for In-Memory processing, including financial trading, gaming, ASP.Net session management, Tempdb replacements and avoiding inefficient Extract Transform Load (ETL) operations.
Image source: (screenshot) https://channel9.msdn.com/Shows/Data-Exposed/In-Memory-OTLP-in-Azure-SQL-DB
