In-memory tables in 60 seconds ~ xSQL Software

Thursday, October 13, 2016

In-memory tables in 60 seconds

As a SQL Server DBA or developer here is what you should know about memory-optimized tables (in-memory tables):

Introduced in SQL Server 2014
Primary store is main memory, second copy maintained on disk to achieve durability
Handled by in-memory OLTP, a memory optimized database engine integrated with SQL Server engine
Fully durable, ACID transactions. Supports delayed durability for better performance (risk losing committed transaction that have not been saved to disk)
SQL Server supports non-durable memory optimized tables (not logged and data not persisted to disk) – no recovery option in case of disk crash
No-lock concurrency eliminates blocking and achieves better performance. Concurrency handled through row versioning.
Not organized in pages and extents, memory-optimized tables are a collection of row versions and a row version is addressed using 8-byte memory pointers
Data in memory-optimized tables can be accessed:

(most efficient) Through natively compiled SPs (the limitations is that natively compiled SPs can only access in-memory tables)
Through interpreted t-sql inside a standard SP or through ad-hoc t-sql statements.

Our Schema and Data Compare tools support comparing and synchronizing in memory tables.