With a view to leverage the changing hardware landscape, Microsoft has for the first time introduced the concept of In-Memory OLTP, popularly known as Hekaton. This will make OLTP transactions faster than ever.
Microsoft SQL Server 2014 introduced memory optimized database technology for optimizing the performance of OLTP workload. It is a new database engine component, fully integrated into the existing SQL Server engine and optimised for very high OLTP performance. It is designed to take advantage of the modern hardware. We know that the number of cores and the size of RAM has increased phenomenally. Servers with terabytes of RAM are available for even less than $100K. In order to leverage this changing hardware landscape, Microsoft has for the first time introduced the concept of In-Memory OLTP, popularly known as Hekaton. This will make OLTP transactions faster than ever.
In-Memory OLTP Internals Overview
The original design of the SQL Server engine assumed that the main memory was very expensive and so the data needs to reside on the disk except when it is actually needed for processing. Traditional SQL Server Engine is optimised for disk-based storage, for reading 8KB data PAGE into memory for processing and writing back out to disk after data modification. It generates a lot of IO, latching and locking that incurs a high latency cost.
In-Memory OLTP allows you to move data into the memory resident tables while keeping other data in the traditional, disk-based tables. Rows in the table are read from and written to memory. A second copy of the table data is maintained on a disk under the memory optimized Filegroup, but only for durability purpose.
Difference between disk-based & memory-optimised tables
The main difference between disk-based and memory-optimised tables is in the underlying storage structure. Disk-based tables are optimised for Block-addressable disk storage (8K PAGE), whereas the memory-optimized for Bytes-addressable memory storage. Another significant difference is that the table schema are natively complied. SQL Server In-Memory OLTP compiler compile structure of the table into a Dynamic Link Library (DLL) - a set of native language runtime.
SQL Server 2014 Engine architecture layout including the In-Memory OLTP components.
Source: Inside the SQL Server 2014 Hekaton Engine By Kalen Delaney
Let’s start using In-Memory OLTP feature.
Before start using In-Memory OLT, please verify whether your SQL instance supports it or not by checking server property “IsXTPSupported”. Except for this server property, there are few important notes on using In-Memory OLPT. Refer the following link for the same:
1. Enable User Database for In-Memory OLTP.
Any database that will store memory-optimized tables must have a MEMORY_OPTIMIZED_DATA filegroup. This filegroup will be used to guarantee durability of the memory-resident data in the event of a server crash or restart. During the crash recovery phase in the server start-up, the data is recovered from this filegroup and loaded back into memory. It is also possible to add a MEMORY_OPTIMIZED_DATA file group to an existing database using the ALTER DATABASE cmd.
-- Add memory-optimized filegroup and a container in existing database.
ALTER DATABASE HKDB ADD FILEGROUP [HKDB] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE HKDB ADD FILE (name='HKDB_mod_dir',
FILENAME='D:\sql\HKDATA\HKDB_dir') TO FILEGROUP [HKDB]
Memory Optimized database can support much, though not all, of the SQL Server feature set. Like SQL Server Management Studio works seamlessly with memory based tables, Filegroups and natively complied procedures. Database Backup and Restore are almost full supported (differential backup unsupported), other high availability solutions like Log-shipping, AlwaysOn, are supported but database mirroring and replication are unsupported. A memory optimized table can be a subscriber in transactional replication but can’t be a publisher.
2. Create Memory Optimized Table.
The syntax for creating the memory-optimized tables is almost identical to the syntax for creating the disk-based tables, however, with few required extensions, and few restrictions on the data types, indexes, constraints and other options, that the memory-optimized tables can support.
MEMORY_OPTIMIZED=ON specify that a table is a memory-optimized table.
DURABILITY = SCHEMA_AND_DATA defines that table’s contents are persisted on disk and will not be lost during a server crash.
DURABILITY=SCHEMA_ONLY means that the contents of the table exist only in memory, and are lost on the server restart.
-- Create a durable (data will be persisted) memory-optimized table
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=10000),
CreatedDate DATETIME2 NOT NULL, TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
It supports only two type of indexes… Hash and Range index. Every memory-optimized table must have at least one index by specifying a Primary Key. We can create up to eight non-clustered indexes on the memory-optimized tables.
3. Load Data into Memory-optimised table.
We can load data into the tables in various ways, including INSERT .. SELECT from an existing disk-based table and BCP.
-- Load Data in Memory optimized tables
INSERT dbo.ShoppingCart (UserId, CreatedDate, TotalPrice) VALUES (8798, GETDATE(), NULL)
INSERT dbo.ShoppingCart (UserId, CreatedDate, TotalPrice) VALUES (23, GETDATE (), 45.4)
INSERT dbo.ShoppingCart (UserId, CreatedDate, TotalPrice) VALUES (80, GETDATE (), NULL)
INSERT dbo.ShoppingCart (UserId, CreatedDate, TotalPrice) VALUES (342, GETDATE (), 65.4)
4. Update Statistics.
Memory-optimized tables do not support auto_update_statistics, thus statistics will need to be updated manually. We can use UPDATE STATISTICS to update statistics for individual tables, or sp_updatestats for all the tables in a database.
-- Update statistics on memory optimized tables
UPDATE STATISTICS dbo.ShoppingCart WITH FULLSCAN, NORECOMPUTE
5. Access memory-optimized tables by Interpreted T-SQL
In-memory OLTP engine supports a truly optimistic concurrency model, called Multi-version Concurrency Control (MVCC) model. It is based on the in-memory row versioning and fully supports transaction property ACID. Queries on memory-optimised tables will benefit from the latch and lock free data structures and more efficient data access.
-- SELECT/UPDATE/DELETE statements in explicit transactions
UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=65.84
SELECT * FROM dbo.ShoppingCart
With only a few exceptions, you can access memory-optimized tables using any T-SQL query or DML operation (SELECT, INSERT, UPDATE, or DELETE), ad hoc batches, and SQL modules such as stored procedures, table-value functions, triggers, and views. If a transaction can reference both memory-optimized and disk-based tables, then we refer to it as cross-container transaction.
6. Natively Complied Stored Procedure
At stored procedure creation time, which are marked with NATIVE_COMPILATION, In-Memory OLTP compiler converts interpreted T-SQL, query plans and expressions into a native code (DLL). Natively compiled stored procedures have a faster execution than the normal ones. Also, the Native complied stored procedures can only access Memory-optimised tables but normal (Interpreted T-SQL) stored procedures can access both type of tables (Disk-Based and Memory-Based).
-- Create Natively Complied Stored Procedure.
CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
DECLARE @ShoppingCartId int = @StartId
WHILE @ShoppingCartId < @StartId + @InsertCount
INSERT INTO dbo.ShoppingCart VALUES (@ShoppingCartId, 1, '2013-01-01T00:00:00', NULL)
SET @ShoppingCartId += 1
Natively complied stored procedure’s execution plan is compiled into the DLL every time SQL Server or database restarts. However, the SQL Server 2014 does not support the automatic recompilation of natively complied stored procedures if there is any change in the table data and statistics.
SQL Server 2014 In-Memory OLTP “Hekaton” is a new and game changing OLTP database engine coupled with existing SQL Server engine features. It is optimized for main large memory and many-core. It gives us an entirely a new way to store and access relational data, using lock and latch free data structure that allows completely non-blocking data processing operation.
Reference link on the topic..