With the release of SQL Server 2016 RC0, last month, I thought it would be pertinent to review the New Features and Enhancements being made to our favourite Database Server. This is the first in a series of blogs where I would discuss the changes that are being released as part of SQL Server 2016. In this post – we will review the new features and enhancements that are being made to In-Memory OLTP.
Introduction
Over the past few decades, the prices of memory have been steadily falling and at the same time, it’s gotten much cheaper to procure a multi-core server. This has made it affordable for enterprises to invest in high memory multi-core servers for their operations. Due to the availability of high memory and demands for low latency and high concurrency for transactional operations Microsoft had introduced In-Memory OLTP in SQL Server 2014.
SQL Server In-Memory OLTP is a powerful technology that addresses issues with using disk based data structures such as I/O and wait times for disk reads or locks to be released. It provides row-based, in-memory data access and modification capabilities used mostly for transaction processing workloads.
This technology uses lock and latch free architecture that enables linear scaling. In-Memory OLTP removes the issues of waiting for locks to be released, using a new type of multi-version optimistic concurrency control.
Hence latency and concurrency improves a lot with these In-Memory OLTP architectural changes. In-Memory OLTP has memory-optimized data structures and provides native compilation, creating more efficient data access and querying capabilities.
The main components of SQL Server In-Memory OLTP are
- Memory-optimised tables and indexes: These tables store their data in memory using multiple versions of the data from each row. They are fully transactional, durable and can be accessed using T-SQL statements just like the disk-based tables. One version of the table is stored in the active memory and the standard version is stored on the hard disk. Transactions, however, only access data directly from the version in the memory and hence run faster.
- Natively compiled modules: These are object types supported by In-Memory OLTP that can be compiled to machine code and have the potential to increase performance even more. Native compilation allows faster data access and more efficient query execution than interpreted Transact-SQL. Currently, stored procedures, triggers, scalar user-defined functions and inline multi-statement user-defined functions are supported.
New Features and Enhancements in SQL Server 2016
Some of the important In-Memory OLTP specific features that are enhanced or improved in SQL Server 2016 are
- Support for larger tables: In SQL Server 2016, the memory-optimised durable table can have a size for 2 TB per table. This limit was 256 GB in SQL Server 2014.
- Support for ALTER TABLE / ALTER PROCEDURE: In SQL Server 2014, changes to memory optimized tables was not allowed once the table was created. SQL Server 2016 now supports the ALTER TABLE statement and can be used to add, drop or alter columns on a memory-optimised table. With support for ALTER PROCEDURE, natively compiled stored procedures can be changed as well.
- Improved T-SQL coverage: SQL Server 2016 has better support for T-SQL clauses including left and right outer joins, union all, unique indexes and distinct syntax. Nesting of natively compiled stored procedures is supported along with other improvements such as support for LOB data types, Collations, Constraints and Row-level Security.
- Support for Parallelism: Parallel Plans for operations accessing memory-optimized tables are now supported in SQL Server 2016. Due to this, Columnstore indexes on memory-optimized table can be used.
- Transaction Performance Report: The Transaction Performance Report evaluates In-Memory OLTP and improves database application’s performance. It also indicates the work needed to support In-Memory OLTP. In SQL Server 2016, this report now no longer requires the configuration of data collectors or management data warehouse and can be directly run on a Production database
- Support for Multiple Active Result Sets (MARS): In-Memory OLTP now supports MARS using queries and natively compiled stored procedures. This enables requesting data from multiple queries without the need to retrieve each result set before sending the next request. MARS will need to be explicitly enabled in a connection since its disabled by default. With support for MARS, Entity Framework will be easier to implement with In-Memory OLTP.
- Support for Transparent Data Encryption (TDE): In SQL Server 2014, an encryption enabled database would not encrypt data stored on MEMORY_OPTIMIZED_DATA filegroup. However, due to the support for TDE, the storage for memory optimized tables can now be encrypted.
For a complete list of improved and enhanced feature set – please refer to Microsoft SQL Server 2016 Release Notes.
Conclusion
In-Memory OLTP is a memory-optimised database engine integrated into the SQL Server engine, optimised for OLTP and can significantly improve OLTP database application performance. In SQL Server 2016, several improvements have been made to or existing features enhanced for In-Memory OLTP. These changes make the In-Memory OLTP a much more mature technology that can be easier to adopt and maintain.
