In-Memory OLTP (Hekaton)

Overview

In-Memory OLTP (codename Hekaton) là tính năng được giới thiệu trong SQL Server 2014, cho phép lưu trữ tables trực tiếp trong RAM thay vì buffer pool truyền thống. Được thiết kế để xử lý workloads OLTP với throughput cực cao và latency thấp.

Tại sao In-Memory OLTP nhanh hơn?

Memory-Optimized Tables

Tạo Memory-Optimized Filegroup

Trước khi tạo memory-optimized tables, database phải có một memory-optimized filegroup:

-- Bước 1: Tạo database với memory-optimized filegroup
CREATE DATABASE InMemoryDemo
ON PRIMARY (NAME = 'InMemoryDemo_data', FILENAME = 'C:\Data\InMemoryDemo.mdf'),
FILEGROUP InMemory_FG CONTAINS MEMORY_OPTIMIZED_DATA
    (NAME = 'InMemory_FG_container', FILENAME = 'C:\Data\InMemory_FG_container')
LOG ON (NAME = 'InMemoryDemo_log', FILENAME = 'C:\Data\InMemoryDemo.ldf');

-- Bước 2 (nếu database đã tồn tại): Thêm filegroup vào database hiện có
ALTER DATABASE ExistingDB ADD FILEGROUP InMemory_FG CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE ExistingDB ADD FILE (
    NAME = 'InMemory_Container',
    FILENAME = 'C:\Data\InMemory_Container'
) TO FILEGROUP InMemory_FG;

Tạo Memory-Optimized Table

