Introduction to Memory-Optimized Tables
Memory-optimized tables are tables, created using CREATE TABLE (Transact-SQL).
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.
The primary store for memory-optimized tables is main memory; memory-optimized tables reside in memory. Rows in the table are read from and written to memory. The entire table resides in memory. A second copy of the table data is maintained on disk, but only for durability purposes. See Creating and Managing Storage for Memory-Optimized Objects for more information about durable tables. Data in memory-optimized tables is only read from disk during database recovery. For example, after a server restart.
For even greater performance gains, In-Memory OLTP supports durable tables with transaction durability delayed. Delayed durable transactions are saved to disk soon after the transaction has committed and returned control to the client. In exchange for the increased performance, committed transactions that have not saved to disk are lost in a server crash or fail over.
Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but the data will not be recovered if there is a server crash or failover.
In-Memory OLTP is integrated with SQL Server to provide a seamless experience in all areas such as development, deployment, manageability, and supportability. A database can contain in-memory as well as disk-based objects.
Rows in memory-optimized tables are versioned. This means that each row in the table potentially has multiple versions. All row versions are maintained in the same table data structure. Row versioning is used to allow concurrent reads and writes on the same row. For more information about concurrent reads and writes on the same row, see Transactions in Memory-Optimized Tables.
The following figure illustrates multi-versioning. The figure shows a table with three rows and each row has different versions.
The table has three rows: r1, r2, and r3. r1 has three versions, r2 has two versions, and r3 has four versions. Note that different versions of the same row do not necessarily occupy consecutive memory locations. The different row versions can be dispersed throughout the table data structure.
The memory-optimized table data structure can be seen as a collection of row versions. Rows in disk-based tables are organized in pages and extents, and individual rows addressed using page number and page offset, row versions in memory-optimized tables are addressed using 8-byte memory pointers.
Data in memory-optimized tables is accessed in two ways:
Through natively compiled stored procedures.
Through interpreted Transact-SQL, outside of a natively-compiled stored procedure. These Transact-SQL statements may be either inside interpreted stored procedures or they may be ad-hoc Transact-SQL statements.
Memory-optimized tables can be accessed most efficiently from natively compiled stored procedures (Introduction to Natively Compiled Stored Procedures). Memory-optimized tables can also be accessed with (traditional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables without a natively compiled stored procedure. Some examples of interpreted Transact-SQL access include accessing a memory-optimized table from a DML trigger, ad hoc Transact-SQL batch, view, and table-valued function.
The following table summarizes native and interpreted Transact-SQL access for various objects.
Access Using a Natively Compiled Stored Procedure
Interpreted Transact-SQL Access
Memory-optimized table type
Natively compiled stored procedure
Nesting of natively compiled stored procedures is now supported. You can use the EXECUTE syntax inside the stored procedures, as long as the referenced procedure is also natively compiled.
1 You cannot access a memory-optimized table or natively compiled stored procedure from the context connection (the connection from SQL Server when executing a CLR module). You can, however, create and open another connection from which you can access memory-optimized tables and natively compiled stored procedures.
The following factors will affect the performance gains that can be achieved with In-Memory OLTP:
An application with many calls to short stored procedures may see a smaller performance gain compared to an application with fewer calls and more functionality implemented in each stored procedure.
- Transact-SQL Execution
In-Memory OLTP achieves the best performance when using natively compiled stored procedures rather than interpreted stored procedures or query execution. There can be a benefit to accessing memory-optimized tables from such stored procedures.
- Range Scan vs Point Lookup
Memory-optimized nonclustered indexes support range scans and ordered scans. For point lookups, memory-optimized hash indexes have better performance than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have better performance than disk-based indexes.
Index operations are not logged and they exist only in memory.
Applications whose performance is affected by engine-level concurrency, such as latch contention or blocking, improves significantly when the application moves to In-Memory OLTP.
The following table lists the performance and scalability issues that are commonly found in relational databases and how In-Memory OLTP can improve performance.
In-Memory OLTP Impact
High resource (CPU, I/O, network or memory) usage.
Most scaling issues in SQL Server applications are caused by concurrency issues such as contention in locks, latches, and spinlocks.
For a brief discussion of typical scenarios where SQL Server In-Memory OLTP can improve performance, see In-Memory OLTP.