BT

New Early adopter or innovator? InfoQ has been working on some new features for you. Learn more

Improving Azure SQL Database Performance Using In-Memory Technologies

| by Kent Weare on Jan 21, 2017. Estimated reading time: 3 minutes |

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

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Spelling mistake by Guo Ethan

In the first paragraph, `On-line Analytical Processing (OLTP)` is a spelling mistake.

Nice article by Online Promotion

Thank you for sharing this article

Re: Spelling mistake by Kent Weare

Thank-you, this issue has been addressed.

Kent

Re: Nice article by Kent Weare

Thank you for the feedback - very much appreciated.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

4 Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT