Increased Memory Size for In-Memory OLTP in SQL Server 2016

  • Article

We are happy to announce that SQL Server 2016 removes the size limitation on user data in memory-optimized tables. You can grow your memory-optimized tables as large as you like as long as you have enough available memory. This means that with Windows Server 2016 you can leverage all 24TB (Terabytes) of available memory in a given server with In-Memory OLTP. With this, you can achieve incredible performance gains (up to 30X) for very large transactional (OLTP) workloads.

When In-Memory OLTP was initially released in SQL Server 2014, there was one key size limitation: SQL Server supported up to 256GB of active user data in tables with durability SCHEMA_AND_DATA in a given database. This limit was due to limitations in the storage subsystem – there was a limit of 8192 checkpoint file pairs, with each containing up to 128MB of data. 256GB was never a hard limit, and it was theoretically possible to store more data in durable tables. However, the storage size is dynamic in nature (even if the data size is not), since it is an append-only system with merge operations, that can go up and down in size, as the source files for merges go through the garbage collection process. Based on both the expected behavior of the system in various scenario and our internal testing we settled on the supported limit of 256GB.

With SQL Server 2016 we revamped the In-Memory OLTP storage subsystem, and as part of that work we removed the limit on number of checkpoint files, thereby removing any hard limit from supported data size. When SQL Server 2016 was initially released we stated that we supported up to 2TB of data in memory-optimized tables. This was the limit up to which we had tested, and found that the system worked well.

We have continued testing with large data sizes, and we have found that with 4TB of data in durable memory-optimized tables SQL Server 2016 continues to perform well. The machine in question had 5TB of memory total – the 1TB of overhead was used for operational needs such as supporting the online workload as well as database recovery. We have not found any scaling bottleneck when going from 2TB to 4TB, either in the throughput of the online workload, or with operations such as database recovery. We have thus decided to remove any limitation from our statement of supported data size. We will support any data size that your hardware can handle.

Further reading:

Updated (10/18/2016): Windows Server 2016 RTM supports up to 24TB