In 2012 it became possible to find commercial two-socket servers with 2 Terabytes of DRAM. With that kind of memory available, Microsoft felt that it was time to start seriously thinking about databases that held their working sets entirely in memory. The result of this is SQL Server 2014 and a new feature known as either “Memory Optimized Tables” or “In-Memory OLTP”.
Memory Optimized Tables are not just a tables that happens to fit in memory. This is an entirely new storage and query subsystem with designed around tables that must remain in RAM at all times. This allows for some interesting optimizations.
One of the optimizations offered by this new table type is entirely lock-free writes. Kalen Delaney of Microsoft claims,
When accessing memory-optimized tables, SQL Server uses completely optimistic multi-version concurrency control. Although SQL Server has previously been described as supporting optimistic concurrency control with the snapshot-based isolation levels introduced in SQL Server 2005, these so-called optimistic methods do acquire locks during data modification operations. For memory-optimized tables, there are no locks acquired, and thus no waiting because of blocking.
It should be notes that there is a difference between “no waiting on locks” and “no waiting”. Waits can still occur when writing to the transaction log.
Memory Optimized Tables are durable by default. Though you can turn durability off, under normal circumstances you’ll enjoy the same ACID guarantees available for traditional disc-based tables. In addition to the standard transaction log, a separate file group tagged with the “CONTAINS MEMORY_OPTIMIZED_DATA” option needs to be created.
Non-durable or “SCHEMA_ONLY” tables are not merely less durable. These tables are entirely free of any I/O. If the server is restarted all data will be lost. This makes it an ideal candidate for caching denormalized data, transient uses sessions (e.g. from ASP.NET), and other data that can easily be recreated.
Memory optimized tables are restricted when it comes to data types. Anything that involves storing data “outside of the row” is prohibited. This includes binary, xml, and text as well as types such as varChar(max). Everything must fit within the standard row size of 8060 bytes.
Furthermore, you cannot cheat with varChar columns. Row size is calculated by looking at the maximum size of variable length columns, not the minimum as is the case with traditional tables. But at least you won’t have to worry about data truncation when using this.
Tomorrow we’ll look at indexes in memory optimized tables.
