(SQL) Tip of the Day: SQL 2014 In-Memory OLTP

Today’s (SQL) Tip…

In-Memory OLTP is a new feature introduced in SQL Server 2014. It allows you to store tables (up to 250 GB) wholly in memory for faster read and write access of the data. From the article:

“To use In-Memory OLTP, you define a heavily accessed table as memory optimized. Memory-optimized-tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables. A query can reference both memory-optimized tables and disk-based tables. A transaction can update data in memory-optimized tables and disk-based tables. Stored procedures that only reference memory-optimized tables can be natively compiled into machine code for further performance improvements. The In-Memory OLTP engine is designed for extremely high session concurrency for OLTP type of transactions driven from a highly scaled-out middle-tier. To achieve this, it uses latch-free data structures and optimistic, multi-version concurrency control. The result is predictable, sub-millisecond low latency and high throughput with linear scaling for database transactions. The actual performance gain depends on many factors, but 5-to-20 times performance improvements are common.”

Check out the rest of the article and get more information about In-Memory OLTP here: https://msdn.microsoft.com/en-us/library/dn133186.aspx