Introducing SQL Server In-Memory OLTP

In-Memory OLTP, aka Hekaton, originally shipped with 2014, and although it certainly helped the performance of certain types of workload, it then had certain restrictions that impeded its widespread adoption.With SQL Server 2016, there is more support for In-Memory OLTP and a more seamless integration with SQL Server's Database Engine. It is time to consider whether In-Memory OLTP can help those pinch-points in your data throughput, explains Artemakis Artemiou.

1. Introduction

In-Memory OLTP, also known as ‘Hekaton’ and ‘In-Memory Optimization’, is Microsoft’s latest in-memory processing technology. In-Memory OLTP is optimized for Online Transaction Processing (OLTP). It is integrated into SQL Server’s Database Engine and can be used in the exact same manner as any other Database Engine component.

In-Memory OLTP originally shipped with SQL Server 2014 and it mainly features two new data structures which are Memory-Optimized Tables, and Natively-Compiled Stored Procedures.

Memory-optimized tables

Memory-optimized tables store their data into memory using multiple versions of each row’s data. This technique is characterized as ‘non-blocking multi-version optimistic concurrency control’ and eliminates both locks and latches, thereby achieving significant performance advantages.

The main features of memory-optimized tables are:

  • Rows in the table are read from, and written to, memory
  • The entire table resides in memory
  • Non-blocking multi-version optimistic concurrency control
  • The option of durable & non-durable data
  • A second copy is maintained on disk for durability (if enabled)
  • Data in memory-optimized tables is only read from disk during database recovery
  • It is interoperable with disk-based tables

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. The main features of a natively-compiled stored procedure are:

  • It is compiled to native code (DLL) upon its creation (the interpreted stored procedures are compiled at first execution)
  • Aggressive optimizations take time at compile time
  • It can only interact with memory-optimized tables
  • The call to a natively-compiled stored procedure is actually a call to its DLL entry point

2. Best-Suited Workloads

I’ve already mentioned that In-Memory OLTP in SQL Server is optimized for OLTP processing. This means that it performs best for certain specific types of workload. It does not mean, however, that if it is used against other types of workload it won’t perform well; but there is a recommendation from Microsoft which specifies the main workload areas that are most likely to see the greatest benefits from using In-Memory OLTP. This can help you identify if you have a workload that can be potentially optimized using SQL Server’s In-Memory Optimization.

The below table summarizes these workload types.

Workload Type

Examples

Main Benefits of In-Memory OLTP

High Data Insert Rate

  • Smart Metering
  • System Telemetry
  • eliminate contention
  • minimize i/o logging

Read Performance and Scale

Social Network Browsing

  • Eliminate contention
  • Efficient data retrieval
  • Minimize code execution time
  • CPU efficiency for scale

Compute Heavy Data Processing

Manufacturing supply chains or retailers

  • Eliminate contention
  • Minimize code execution time
  • Efficient data processing

Low Latency

  • Online Gaming Platforms
  • Capital Markets
  • Eliminate contention
  • Minimize code execution time
  • Efficient data retrieval

Session State Management

Managing sessions (i.e. user requests, etc.) for a heavily-visited websites

  • Eliminate contention
  • Efficient data retrieval
  • Optional I/O reduction/removal

Table1: Best-Suited Workload Types for In-Memory OLTP.

3. Memory-Optimized Tables

Memory-optimized tables are by default durable, meaning that in case of a server crash or failover the

data will be recovered from the transaction logs. However, you can also define a memory-optimized table as non-durable. That means that transactions on these tables do not require any disk I/O, however in the case of server crash or failover, the data in these tables will not be recovered because the memory buffers will be flashed.

The decision to use durable or non-durable memory-optimized tables depends on the business requirements. For example, if you want to use a memory-optimized table as part of data warehouse staging process where it gets deleted every day and re-populated with millions of records in order to perform computations and pass the results to other tables, then it would not be a bad idea to use a non-durable memory-optimized table. If however you have a table with permanent data that gets updated daily and you decide to migrate it to a memory-optimized table then you should consider using a durable table memory-optimized table.

Let’s see how a disk-based table can be defined/migrated as a memory-optimized table in SQL Server. For example consider a table named “Product” with the following definition

Listing 1: The Disk-Based Table “Product”.

The corresponding non-durable memory-optimized table for “Product” would be defined as below:

Listing 2: The Memory-Optimized Non-Durable Table “Product”.

The Durability setting for the above table is set to “SCHEMA_ONLY” meaning that only the schema of the table will be recovered in the case of a server crash or failover.

Also, the corresponding durable memory-optimized table for “Product” would be defined as below, having as the only difference from the previous one the value “SCHEMA_AND_DATA” for the Durability setting:

Listing 3: The Memory-Optimized Durable Table “Product”.

As well as the “Durability” setting in the memory-optimized table definitions, you also need to take non-clustered indexes into consideration. Memory-optimized tables do not support clustered indexes but do however support non-clustered indexes (currently up to eight). Along with each index specification you will also need to specify the BUCKET_COUNT value. The recommended value for this, is to be between 1.5 and 2 times the estimated number of unique values (500 unique products in this example) for the column indexed by the non-clustered index. If you estimate that you are going to have large tables (i.e. with over 5 million unique values or more), then for saving up memory consumption you can set the Bucket_Count value to 1.5 times the number of unique values. In the opposite case you can set the Bucket_Count value to 2 times the number of unique values.

In SQL Server 2014, there is was a restriction on memory-optimized tables that requires that every string column that participates in a non-clustered index must have a *_BIN2 collation. Either you need to have the default collation set as *_BIN2 or should explicitly specify a *_BIN2 collation for the column that will participate in the index. An example is shown below:

