Azure SQL Database built-in In-Memory technologies are now generally available for the Premium database tier including Premium pools. In-memory technology helps optimize the performance of transactional (OLTP), analytics (OLAP), as well as mixed workloads (HTAP). These technologies allow you to achieve phenomenal performance with Azure SQL Database – 75,000 transactions per second for order processing (11X perf gain) and reduced query execution time from 15 seconds to 0.26 (57X perf). You can also use them to reduce cost – on a P2 database obtain 9X perf gain for transactions or 10X perf gain for analytics queries by implementing In-Memory technologies, without any additional cost! See below for details about these performance and cost savings results.
- In-Memory OLTP increases throughput and reduces latency for transaction processing. Scenarios such as trading and gaming really see the performance benefits. Another common scenario is data ingestion from events or IoT devices. You can also use it to speed up caching, data load, and temp table and table variable scenarios.
- Clustered Columnstore Indexes reduce storage footprint (up to 10X) and improve performance for reporting and analytics queries. Use it with fact tables in your data marts to fit more data in your database and improve performance. Use it with historical data in your operational database to archive and be able to query up to 10 times more data.
- Non-clustered Columnstore Indexes for Hybrid Transactional and Analytical Processing (HTAP) gain real-time insights into your business by querying the operational database directly, without the need to run an expensive ETL process and wait for the data warehouse to be populated. Non-clustered Columnstore indexes allow very fast execution of analytics queries on the OLTP database, while reducing the impact on the operational workload.
- In-Memory OLTP and Columnstore can also be combined: you can have a memory-optimized table with a columnstore index, allowing you to both perform very fast transaction processing and run analytics queries very fast on the same data.
Quorum Business Solutions provides innovative software as a service (SaaS) solutions for field operations in the Oil and Gas industry running on Microsoft Azure. With In-Memory OLTP they were able to grow their business by onboarding new customers and supporting organizations at much larger scale, without spending more on more database throughput.
“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. Without in-memory tables, our growth would have required significant effort to multiple areas of the platform to maintain performance. For data-centric services, in-memory support provides instant scale to existing applications with little to no changes outside of the database.” Mark Freydl, solution architect, Quorum Business Solutions
For more details about Quorum Business Solutions’ use of Azure SQL Database and the benefits they see with In-Memory OLTP, read this case study.
Spotlight is a solution provided by Quest for the monitoring of SQL Server deployments in their customers’ data centers and in the cloud. They leverage In-Memory OLTP to speed up the processing of requests and events such that Quest’s customers learn about any potential issues in their environments very quickly.
“Quest’s customers rely on Spotlight to tell them in a timely manner about issues in their SQL Server environments. Quest relies on In-Memory OLTP in Azure SQL Database to provide a robust base on which to build Spotlight features quickly and deliver them to customers. With In-Memory OLTP, we get extremely high throughput and all the goodness of a flexible and well understood programming model based on T-SQL – this means we can deliver a high quality experience to our Spotlight customers very quickly.” Patrick O’Keeffe, executive director, software engineering (information management)
M-Files provides enterprise information management solutions that eliminate information silos and provide quick and easy access to the right content from any core business system and device. Non-clustered columnstore indexes allow running very fast analytical queries on the document data, in turn allowing M-Files’ customers to gain insights and find information much faster.
– “Lightning fast analytical queries as well as online transactions are a critical success factor in M-Files implementations. The addition of updatable non-clustered columnstore indexes in Azure SQL Database and in Microsoft SQL Server 2016 helped us to achieve over 10x faster queries in critical workloads at our customers without significant performance cost on online transaction processing. The change required no code changes in our application and provided instant performance boost both to our clients on-premises as well as to our M-Files Cloud Vault users in Azure.” Antti Nivala, founder and CTO
Performance and cost savings benefits
Both In-Memory OLTP and Columnstore Indexes achieve their respective performance benefits by utilizing resources such as CPU and IO more efficiently. Each Azure SQL Database pricing tier has a certain amount of resources allocated to it. Since In-Memory technologies are more resource-efficient, this means that you can achieve performance gains without having to increase your pricing tier. On the flip side, you can achieve the same performance level with a lower pricing tier when adopting In-Memory technologies in your database.
Some performance results for In-Memory OLTP in Azure SQL Database:
- Order processing benchmark (scale factor 100, with 400 clients) on P15 (the highest tier at the time of writing): 75,000 transactions per second (TPS) with In-Memory OLTP, compared with 6,800 TPS with traditional tables and stored procedures, which translates to 11X performance gain with In-Memory OLTP.
- Also lower performance tiers show impressive results. For a P2 with the same workload (scale factor 5, with 200 clients): 8,900 TPS with In-Memory OLTP, compared with 1,000.
- 30-40% performance gain just by replacing traditional table-valued parameters (TVPs) with memory-optimized TVPs: High Speed IoT Data Ingestion Using In-Memory OLTP in Azure
Performance results for Columnstore indexes, using the sample on this page:
- Using a P15 database (the highest performance tier at the time of writing), the query runs in 0.26 seconds with Columnstore, while it runs in 15 seconds when using traditional indexes with page compression. This translates to a performance gain of 58X!
- Even with lower pricing tiers you see significant performance benefits: using a P1 database, the query runs in 4.8 seconds with Columnstore, while it runs in 27 seconds using traditional indexes with page compression. A performance gain of 5.6X without increasing the pricing tier!
Try out In-Memory technologies in Azure SQL Database today
For more information about In-Memory technologies in Azure SQL Database, tips on how to get started, as well as samples, check out the SQL In-Memory documentation.
Stay tuned on this blog for more detailed information about the individual In-Memory technologies over the next few weeks!