-- SCHEMA_AND_DATA: Data được persist xuống disk (durable)
CREATE TABLE dbo.ShoppingCart (
    CartID       INT NOT NULL,
    UserID       INT NOT NULL,
    ProductID    INT NOT NULL,
    Quantity     INT NOT NULL DEFAULT 1,
    AddedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_ShoppingCart PRIMARY KEY NONCLUSTERED (CartID),
    INDEX IX_ShoppingCart_UserID HASH (UserID) WITH (BUCKET_COUNT = 131072)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- SCHEMA_ONLY: Chỉ schema được persist, data mất sau restart (như temp table)
CREATE TABLE dbo.SessionCache (
    SessionKey   NVARCHAR(100) NOT NULL,
    SessionData  NVARCHAR(MAX) NULL,
    ExpiresAt    DATETIME2 NOT NULL,
    CONSTRAINT PK_SessionCache PRIMARY KEY NONCLUSTERED HASH (SessionKey)
        WITH (BUCKET_COUNT = 262144)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

DURABILITY Options

Restrictions của Memory-Optimized Tables

-- KHÔNG hỗ trợ:
-- 1. FOREIGN KEY constraint (SQL 2014-2016, được hỗ trợ từ SQL 2017+)
-- 2. CHECK constraint chứa subquery
-- 3. IDENTITY column với TRUNCATE TABLE
-- 4. Computed columns (SQL 2014-2016)
-- 5. NULL columns trong PRIMARY KEY
-- 6. Varchar(MAX), nvarchar(MAX) trong primary key
-- 7. TRUNCATE TABLE
-- 8. DDL không thể chạy trong transaction với memory-optimized tables

-- SQL 2019+ đã relaxed nhiều restrictions:
-- - Parallel query plans
-- - SELECT DISTINCT, UNION, GROUP BY với non-aggregate columns
-- - JOIN với non-memory-optimized tables

In-Memory Indexes

Hash Index

Hash Index dành cho equality lookups (WHERE column = value). Không hỗ trợ range scans hay ORDER BY.

-- Tạo Hash Index
CREATE TABLE dbo.Products (
    ProductID    INT NOT NULL,
    SKU          NVARCHAR(50) NOT NULL,
    CategoryID   INT NOT NULL,
    Price        DECIMAL(10,2) NOT NULL,
    -- Hash index cho equality lookup theo ProductID
    CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED HASH (ProductID)
        WITH (BUCKET_COUNT = 1048576),
    -- Hash index cho lookup theo SKU
    INDEX IX_Products_SKU HASH (SKU) WITH (BUCKET_COUNT = 131072)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

BUCKET_COUNT — quan trọng nhất khi thiết kế Hash Index:

• Quá nhỏ: Nhiều collisions, chậm hơn vì phải chain nhiều rows
• Quá lớn: Lãng phí memory (mỗi bucket = 8 bytes)
• Rule of thumb: 1-2x số distinct values dự kiến, làm tròn lên power of 2

-- Kiểm tra hash index utilization
SELECT 
    OBJECT_NAME(hs.object_id) AS table_name,
    i.name AS index_name,
    hs.total_bucket_count,
    hs.empty_bucket_count,
    hs.avg_chain_length,
    hs.max_chain_length,
    CAST(100.0 * hs.empty_bucket_count / hs.total_bucket_count AS DECIMAL(5,2)) AS empty_bucket_pct
FROM sys.dm_db_xtp_hash_index_stats hs
JOIN sys.indexes i ON hs.object_id = i.object_id AND hs.index_id = i.index_id;
-- avg_chain_length > 10 → BUCKET_COUNT quá nhỏ, cần tăng lên
-- empty_bucket_pct < 33% → BUCKET_COUNT quá nhỏ

Range Index (Nonclustered Memory-Optimized Index)

Range Index (Bw-tree) hỗ trợ range scans, ORDER BY, inequality predicates:

CREATE TABLE dbo.Orders (
    OrderID      INT NOT NULL,
    CustomerID   INT NOT NULL,
    OrderDate    DATETIME2 NOT NULL,
    TotalAmount  DECIMAL(15,2) NOT NULL,
    -- Range index: hỗ trợ WHERE OrderDate BETWEEN ... AND ...
    INDEX IX_Orders_OrderDate NONCLUSTERED (OrderDate DESC),
    -- Hash index: hỗ trợ WHERE CustomerID = @ID
    INDEX IX_Orders_CustomerID HASH (CustomerID) WITH (BUCKET_COUNT = 65536),
    CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderID)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Natively Compiled Stored Procedures

Natively Compiled Stored Procedures được compile thành machine code (native DLL) khi tạo, thay vì interpret T-SQL mỗi lần chạy.

Tạo Native Procedure

CREATE OR ALTER PROCEDURE dbo.usp_AddToCart
    @CartID   INT,
    @UserID   INT,
    @ProductID INT,
    @Quantity INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    -- Chỉ có thể access memory-optimized tables
    IF EXISTS (SELECT 1 FROM dbo.ShoppingCart 
               WHERE CartID = @CartID AND ProductID = @ProductID)
    BEGIN
        UPDATE dbo.ShoppingCart
        SET Quantity = Quantity + @Quantity
        WHERE CartID = @CartID AND ProductID = @ProductID;
    END
    ELSE
    BEGIN
        INSERT INTO dbo.ShoppingCart (CartID, UserID, ProductID, Quantity)
        VALUES (@CartID, @UserID, @ProductID, @Quantity);
    END
END;

Restrictions của Native Procedures

-- KHÔNG hỗ trợ trong Native Procedures (SQL 2014-2016):
-- - TRY...CATCH
-- - Dynamic SQL (EXEC sp_executesql)
-- - Cursors
-- - SELECT INTO
-- - Subqueries trong nhiều contexts
-- - OUTER JOIN (SQL 2014)
-- - OR điều kiện (SQL 2014)
-- - Window functions (SQL 2014)
-- - CASE với subqueries
-- - String functions: LEN, SUBSTRING có giới hạn

-- SQL 2016+ đã mở rộng nhiều hơn
-- SQL 2019+ gần như full T-SQL surface area

BEGIN ATOMIC Block

Native procedures phải dùng BEGIN ATOMIC để đảm bảo atomicity:

-- BEGIN ATOMIC yêu cầu:
-- 1. TRANSACTION ISOLATION LEVEL: SNAPSHOT hoặc REPEATABLE READ hoặc SERIALIZABLE
-- 2. LANGUAGE
-- Không cần BEGIN TRAN/COMMIT TRAN → tự động atomic
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english',
    DELAYED_DURABILITY = ON  -- Optional: buffered logging cho performance
)
    -- T-SQL statements
END;

Performance Characteristics

-- Xem native procedure DLL location
SELECT 
    OBJECT_NAME(ps.object_id) AS proc_name,
    ps.cached_time,
    ps.last_execution_time,
    ps.execution_count,
    ps.total_worker_time / 1000 AS total_cpu_ms,
    ps.total_worker_time / NULLIF(ps.execution_count, 0) / 1000 AS avg_cpu_ms
FROM sys.dm_exec_procedure_stats ps
WHERE OBJECTPROPERTY(ps.object_id, 'IsNativelyCompiled') = 1
ORDER BY ps.total_worker_time DESC;

Memory-Optimized Table Types

Memory-Optimized Table Variables dùng như table variables nhưng được lưu trong RAM, tránh tempdb contention:

-- Tạo memory-optimized table type
CREATE TYPE dbo.OrderItemList AS TABLE (
    ProductID    INT NOT NULL,
    Quantity     INT NOT NULL,
    UnitPrice    DECIMAL(10,2) NOT NULL,
    INDEX IX_ProductID HASH (ProductID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);

-- Dùng trong stored procedure
CREATE OR ALTER PROCEDURE dbo.ProcessBulkOrder
    @OrderItems dbo.OrderItemList READONLY  -- Phải là READONLY
AS
BEGIN
    -- Process items from in-memory table variable
    INSERT INTO dbo.OrderDetails (ProductID, Quantity, UnitPrice)
    SELECT ProductID, Quantity, UnitPrice
    FROM @OrderItems;
END;

-- Gọi procedure
DECLARE @Items dbo.OrderItemList;
INSERT INTO @Items VALUES (1, 5, 29.99), (2, 2, 49.99);
EXEC dbo.ProcessBulkOrder @OrderItems = @Items;

Migration Considerations

Xác định Candidates cho In-Memory OLTP

-- SQL Server cung cấp AMR (Analysis, Migrate, Report) tool
-- Hoặc dùng query để tìm hot tables
SELECT TOP 20
    OBJECT_NAME(ios.object_id) AS table_name,
    ios.row_lock_count + ios.page_lock_count AS total_locks,
    ios.row_lock_wait_count + ios.page_lock_wait_count AS total_lock_waits,
    ios.row_lock_wait_in_ms + ios.page_lock_wait_in_ms AS total_lock_wait_ms,
    ios.page_latch_wait_count,
    ios.page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.tables t ON ios.object_id = t.object_id
WHERE t.is_memory_optimized = 0  -- Chỉ disk-based tables
ORDER BY total_lock_waits DESC;

Surface Area Restrictions Check

-- Dùng stored procedure để check compatibility
-- (SQL Server Management Studio có built-in tool)
EXEC sys.sp_xtp_bind_db_resource_pool 
    @database_name = N'YourDatabase',
    @pool_name = N'InMemoryPool';

-- Kiểm tra tables có unsupported features không
SELECT 
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('text', 'ntext', 'image', 'xml', 'geography', 'geometry')
    -- Các data types này không hỗ trợ trong memory-optimized tables
ORDER BY t.name;

Performance Gains

Eliminating Lock/Latch Contention

In-Memory OLTP dùng Multi-Version Concurrency Control (MVCC):

• Writers không block readers và ngược lại
• No row locks, page locks, or table locks cho data access
• Conflict detection dùng optimistic concurrency → write conflicts được detect lúc commit
• Không có buffer pool latches → loại bỏ một nguồn contention lớn

Logging Optimization

-- Traditional tables: log full before/after page images
-- Memory-optimized tables: log chỉ logical operations (insert/delete pairs)
-- Không có update-in-place → mọi update = delete old version + insert new version

-- Delayed Durability: buffer log writes cho throughput cao hơn
-- (risk: data loss trong khoảng buffer window nếu crash)
ALTER DATABASE YourDB SET DELAYED_DURABILITY = FORCED;  -- Tất cả transactions

-- Hoặc per-procedure
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english',
    DELAYED_DURABILITY = ON
)

Monitoring In-Memory OLTP

sys.dm_db_xtp_* DMVs

-- Xem memory usage của memory-optimized objects
SELECT 
    OBJECT_NAME(object_id) AS table_name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb,
    memory_allocated_for_indexes_kb,
    memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
ORDER BY memory_used_by_table_kb DESC;

-- Xem XTP (In-Memory OLTP) runtime stats
SELECT * FROM sys.dm_xtp_system_memory_consumers;

-- Transactions conflict stats
SELECT 
    object_id,
    OBJECT_NAME(object_id) AS table_name,
    scans_started,
    read_committed_scans_started,
    write_conflicts,
    unique_constraint_violations
FROM sys.dm_db_xtp_object_stats
ORDER BY write_conflicts DESC;

-- Xem tất cả memory-optimized tables và metadata
SELECT 
    name,
    is_memory_optimized,
    durability,
    durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;

XTP-Specific Performance Counters

-- Dùng DMV để theo dõi
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%XTP%'
ORDER BY object_name, counter_name;

-- Các counters quan trọng:
-- XTP Transactions/sec
-- XTP Garbage Collections/sec  
-- XTP Checkpoint Files Merged/sec
-- XTP Log bytes written/sec

Buffer Pool Bypass

Data trong memory-optimized tables KHÔNG được lưu trong buffer pool:

• Disk-based tables: data pages → buffer pool (RAM cache) ↔ disk
• Memory-optimized tables: data chỉ tồn tại trong RAM, không qua buffer pool
• Durability: SQL Server ghi checkpoint files (data/delta files) xuống disk để recover sau restart
• Recovery: khi SQL Server restart, load data từ checkpoint files + transaction log vào RAM

-- Xem checkpoint file pairs (data + delta files)
SELECT 
    container_id,
    state_desc,
    file_type_desc,
    internal_storage_slot,
    checkpoint_pair_file_id,
    file_size_in_bytes / 1024 / 1024 AS size_mb,
    file_used_size_in_bytes / 1024 / 1024 AS used_mb
FROM sys.dm_db_xtp_checkpoint_files
ORDER BY container_id, file_type_desc;

When to Use In-Memory OLTP

Ideal Use Cases

Khi KHÔNG nên dùng

• Table có nhiều full-table scans (không phải OLTP pattern)
• Data lớn hơn available RAM
• Schema thay đổi thường xuyên (DDL hạn chế)
• Cần complex SQL features chưa được hỗ trợ
• Ad-hoc reporting queries (dùng columnstore index thay thế)

Q&A theo Cấp Độ

Junior Level

Q: In-Memory OLTP (Hekaton) là gì? Tại sao nó nhanh hơn?

A: In-Memory OLTP là tính năng từ SQL Server 2014, lưu toàn bộ data trong RAM thay vì disk. Nhanh hơn vì:

1. Không cần I/O disk cho data access
2. Loại bỏ locking bằng MVCC (writers không block readers)
3. Không có buffer pool latches
4. Native compiled procedures chạy thẳng machine code

Q: SCHEMA_AND_DATA vs SCHEMA_ONLY durability là gì?

A: SCHEMA_AND_DATA: data được ghi xuống disk (checkpoint + log), không mất sau restart — dùng cho permanent data. SCHEMA_ONLY: chỉ schema được lưu, data mất sau SQL Server restart — dùng cho session state, temp data không cần persist.

Q: Hash Index và Range Index trong In-Memory OLTP khác nhau thế nào?

A: Hash Index dùng hash function để lookup equality (WHERE col = value) trong O(1) time, không hỗ trợ range hay ORDER BY. Range Index (Bw-tree) hỗ trợ equality, range scans, ORDER BY nhưng chậm hơn Hash Index cho point lookups. BUCKET_COUNT của Hash Index phải được chọn cẩn thận.

Mid Level

Q: Làm sao chọn BUCKET_COUNT phù hợp cho Hash Index?

A: Rule of thumb:

• Đặt bucket_count = 1-2x số distinct values của indexed column (không phải tổng số rows)
• Làm tròn lên power of 2 gần nhất (4096, 8192, 16384, 131072, 262144…)
• Monitor bằng sys.dm_db_xtp_hash_index_stats: nếu avg_chain_length > 10 → tăng bucket_count
• Nếu empty_bucket_pct < 33% → tăng bucket_count
• Nếu > 95% empty → giảm bucket_count để tiết kiệm memory

Q: Giải thích BEGIN ATOMIC trong Native Compiled Procedures?

A: BEGIN ATOMIC là bắt buộc trong native procedures, đảm bảo khối code chạy trong một unit (atomic). Khác với disk-based procedures:

• Không cần BEGIN TRAN/COMMIT TRAN — tự động atomic
• Phải chỉ định TRANSACTION ISOLATION LEVEL (SNAPSHOT thường dùng)
• Phải chỉ định LANGUAGE
• Có thể dùng DELAYED_DURABILITY = ON để buffer log writes

Senior Level

Q: Giải thích cơ chế MVCC trong In-Memory OLTP và tại sao nó tốt hơn locking truyền thống cho OLTP?

A: In-Memory OLTP dùng optimistic MVCC:

• Mỗi row có begin_ts và end_ts (transaction timestamps)
• Readers luôn đọc phiên bản row tương ứng với thời điểm transaction bắt đầu → không chờ writers
• Writers tạo new version của row (không modify in-place), set end_ts của version cũ = commit timestamp
• Write-write conflict: nếu 2 transactions cùng update 1 row, transaction thứ hai bị abort
• Garbage collection tự động dọn các row versions không còn cần
• Tốt hơn locking: loại bỏ lock wait time, deadlock, lock escalation hoàn toàn

Q: Làm sao migrate một disk-based table sang In-Memory OLTP với zero downtime?

A: Quy trình migration an toàn:

1. Analysis: Dùng AMR tool hoặc DMVs để check surface area restrictions
2. Create memory-optimized equivalent: Tạo table mới với MEMORY_OPTIMIZED = ON
3. Dual-write pattern: Application write vào cả 2 tables trong transition period
4. Backfill: Copy data từ disk-based sang memory-optimized
5. Switch reads: Chuyển reads sang memory-optimized table
6. Stop dual-write: Sau khi verified, bỏ writes vào disk-based table
7. Cleanup: Drop disk-based table

Thay thế cho stored procedures: tạo native procedure mới, rename hoặc dùng sp_rename để swap atomically.

Q: In-Memory OLTP ảnh hưởng thế nào đến Always On Availability Groups?

A:

• Memory-optimized tables được replicate đầy đủ sang secondary replicas
• Checkpoint files được stream sang secondary như disk-based data files
• Transaction log entries cho memory-optimized operations được gửi sang secondary
• Recovery trên secondary cũng load data từ checkpoint + log vào RAM
• Caveats: secondary cần đủ RAM để chứa cả memory-optimized data
• SCHEMA_ONLY tables: data không replicate (chỉ schema) → secondary sẽ có empty tables sau failover
• Recommend: test failover thoroughly để đảm bảo memory requirements được đáp ứng trên secondary