SQL Server In-memory OLTP - Project Hekaton

Key Takeways #

• Uses different underlying data structures for data and index storage, thereby reducing or eliminating locks and contention
• No more memory pages or extents
• Memory is byte-addressable, whereas disk is block-addressable, Hekaton takes advantage of that difference
• In-memory tables must have an index, rows are not contiguously located, even within the Primary Key; rows from different tables could be located side by side
• Index data is not persisted to disk or transaction log; all indexes are rebuilt when the server is restarted
• Enables natively compiled stored procedures
• Can selectively migrate individual tables to in-memory to limit risk
• Replication not supported!

Architectural Overview #

https://blogs.technet.microsoft.com/dataplatforminsider/2013/07/22/architectural-overview-of-sql-server-2014s-in-memory-oltp-technology/

Limitations #

https://msdn.microsoft.com/en-us/library/dn246937%28v=sql.120%29.aspx

Interesting Links #

In-memory OLTP Whitepaper:
https://www.microsoft.com/en-us/server-cloud/solutions/oltp-database-management.aspx

In-memory Team Blog:
https://blogs.technet.microsoft.com/dataplatforminsider/2013/06/26/sql-server-2014-in-memory-technologies-blog-series-introduction/

MSDN Docs
https://msdn.microsoft.com/en-us/library/dn133186.aspx

Choosing to Port
https://msdn.microsoft.com/en-us/library/dn205133.aspx

BrentOzar Comments:
https://www.brentozar.com/blitz/hekaton-memory-oltp-tables-use/

Jonathan Watts at RedGate:
https://www.simple-talk.com/sql/performance/the-promise—and-the-pitfalls—of-in-memory-oltp/