Applicable on: SQL Server 2014, SQL Server 2016
What is In-Memory OLTP?
New in SQL Server 2014, Microsoft added new feature called In-Memory OLTP which can significantly improve OLTP database application performance by placing the whole table records into the memory. There are some limitations to use this features which will be rectified on next version. In-Memory Optimized tables can be durable or non-durable. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP and the submitted query can retrieve data from disk-based or in-memory tables at same time.
Benefits of using In-Memory Tables:
- Eliminate contention.
- Reduce logging.
- Lower latency data retrieval.
- Minimize code execution time.
- Efficient data retrieval.
- Optional IO reduction or removal, when using non-durable tables.
Implementation scenarios of In-Memory Tables:
- High data insertion rate from multiple concurrent connections.
- Read performance and scale with periodic batch inserts and updates.
- Intensive business logic processing in the database server.
- Low latency.
- Session state management.
In-Memory OLTP Components
In-Memory OLTP engine consists of three (3) major components called ‘In Memory Native Compiler’ which is responsible to compile and generate ‘DLL’ output file per native stored procedure, the DLL file is generated by C language, ‘Query Interoperability’ which is responsible for Disk-based and Memory-Based table/TSQL integration and at the last ‘Storage Engine for Memory Optimized Tables and Indexes’ for storing and retrieving data into memory and transaction log file for in-memory optimized tables.
Memory Optimized Table Requirements
For optimal performance, SQL Server In-Memory OLTP needs below requirements to be able to run.
- X64 Architecture Processor
- Enough Memory (x2 Table Size)
- Enough Storage (x2 Table Size)
- Processor Needs to Support cmpxchg16b
- X64 Architecture Edition
- Enterprise or Developer Edition
- Hyper-V to Support cmpxchg16b (If needed)
- Enable Instant File Initialization
SQL Server needs enough memory to hold the data in memory-optimized tables and indexes. To account for row versions, you should provide an amount of memory that is two times the expected size of memory-optimized tables and indexes. But the actual amount of memory needed will depend on your workload. You should monitor your memory usage and make adjustments as needed. The size of data in memory-optimized tables must not exceed the allowed percentage of the pool.
Memory Optimized Tables
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.
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.
In-Memory OLTP supports durable tables with transaction durability delayed. Delayed durable transactions are saved to disk soon after the transaction has committed. In exchange for the increased performance, committed transactions that have not saved to disk are lost in a server crash or failover.
You cannot access a memory-optimized table or natively compiled stored procedure from the SQL CLR context connection. You can, however, create and open another connection from which you can access memory-optimized tables and natively compiled stored procedures.
Memory Optimized Indexes
A memory-optimized table can have up to 8 indexes, including the index created with the primary key. Regarding the number of indexes created on a memory-optimized table, consider the following:
- Specify the indexes you need when you create the table.
- Do not create an index that you rarely use:
Garbage collection works best if all indexes on the table are frequently used. Rarely-used indexes may cause the garbage collection system to not perform optimally for old row versions.
Each memory-optimized table must have at least one index. Note that each PRIMARY KEY constraint implicitly creates an index. Therefore, if a table has a primary key, it has an index. A primary key is a requirement for a durable memory-optimized table. When querying a memory-optimized table, hash indexes perform better when the predicate clause contains only equality predicates. The predicate must include all columns in the hash index key. A hash index will revert to a scan given an inequality predicate.
When querying a memory-optimized table with inequality predicates, nonclustered indexes will perform better than nonclustered hash indexes. The hash index requires a key (to hash) to seek Into the index. If an index key consists of two columns and you only provide the first column, SQL Server does not have a complete key to hash. This will result in an index scan query plan. Usage determines which columns should be indexed.
When a column in a nonclustered index has the same value in many rows (index key columns have a lot of duplicate values), performance can degrade for updates, inserts, and deletions. One way to improve performance in this situation is to add another column to the nonclustered index.
The hashing function used for hash indexes has the following Characteristics:
- SQL Server has one hash function that is used for all hash indexes.
- The hash function is deterministic. The same index key is always mapped to the same bucket in the hash index.
- Multiple index keys may be mapped to the same hash bucket.
- The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson distribution.
Poisson distribution is not an even distribution. Index key values are not evenly distributed in the hash buckets. For example, a Poisson distribution of n distinct index keys over n hash buckets results in approximately one third empty buckets, one third of the buckets containing one index key, and the other third containing two index keys. A small number of buckets will contain more than two keys.
If two index keys are mapped to the same hash bucket, there is a hash collision. A large number of hash collisions can have a performance impact on read operations.
During this phase, the user-issued Transact-SQL statements are executed. Rows are read from the tables, and new row versions are written to the database. The transaction is isolated from all other concurrent transactions. The transaction uses the snapshot of the memory-optimized tables that exists at the start of the transaction. Writes to the tables in this phase of the transaction are not yet visible to other transactions, with one exception: row updates and deletes are visible to update and delete operations in other transactions, in order to detect write conflicts.
During regular processing, a transaction can read rows written by other transactions that are in the validation or commit phase, but have not yet committed. The rows are visible because the logical end time of the transactions has been assigned at the start of the validation phase.
If a transaction reads such uncommitted rows, it will take a commit dependency on that transaction. This has two main implications:
- A transaction cannot commit until the transactions it depends on have committed. In other words, it cannot enter the commit phase, until all dependencies have cleared.
- In addition, result sets are not returned to the client until all dependencies have cleared. This prevents the client from observing uncommitted data.
During the validation phase, the system validates that the assumptions necessary for the requested transaction isolation level were true between the logical start and logical end of the transaction.
At the start of the validation phase, the transaction is assigned a logical end time. The row versions written in the database become visible to other transactions at the logical end time.
Repeatable Read Validation:
If the isolation level of the transaction is REPEATABLE READ or SERIALIZABLE, or if tables are accessed under REPEATABLE READ or SERIALIZABLE isolation (for more information, see the section on Isolation of Individual Operations in Transaction Isolation Levels), the system validates that the reads are repeatable. This means it validates that the versions of the rows read by the transaction are still valid row versions at the logical end time of the transaction.
Serializable validation is performed in two cases:
- If the isolation level of the transaction is SERIALIZABLE or tables are accessed under SERIALIZABLE isolation.
- If rows are inserted in a unique index, such as the index created for a PRIMARY KEY constraint. The system validates that no rows with the same key have been concurrently inserted.
The system validates that no phantom rows have been written to the database. The read operations performed by the transaction are evaluated to determine that no new rows were inserted in the scan ranges of these read operations.
Insertion of a key in a unique index includes an implicit read operation, to determine that the key is not a duplicate. Serializable validation for unique indexes ensures these indexes cannot have duplicates in case two transactions concurrently insert the same key.
If phantom rows are detected, the transaction fails to commit with error 41325 (“The current transaction failed to commit due to a serializable validation failure.”).
If validation succeeds and all transaction dependencies clear, the transaction enters the commit processing phase. During this phase the changes to durable tables are written to the log, and the log is written to disk, to ensure durability. Once the log record for the transaction has been written to disk, control is returned to the client.
All commit dependencies on this transaction are cleared, and all transactions that had been waiting for this transaction to commit can proceed.
- Cross-database transactions are not supported with memory-optimized tables. Every transaction that accesses memory-optimized tables cannot access more than one database, with the exception of read-write access to tempdb and read-only access to the system database master.
- Distributed transactions are not supported with memory-optimized tables. Distributed transactions started with BEGIN DISTRIBUTED TRANSACTION cannot access memory-optimized tables.
- Memory-optimized tables do not support locking. Explicit locks through locking hints (such as TABLOCK, XLOCK, ROWLOCK) are not supported with memory-optimized tables.
In-Memory OLTP Memory Estimation
The above calculations estimate your memory needs for the table as it currently exists. In addition to this memory, you need to estimate the growth of the table and provide sufficient memory to accommodate that growth. For example, if you anticipate 10% growth then you need to multiple the results from above by 1.1 to get the total memory needed for your table.
In-Memory Filegroup Configuration
You should consider creating multiple containers in the memory-optimized filegroup and distribute them on different drives to achieve more bandwidth to stream the data into memory.
When configuring storage, you must provide free disk space that is four times the size of durable memory-optimized tables. You must also ensure that your IO subsystem supports the required IOPS for your workload. If data and delta file pairs are populated at a given IOPS, you need 3 times that IOPS to account for storing and merge operations. You can add storage capacity and IOPS by adding one or more containers to the memory-optimized Filegroup.
Reference: SQL Server In-Memory Internals Event Slide Deck by Hamid J. Fard