Azure SQL Database offers a straightforward approach to controlling its performance through vertical scaling. Despite its simplicity, scaling up has obvious pricing implications and, while it is an online operation, it might result in rollback of in-flight transactions. If you are looking for a supplemental approach to enhancing performance, then you might want to consider implementing in-memory technologies, which are part of the Azure SQL Database feature set.
Azure SQL Database provides three basic in-memory based capabilities (built into the underlying database engine) that can contribute in a meaningful way to performance improvements:
- In-Memory Online Transactional Processing (OLTP)
- Clustered columnstore indexes intended primarily for Online Analytical Processing (OLAP) workloads
- Nonclustered columnstore indexes geared towards Hybrid Transactional/Analytical Processing (HTAP) workloads
In-memory OLTP has been introduced in SQL Server 2014. Its functionality ties directly to memory-optimized tables and natively-compiled stored procedures, which can deliver significant performance benefits (especially when used collectively). These benefits result not only from in-memory operations, but also from a different approach to transactional consistency, which eliminates locks and latches, relying instead on non-blocking, multi-version optimistic concurrency mechanisms. Potential conflicts have no impact on read-only operations, but instead are limited only to write-write and read-write scenarios. It is important to note that this innovative approach does not affect data durability. All changes are recorded into transaction logs as soon as transactions are committed, the same way as they are in the case of disk-based tables.
To implement In-Memory OLTP you can either start with new memory-optimized tables or convert existing, disk-based ones. Azure SQL Database assists with the process of identifying disk-based tables that could yield meaningful performance advantages following their conversion by providing Transaction Performance Analysis Overview reports. It also offers Memory Optimization Wizard, which simplifies the migration process. While it is possible to perform the migration by running the INSERT INTO T-SQL statement, the wizard will identify any unsupported features that must be remediated. As part of the migration, you should also consider converting any interpreted stored procedures into natively compiled ones.
Since the target tables must reside in memory, you need to choose the performance level or amount of resources (depending on the purchasing model) that includes a sufficient amount of In-Memory OLTP storage quotas. These quotas must accommodate resources consumed by:
- current versions of user data rows
- operational overhead associated with running ALTER TABLE operations
Clustered columnstore indexes, introduced in SQL Server 2012, store table data in columns, rather than rows. Since the base table is a subject to columnar compression, this technique tends to deliver significant storage savings and, consequently, leads to meaningful performance improvements of analytics and reporting workloads. These benefits result from commonly encountered data patterns, where content of individual columns or groups of columns yields much better compression ratios than content of individual rows or group of rows.
Nonclustered columnstore indexes facilitate scenarios where data stored in SQL Server or Azure SQL Database in the traditional rowstore format can serve as the source for OLAP workloads, eliminating the need for data extraction, transformation, and load (ETL) into a data warehouse before analytical or reporting queries can be run. Alternatively, it is also possible to configure memory-optimized tables with columnstore indexes. This gives you the ability to run OLTP and OLAP workloads concurrently against the same data. Unlike memory-optimized tables, columnstore indexes do not have to reside fully in memory. As a result, to account for their size, you should focus on size limitations of Azure SQL Database, rather than its memory limits.
While in-memory OLTP is available in all current editions of SQL Server, Azure SQL Database supports it only in its Premium and Business Critical pricing tiers. However, within the Premium and Business Critical pricing tiers, you have the option of using (depending on the purchasing model) any performance level or any amount of compute and storage resources. As a matter of fact, you should be able to lower the performance level (and effectively lower the cost) while maintaining or even exceeding existing performance when switching from on-disk tables to memory-optimized tables. It is also possible to leverage these benefits when placing multiple Azure SQL Database instances into an elastic pool, as long as you associate the pool to the Premium and Business Critical pricing tier.
It is important to note that when changing the Premium or Business Critical pricing tier to a lower one, you should remove first all memory-optimized tables, table types, and natively compiled T-SQL modules. Columnstore indexes are supported in the Standard S3 and higher (including Premium) pricing tiers. Lowering the pricing tier and performance level below this threshold will yield indexes non-operational, so it is recommended that you drop all clustered columnstore indexes first.
This concludes our overview of in-memory based capabilities of Azure SQL Database. In our upcoming articles, we will take a closer look at their implementation, including potential challenges involved in migration of disk-based tables.
See all articles by Marcin Policht