SQL Server In-Memory OLTP 2016

In-memory OLTP is a new feature introduced on SQL Server 2014.

Why In-Memory?

This feature is used to improve OLTP database application performance. Memory-optimized-tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables.

• ·         A query can reference both memory-optimized tables and disk-based tables.
• ·         A transaction can update data in memory-optimized tables and disk-based tables.
• ·         Stored procedures that only reference memory-optimized tables can be natively compiled into machine code for further performance improvements.

Benefits of this feature:

• ·         In-Memory OLTP will improve performance best in OLTP with short-running transactions.
• ·         Optimistic concurrency control that eliminates logical locks.
• ·         Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.
• ·         Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures, when accessing a memory-optimized table.

Deep dive into SQL Server in-memory OLTP

It mainly features two new data structures which are Memory-Optimized Tables, and Natively-Compiled Stored Procedures.

Memory-Optimized Tables

Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-based tables, fully durable transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and natively compiled stored procedures support a subset of Transact-SQL.

Natively-compiled stored procedures

A natively-compiled stored procedure is a SQL Server object that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. 

Here comes SQL server 2016 In-Memory OLTP

SQL Server 2016 offers significant improvements in In-Memory feature.

Some of the important features with enhancements

In-Memory Performance

SQL Server 2016 in-memory technology provides improved query performance. For example, the same transactions that run against SQL Server 2014 memory-optimized tables will run 30 times faster when run against SQL Server 2016 memory-optimized tables.

Supported size for In-Memory Tables

In SQL Server 2014, size of memory optimized tables is maximum of 256 GB of memory.

In SQL Server 2016 the recommended maximum is 2 TB of memory for memory-optimized tables.

Schema and data changes

SQL Server 2014 in-memory OLTP does not allow you to modify memory-optimized tables once they are created. If you have to make changes to a memory-optimized table schema or rebuild its indexes, you must drop and re-create the memory-optimized table, using DROP TABLE and CREATE TABLE statements. 

From SQL Server 2016 in-memory OLTP, this is no longer a constraint; you can make changes to memory-optimized tables after creation, using the ALTER TABLE statement.

Transparent Data Encryption (TDE)

In SQL Server 2014, the TDE feature is not compatible with memory-optimized tables. That means data stored for memory-optimized tables on a MEMORY_OPTIMIZED_DATA file-group are not encrypted on disk. 

TDE function is fully compatible with SQL Server 2016 memory-optimized tables

  

AlwaysON

In SQL Server 2014, the visibility of data in memory-optimized tables on secondary replicas is delayed by few transactions.

SQL Server 2016 memory-optimized tables; like disk-based tables, all data is visible immediately to the user.

Below are the complete feature enhancements from 2014 to 2016

Feature/Limit	SQL Server 2014	SQL Server 2016
Maximum combined size of durable tables	256 GB	2 TB
LOB (varbinary(max), [n]varchar(max))	Not supported	Supported*
Transparent Data Encryption (TDE)	Not supported	Supported
Offline Checkpoint Threads	1	1 per container
ALTER PROCEDURE / sp_recompile	Not supported	Supported (fully online)
Nested native procedure calls	Not supported	Supported
Natively-compiled scalar UDFs	Not supported	Supported
ALTER TABLE	Not supported (DROP / re-CREATE)	Partially supported (offline – details below)
DML triggers	Not supported	Partially supported (AFTER, natively compiled)
Indexes on NULLable columns	Not supported	Supported
Non-BIN2 collations in index key columns	Not supported	Supported
Non-Latin codepages for [var]char columns	Not supported	Supported
Non-BIN2 comparison / sorting in native modules	Not supported	Supported
Foreign Keys	Not supported	Supported
Check/Unique Constraints	Not supported	Supported
Parallelism	Not supported	Supported
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN	Not supported	Supported
Multiple Active Result Sets (MARS) (Means better Entity Framework support.)	Not supported	Supported
SSMS Table Designer	Not supported	Supported

Creating Memory optimized table

CREATE TABLE [Employee](

[EmployeeID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),

[Name] NVARCHAR(250) NOT NULL INDEX [IName] HASH WITH (BUCKET_COUNT = 1000000),

[EmployeeSince] DATETIME NULL

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Below are keywords used in above query.

MEMORY_OPTIMIZED = ON

This clause triggers the database engine to consider this table as memory optimized table

NONCLUSTERED HASH

Memory optimized tables can have up to eight non-clustered indexes, they don’t support clustered indexes. These indexes don't duplicate data, but rather just point to the rows in the chain. In-memory OLTP maintains these indexes online and they get created each time during database recovery.

Only two types of Indexes can be created on In-Memory tables, i.e. Non Clustered Hash Index and Non Clustered Index (aka Range Index).

Hash index is an array of N buckets or slots, each one containing a pointer to a row.

Non Clustered Indexes are inherently Covering, and all columns are automatically INCLUDED in the Index.

BUCKET_COUNT

With index specification, you also need to specify the BUCKET COUNT.  The recommended value for this should be two times the expected number of unique values of that column.

The value of BUCKET_COUNT represents the number of buckets a Hash index has. We can think of a bucket as an index position in which a row address is stored.

DURABILITY = SCHEMA_AND_DATA

With memory optimized tables you can either specify DURABILITY = SCHEMA_AND_DATA or DURABILITY = SCHEMA_ONLY. 

DURABILITY = SCHEMA_AND_DATA is a default option, with this option changes to table data are logged in the SQL Server transaction log and data gets stored in a file-stream based file-group. So that data can be recovered after an unexpected crash or a server restart.

DURABILITY = SCHEMA_ONLY with this option changes to table data are not logged, only structure or schema of table is saved. After a server restart the table will be available, but it will be an empty table.

---------------------------------------------------------------------------------------------

In-Memory OLTP in Azure

In-Memory OLTP can be used to improve the performance of OLTP workload in Premium Azure SQL Databases without increasing the performance level. With In-Memory OLTP in SQL Server, customers achieve up to 30x transactional performance gains.

A memory-optimized table has one representation of itself in active memory, in addition to the standard representation on a hard drive. Business transactions against the table run faster because they directly interact with only the representation that is in active memory.

Premium databases created in November 2015 or later do support the In-Memory feature.

Check the table compatibility for In-Memory using Memory Optimization Advisor.

Bringing this technology to the cloud means customers will be able to take advantage of in-memory OLTP in a fully managed database-as-a-service with 99.99% SLA.