Listing 4: Specifying a *_BIN2 Collation for the Memory-Optimized Table “Product”.

In SQL Server 2016 In-Memory OLTP, these limitations do not exist as there is full support for all collations.

4. Natively-Compiled Stored-Procedures

Natively-compiled stored procedures can only be used for processing memory-optimized tables, as well as other T-SQL constructs that are supported such as subqueries, scalar user-defined functions, built-in math functions, etc. They are compiled to native code and execute without the need for any further compilation or interpretation.

Based on the previous examples, the listing below has the definition of a natively-compiled stored procedure that updates the “Price” column of the memory-optimized table “Product”.

Listing 5: The Natively-Compiled Stored Procedure for Updating the Table “Product”.

Some points to note in the above definition of the natively-compiled stored procedure are:

  • The WITH NATIVE_COMPILATION clause is used
  • The SCHMABINDING clause is required because it bounds the stored procedure to the schema of the objects it references
  • The BEGIN_ATOMIC clause is required because the stored procedure must consist of exactly one block along with the Transaction Isolation Level.

5. Limitations

In-Memory OLTP had certain limitations when originally shipped with SQL Server 2014. Examples of such limitations are: not being able to create Indexes on NULLable columns or use OUTER JOIN, UNION, DISTINCT etc. However, in SQL Server 2016 we see that many of these limitations are lifted.

The below table compares the main limitations and features of In-Memory OLTP in SQL Server 2014 in comparison to SQL Server 2016 (CTP 2).

Feature / Limitation

SQL Server 2014

SQL Server 2016 CTP2

Maximum memory for memory-optimized tables

Recommendation (not hard limit): 256 GB

Recommendation (not hard limit): 2TB

Collation support

Must use a *_BIN2 collation for:

(i) Character columns used as all or part of an index key.

(ii) All comparisons/sorting between character values in natively-compiled modules.

Must use Latin code pages for char and varchar columns.

All collations are fully supported

Alter memory-optimized tables (after creation)

Not Supported

Supported

Alter natively-compiled stored procedures

Not Supported

Supported

Parallel plan for operations accessing memory-optimized tables

Not Supported

Supported

Transparent Data Encryption (TDE)

Not Supported

Supported

Use of the below language constructs in natively-compiled stored procedures:

  • LEFT and RIGHT OUTER JOIN
  • SELECT DISTINCT
  • OR and NOT operators
  • Subqueries in all clauses of a SELECT statement
  • Nested stored procedure calls
  • UNION and UNION ALL
  • All built-in math functions

Not Supported

Supported

DML triggers in memory-optimized tables

Not Supported

Supported

(AFTER triggers, natively-compiled)

Multiple Active Result Sets (MARS)

Not Supported

Supported

Large Objects (LOBs):

  • varchar(max)
  • nvarchar(max)
  • varbinary(max)

Not Supported

Supported

Offline Checkpoint Threads

1

Multiple Threads

Natively-compiled, scalar user-defined functions

Not Supported

Supported

Indexes on NULLable columns

Not Supported

Supported

Table2: Comparison of In-Memory OLTP Limitations/Features in SQL Server 2014 vs SQL Server 2016.

6. Conclusions

In this article we discussed about memory-optimized tables and natively-compiled stored procedures which are provided in SQL Server 2014 or later. These new data structures are part of the In-Memory OLTP Engine of SQL Server which can be used in order to achieve significant performance over processing that uses “traditional” disk-based data structures.

We have also discussed about the workload types which can benefit the most in terms of performance when In-Memory OLTP is used. Furthermore, we saw simple examples on how we can define memory-optimized tables and natively-compiled stored procedures. Last, we discussed about the limitations and features of In-Memory OLTP in SQL Server 2014 in comparison to SQL Server 2016 (CTP2).

As we can see, in SQL Server 2016 there is more support for In-Memory OLTP and a more seamless integration with SQL Server’s Database Engine. In-Memory OLTP is here to stay and following the momentum of its predecessor technologies (PowerPivot engine/Vertipaq, In-Memory Columnstore Index) it is something that constantly evolves, aiming at providing a powerful processing engine for intensive database operations.

References

See also…

Tags: , , , , , , ,

  • 38068 views

  • Rate
    [Total: 46    Average: 4.5/5]
  • Win32nipuh

    T-SQL proc and MO tables
    Thank you.

    Is it possible to work with MO tables in T-SQL stored procedures?
    Does it make sense?

    In some cases it is impossible to create pure native_compiled procedure to work with MO tables because of limitations.

  • artemakis

    T-SQL proc and MO tables
    Hi Win32nipuh,

    Thank you for your comment.

    Within a natively-compiled stored procedure you can only use memory-optimized tables and related structures (i.e. table variables).

    However, yes of course, you can use memory-optimized tables in traditional (disk-based) stored procedures.

    So, in the case where you cannot use a natively-compiled stored procedure due to any restrictions, you can use a disk-based stored procedure thus combining in it memory-optimized tables along with disk-based objects.

    Regards,
    Artemakis

  • vas

    Dear artemakis,

    This is really good explanation of In-memory OLTP those who don’t know will understand in a clear way..Thanks for this article, hope you do the many more useful discussions on SQL Server..

    Thanks again,
    Vas

    • Dear Vas,

      Thank you very much for your kind words.

      I believe In-Memory OLTP is an exciting and useful technology and I will sure continue writing about it.

      Kind regards,
      Artemakis