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. 


Post a Comment