Episode 89 - MS-SQL Memory Optimised Tables

Memory-Optimized Tables, also known as In-Memory OLTP, were introduced in SQL Server 2014. This feature was further enhanced in subsequent versions of SQL Server, including SQL Server 2016, 2017, and in Azure. However, in Azure, they're only available in the Premium tier.

Memory-Optimized Tables in SQL Server are a feature that provides significant performance improvements by storing table data in memory and optimizing data access. Here's an overview:

• What are Memory-Optimized Tables? Memory-Optimized Tables are tables that have their data completely held in memory, eliminating disk access except for durability purposes. This allows SQL Server to redesign how data access is handled, including locking and latching, resulting in substantially improved performance.

• How do they work? Memory-Optimized Tables store their data in memory using multiple versions of each row’s data. This technique, known as "Non-blocking multi-version optimistic concurrency control", eliminates both Locks and Latches, thus achieving breakthrough performance. All row versions are maintained in the same table data structure. Row versioning is used to allow concurrent reads and writes on the same row.

• Durability of Data: Memory-Optimized Tables are fully durable by default, meaning that like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). A second copy of the table data is maintained on disk, but only for durability purposes. Data in memory-optimized tables is only read from disk during database recovery (e.g., after a server restart).

• Non-Durable Tables: Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but the data will not be recovered if there is a server crash or failover.

• Limitations: Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features. Starting with SQL Server 2016, and in Azure SQL Database, there are no limitations for collations or code pages that are specific to In-Memory OLTP.