In-memory OLTP or Hekaton these 2 terms can be used interchangeably but Microsoft officially announce this new technology name as “In-memory OLTP”. “Hekaton” is Greek word for “hundreds,” and it was given this name for its ability to speed up database function 100x (possibly).  It certainly increases application speed by 10x and nearly 50x for new, optimized applications but it depends of several parameters.

Hekaton works by providing in-application memory storage for the most often used tables in SQL Server, but don’t confuse this is entirely different from an older technology you might heard of DBCC PINTABLE ( database_id , table_id ).

DBCC PINTABLE does not cause the table to be read into memory. As the pages from the table are read into the buffer cache by normal Transact-SQL statements, they are marked as pinned pages. SQL Server does not flush pinned pages when it needs space to read in a new page. SQL Server still logs updates to the page and, if necessary, writes the updated page back to disk. SQL Server does, however, keep a copy of the page available in the buffer cache until the table is unpinned with the DBCC UNPINTABLE statement.

DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read, but still the internal structure will remain page based.

Hence if the internal structure is page based then definitely there will be locking, latching and logging, also they use the same index structure which also require locking and latching.

Although the feature of DBCC PINTABLE is good and provide performance enhancement but it has some limitations like if a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table is larger than the buffer cache is pinned, it can fill the entire buffer cache. In that case a sysadmin must shut down SQL Server, restart SQL Server, and then unpin the table. Pinning too many tables can cause the same problems as pinning a table larger than the buffer cache.

 

 

In SQL 2014 “In-Memory OLTP” it entirely different or we can say also say that it’s an enhancement of previous technology DBCC PINTABLE. Tables in In-Memory OLTP are stored in entirely different way from disk based tables, they use entirely different data and index structure because this feature did not store data on PAGES and separate index mechanism, hence removing the need to latching and can solve the problem of latch contention mostly happens in case of last page insert.

  

You can check this problem via DMV’s and investigate the issue from below queries.   Select * From sys.dm_os_waiting_tasks  

Select wt.*, st.text
From sys.dm_os_waiting_tasks wt
left join sys.dm_exec_requests er
    on er.session_id = wt.session_id
outer apply fn_get_sql(er.sql_handle) st
where wt.resource_description is not null

 

Now as In-Memory OLTP works on a new structure as mentioned above we have below advantages. Elimination of disk reads entirely by always residing in memory

• Hash indexing (more efficient than on-disk table b-tree indexes)
• Elimination of locking & latching mechanisms used in on-disk tables
• Reduction in “blocking” via improved multi-version concurrency model

In-Memory OLTP Architecture

As you can see from the above diagram the tables and indexes are stored in a memory in different way, there is no buffer pool like conventional architecture also as there is no 8KB page based data structure MS is using stream based storage, and only files are appended to store the durable data.

Hence the major difference is that memory optimized table do not require pages to be read form the disk, all the data itself stored in memory all the time. A set of checkpoint files which are only used for recovery purpose is created on the file system file group to keep track of the changes of data, and the checkpoint files append-only. Memory optimized table uses the same transaction log that is used or the operation on disk based tables and is stored on disk, to ensure that in case of system crash the rows of data in the memory optimized table and be recreated from the checkpoint files and the transaction log.

Here you also have an option of Non-Durable table as well, in this option only table schema will be durable not data, so these tables will be re-created once the SQL start after a crash without data.

Indexes in In-Memory OLTP is also different they are not stored in a normal B Tree structure in fact they support HASH indexes to search records. Every memory optimized table must have at-least one Hash Index because it is the index that combine all rows into a single table. Indexes for memory optimized table are never stored on disk in-fact they are stored on memory and recreated everyone the SQL restart and data is inserted into the memory.

Bellow you can see there are 3 disk based tables T1, T2, and T3 with file-group on disk, but once we once we want some tables to be in memory that there will be a new space in

