In-Memory OLTP  (A.K.A. Hekaton) is a specialized, memory-optimized relational data management engine and native stored procedure compiler, integrated into SQL Server.

Microsoft designed In-Memory OLTP to handle the most demanding OLTP workloads. To accomplish this, In-Memory OLTP introduces two fundamentally new concepts: memory-optimized tables and natively compiled stored procedures.

Data in memory-optimized tables resides in memory, and while transactions are logged for recovery, there is no paging to disk like traditional disk-based tables. Memory-optimized tables provide highly optimized data access structures using hash and non-clustered ordered indexes. The internal structures of these indexes are different from traditional B-trees and provide a new, high performance way of accessing in-memory data. Data access and transaction isolation are handled through a multi-version, concurrency control mechanism that provides an optimistic, non-blocking implementation. While implemented differently from traditional RDBMS, In-Memory OLTP still provides ACID compliance.

In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for transaction processing. 

In Memory OLTP significantly improve OLTP database application performance by improving throughput and reducing latency for transactional processing.

System Requirement for use of In Memory OLTP in SQL Server 2014

Hardware requirement

In-Memory OLTP uses memory and disk in different ways than traditional disk-based tables. The  performance improvement you will see with In-Memory OLTP depends the hardware you use.

CPU 

In-Memory OLTP does not require a high-end server to support a high-throughput OLTP workload. Recommendation is to use  mid-range server with 2 CPU sockets. Due to the increased throughput enabled by In-Memory OLTP, 2 sockets are likely going to be enough for business needs.

Turn hyper-threading ON with in-memory OLTP. With some OLTP workloads  have seen performance gains of up to 40% when using hyper-threading.

Memory 

All memory-optimized tables reside fully in memory. Therefore, you must have enough physical memory for the tables themselves and to sustain the workload running against the database – how much memory you actually need really depends on the workload, but as a starting point you will probably want enough available memory for about 2X the data size. You will also need enough memory for the buffer pool in case the workload also operates on traditional disk-based tables.

To find out the memory use by memory optimized table run the below Query

select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats 

It is important to keep in mind when you use in-memory OLTP that your whole database does not need to fit in memory. You can have a multi-Terabyte database and still benefit from in-memory OLTP, as long as the size of your hot data (i.e., the memory-optimized tables) does not exceed 256GB.

Non-durable memory-optimized tables (NDTs), i.e., memory-optimized tables with DURABILITY=SCHEMA_ONLY are not persisted on disk. Although NDTs are not limited by the number of checkpoint files, only 256GB is supported.

Log drive 

Log records pertaining to memory-optimized tables are written to the database transaction log, along with the other SQL Server log records.

It is always important to put the log file on a drive that has low latency, such that transactions do not need to wait too long, and to prevent contention on log IO. Your system will run as fast as your slowest component. You need to ensure that, when running In-Memory OLTP, your log IO device does not become a bottleneck. Recommendation is to use a storage device with low latency, at least SSD.

Note that memory-optimized tables use less log bandwidth than disk-based tables, as they do not log index operations and do not log UNDO records. This can help to relieve log IO contention.

Data drive 

Persistence of memory-optimized tables using checkpoint files uses streaming IO. Therefore, these files do not need a drive with low latency or fast random IO. Instead, the main factor for these drives is the speed of sequential IO and bandwidth of the host bus adapter (HBA). Thus, you don’t need SSDs for checkpoint files; you can place them on high performance spindles (e.g., SAS), as long as their sequential IO speed meets your requirements.

The biggest factor in determining the speed requirement is your RTO [Recovery Time Objective] on server restart. During database recovery, all data in the memory-optimized tables needs to be read from disk, into memory. Database recovery happens at the sequential read speed of your IO subsystem; disk is the bottleneck.

To meet strict RTO requirements Recommendation is to spread the checkpoint files over multiple disks, by adding multiple containers to the MEMORY_OPTIMIZED_DATA filegroup.

SQL Server supports parallel load of checkpoint files from multiple drives – recovery happens at the aggregate speed of the drives.

Requirement Summary

• 64-bit Enterprise, Developer, or Evaluation edition of SQL Server 2014.
• 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
• If you have disk-based tables in the database, you need to provide enough memory for the buffer pool and query processing on those tables.
• Free disk space for that is two times the size of your durable memory-optimized tables.
• A processor needs to support the instruction cmpxchg16b to use In-Memory OLTP. All modern 64-bit processors support cmpxchg16b.

• The total in-memory size of all durable tables in a database should not exceed 250 GB.
• This release of In-Memory OLTP is targeted to perform optimally on systems with 2 or 4 sockets and fewer than 60 cores.
• Checkpoint files must not be manually deleted. SQL Server automatically performs garbage collection on unneeded checkpoint files.
• If you create one or more databases with memory-optimized tables, you should enable Instant File Initialization (grant the SQL Server service startup account the SE_MANAGE_VOLUME_NAME user right) for the SQL Server instance. Without Instant File Initialization, memory-optimized storage files (data and delta files) will be initialized upon creation, which can have negative impact on the performance of your workload.

In The Next Post , I will show you the demo of Performance improvement by using In Memory OLTP  and will explain you why we get the gain in performance.

Till then  enjoy ….. and if you like the post or have something to say than do comment so..