In-Memory OLTP (also known as Hekaton) is introduced by SQL Server 2014 to improve the performance of OLTP workloads. In-Memory OLTP is optimized for Online Transaction Processing (OLTP) and integrated into SQL Server Database Engine. It lets You move tables from your traditional, disk based, storage, which suffer I/O latency, into the memory directly. It features two new structures which are Memory-Optimized Tables, and Natively-Compiled Stored Procedure.
Memory-optimized tables are defined as C structs, compiled as dynamic-link libraries (DLLs), store their data into memory and do not use locks to enforce concurrency isolation.The main features of memory-optimized tables:
- Are defined as C structs, compiled into DLLs, and loaded into memory.
- Can persist data to disk or can be nondurable.
- Do not apply any locking during transactional data modifications.
- Can be indexed by using hash or range indexes.
- Can coexist with disk-based tables in the same database.
- Can be queried by using Transact-SQL through interop services provided by SQL Server query processor.
- Do not support some data types, including text, image, and nvarchar(max).
- Do not support identity columns or foreign key constraints.
Best-Suited scenarios for Memory-Optimized Tables:
- Multiple concurrent queries modify large numbers of rows in a transaction.
- A table contains “hot” pages. A table that contains a clustered index on an incrementing key value will inherently suffer from concurrency issues because all insert transactions occur in the last page of the index.
Memory-optimized table is defined as non-blocking multi-version optimistic concurrency control which means SQL Server uses an optimistic concurrency row-versioning mechanism to track changes to rows and eliminates both locks and latches.Although data modifications in memory-optimized tables occur extremely fast, concurrency conflict errors are possible that you should therefore consider the design to handle conflict errors that can occur in memory-optimized tables such as:
- Write conflicts occur when an attempt is made to update or delete a record that has been updated since the transaction began.
- Repeatable read validation failures occur when a row that the transaction has read has changed since the transaction began.
- Serializable validation failures occur when a new row is inserted into the range of rows that the transaction accesses while it is still in progress.
- Commit dependency failures occur when a transaction has a dependency on another transaction that has failed to commit.
Natively Compiled Stored Procedure
In traditional structure of SQL Server, codes are not compiled in advance of execution. When a disk-based stored procedure is executed for the first time, SQL Server compiles the store procedure. Natively compiled stored procedures are stored procedures that are written in Transact-SQL, but are then compiled into native code and stores the code as a DLL when they are created rather than when they are executed which improves performance.Another difference between interpreted (disk-based) stored procedures and natively compiled stored procedures is compiling natively compiled stored procedures can detects many error conditions such as type conversion, arithmetic overflow and some divide-by-zero conditions at create time and cause creation to fail, while such errors do not result in failure of the creation of the stored procedure, but the executions will fail.Natively compiled stored procedures only have access to memory-optimized tables and disk-based tables are not accessible in natively compiled stored procedures.
In-Memory OLTP Limitations
The bellow table demonstrate main limitations of in-memory OLTP:
DATABASE SNAPSHOT Not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup
DBCC CHECKDB Skips the memory-optimized tables
DBCC CHECKTABLE Will fail for memory-optimized tables
Replication Not supported with memory-optimized tables
TRUNCATE TABLE Not supported for memory-optimized tables
Filtered index Not supported with memory-optimized tables
Cursors Not supported on or in natively compiled stored procedures
Multi-row INSERT … VALUES Cannot use in a natively compiled stored procedure
temporary tables Cannot use in a natively compiled stored procedure
Memory Optimization Advisor
Memory Optimization Advisor can review your existing disk-based tables through a checklist to determine that your environment and the specific tables are suitable to be ported to in-memory OLTP.To use the Memory Optimization Advisor, in Object Explorer, right-click on the table you want to convert and select the Memory Optimization Advisor.Memory Optimization Advisor steps:
- Migration Validation reports on any features of disk-based tables that are not supported in memory-optimized tables.
- Migration Warnings contains the list of issues that do not prevent a disk-based table from being migrated to a memory-optimized table, or stop the table from functioning once it is converted, but may cause unexpected behaviour of any other associated objects, such as stored procedures or CLR functions, after migration.
- Migration Options let you specify options for the migration to in-memory OLTP as follows:
- Memory-optimized filegroup
- Logical file name and file path
- Rename the original unmigrated disk-based table
- Estimated current memory cost (MB)
- Copy table data to the new memory-optimized table
- Migrate this table to a memory-optimized table with no data durability
- Index Creation. for a durable memory-optimized table, it is required at least one primary key. You can also specify whether the index should be a hash index or not. Hash indexes are better for point lookups. Non-hash indexes are better for range lookups.
- Index Migration gives you the same options as primary key migration for each of the indexes on the table.
- summary lists the options that you have specified in the previous steps and allows you to migrate the table, or to generate a Transact-SQL script to migrate the table at subsequent time.
Native Compilation Advisor
The Native Compilation Advisor works the same way as the Memory Optimization Advisor. In Object Explorer, right click the stored procedure you want to convert, and select Native Compilation Advisor.Native Compilation Advisor Elements:
- Stored Procedure Validation validate the stored procedure for any construct’s compatibilities with native compilation.
- Unsupported Transact-SQL Elements displays details if there are any constructs that are not compatible with native compilation.
Indexes in Memory-Optimized Tables
All memory-optimized tables must have at least one index, which can be the index that was created for the primary key. these are the indexes which store the data rows and connect them together. Indexes on memory-optimized tables behave differently from traditional disk-based indexes. Data rows are not stored on pages, so there are no partitions, allocation units or extents, therefore there is no fragmentation, so they do not need fillfactor. Changes on indexes are never written to disk, and only data rows and changes to data are written to transaction log. They are also rebuilt when the database is brought back online.Memory-optimized tables support two types of index:
- Hash Indexes. These indexes contain an array of pointers and each element called a hash bucket. Each bucket is 8 bytes and used to store the memory address of each link list of key entries. The hash function applies an algorithm to the indexed key values to determine the bucket in which the row is stored.
- Memory-Optimized Nonclustered Indexes. These indexes use a latch-free variation of a binary tree (B-Tree) structure, called a “BW-Tree,” to organize the rows based on key values.
AMR in Memory-Optimized Tables
SQL Server Management Studio in SQL Server 2014 includes the AMR (Analysis, Migration, and Reporting) tool to analyze and evaluate OLTP workloads and determine if tables and stored procedures are potential to convert to in-memory OLTP. The AMR tool uses the Data Collector feature to collect workload statistics in a management data warehouse (MDW). A predefined data collection set, named Transaction Performance Collection Sets, gathers the required data for analysis.After the data has been collected, a Transaction Performance Analysis Overview report provides a matrix that shows the performance gains on one axis and the migration effort on another axis, as shown on the below.
Please stay tuned for next blog to learn how to query memory-optimized tables.