SQL memory and in addition there will be a new type of file group for stream based storage to persist copy of data to ensure data can be recovered in case of crash. Now consider a scenario we want to move some disk based tables to memory then we have to first drop them and recreate them with special syntax and the situation will look like mentioned below, as you can see there is a separate fie group called memory optimized file group and all DML operations are logged in conventional log file and as you can also see indexes and data exists in memory itself, note that there will no copy of indexes on disk.

Natively Complied Stored Procedure

In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. Native compilation allows faster data access and more efficient query execution, than interpreted (traditional) Transact-SQL

Native compilation refers to the process of converting programming constructs to native code, consisting of processor instructions that are executed directly by the CPU, without the need for further compilation or interpretation.

In-Memory OLTP compiles Transact-SQL constructs to native code for fast runtime data access and query execution. Native compilation of tables and stored procedures produce DLLs that are loaded into the SQL Server process.

In-Memory OLTP compiles both memory-optimized tables and natively compiled stored procedures to native DLLs when they are created. In addition, the DLLs are recompiled after a database or server restart. The information necessary to recreate the DLLs is stored in the database metadata.

Natively compiled stored procedure is a way to perform the same operation with fewer instruction because you can see that the CPU clock rate is stalled at a point. Hence it’s a way to perform the same operation with lesser instruction like there is a task which require 1000 instruction to complete with conventional architecture, but the same task can be done with 300 instruction with natively compiled stored procedure.

.

Hence you will get much performance gains in many aspects like the storage engine here itself is lock and latch free, hence there will be no locking and latching and in case your application is having performance bottle neck because of latch contention then you can use this feature of In-Memory OLTP to remove latch contention and you will see great result once you move some suffering tables in memory. Natively compiled stored procedure will give to 10-30x benefit by improving execution time, and also memory optimized table require lesser logging than disk spaced table as no index operations are logged, but still the latency could be there for the log, because the commit of transaction will not complete till the log is hardened to disk so if there is good storage then this will not be an issue.

Although the In-Memory OLTP tables provides lot of performance gains but it has lot of limitations too and those must be taken care before you decided to implement this in your production.

• Truncate Table
• Merge
• Dynamic and Keyset cursor
• Cross Database queries
• Cross Database transactions
• Linked Server
• Locking Hits
• Isolation Level Hints (ReadUncommitted, ReadCommitted, and ReadCommittedLock)
• Memory Optimized table types and table variable are not supported in CTP1
• Tables containing binary columns such as text, xml or row width exceeding 8kB cannot be configured as “in-memory” and there are also some limits on SQL commands and transaction concurrency options
• Default and check constraints are not supported in memory optimized tables
• User defined data types within table definition are not supported
• Expects the collation of the table or database a BINARY – Latin1_General_100_BIN2 (tested trying with a database with collation SQL_Latin1_General_CP1_CI_AS and resulted in error)
• File SIZE or AUTOGROWTH can’t be set to the file stream data file (in the CREATE DATABASE syntax)
• The non BIN2 collation is not supported with indexes on memory optimized tables
• In memory table creation fails in the normal database as it is not supported (The feature ‘non-bin2 collation’ is not supported with indexes on memory optimized tables.)
• The above statement doesn’t apply for a normal disk table residing in an in-memory database
• Identity columns can’t be defined in the memory optimized tables Data types IMAGE, TEXT, NVARCHAR(MAX), VARCHAR(MAX) types not supported in memory optimized tables
• Row size for the memory optimized table is 8060 bytes
• Memory optimized table can’t be altered to add a FORIGEN KEY constraint & in-line creation of Foreign key constraint can’t be created either
• CREATE TRIGGER not supported by the memory optimized tables
• CREATE STATISTICS on the tables wasn’t allowed when tried to create (CREATE and UPDATE STATISTICS for memory optimized tables requires the WITH FULLSCAN or RESAMPLE and the NORECOMPUTE options; the WHERE clause is not supported.)
• Partition not supported
• LOB data type not supported