Denny's SQL Secrets

Using In-Memory OLTP Tables in SQL Server 2014

Knowing exactly what in-memory OLTP tables are used for will help you make the most of one of SQL Server's newest features.

• By Denny Cherry
• 01/31/2014

While in-memory online transaction processing (OLTP) tables may sound like a great solution for all tables within a database engine, it's actually not. This feature is designed to fix a very specific problem -- and only that problem.

In-memory OLTP tables were introduced with SQL Server 2014. These tables keep the entire set of data in RAM, accessing the table contents from memory. This requires that all data fits into the physical memory allocated to the SQL Server instance.

The problem in-memory OLTP tables try to fix is slow data changes due to latching and locking. In-memory OLTP tables often make minimal performance improvements on select slow-performing statements. This is due to design changes within the database engine introduced with the in-memory OLTP table feature. Those changes reduce the amount of latching and locking that actually happen when you run queries in the first place.

When you run normal select statements, the amount of latching and locking that happens is already minimal. The amount of latching is minimal because the data that SQL Server needs is already in memory. And when you aren't making any changes to data, you don't need any latches on the data pages.

Because of the way the in-memory OLTP tables are designed, they only fix problems with tables that have a high number of threads all inserting data into the same table at once. Some examples of use cases for in-memory OLTP Tables include the .NET Session State Database, logging tables, staging tables for reporting databases and data warehouses.

Don't Get Caught
One of the problems with in-memory OLTP tables is that getting into them or back out of them is a lot of work. There's is no way to convert a normal on disk table into an in-memory OLTP table. There's also no way to convert an in-memory OLTP table back to a normal on disk table. The only way to switch back and forth is to export the data (assuming you need to keep the data), drop the object and recreate it, then import the data back into the table if needed.

Tables Continue To Grow
Don't forget that tables will continue to grow. If tables within the database grow beyond the amount of memory you can allocate to the SQL Server instance, the table isn't a good candidate for in-memory OLTP tables. In-memory OLTP tables require that the entire table resides in memory at all times.

Remember the most memory Windows can use, as of Windows 2012 R2, is 2TB. The in-memory OLTP table must be less than that (depending on how the final version of SQL Server 2014 allows for memory allocation to the In-memory OLTP table feature). If you try to write more data into an in-memory OLTP table than there is room for, the insert will simply fail. There's no way for SQL Server to spill the table to disk. Once the in-memory OLTP table is full, you have to delete some data before writing more.

Slow Disks
Using the in-memory OLTP table feature also requires very fast disks when using durable tables. There are two reasons you'll need fast disks. Even though the data is stored in memory, the transactions are still logged to the transaction log. So the transaction log must be fast. Basically the transaction log needs to be as fast as possible so it can log changes to the in-memory OLTP table as fast as needed.

The second reason you'll need high-speed disks is to support server restarts. When the instance is restarted, you have to reload the entire in-memory OLTP table from the disk into memory before you can access the table. If there is 1 TB of data in the in-memory OLTP table, then SQL Server will need to read 1 TB of data from the disk before you'll have access. If the disks are slow, reading the in-memory OLTP table from the disk would take minutes at the least if not longer.

While the in-memory OLTP tables feature is going to solve performance problems for some applications, it's not going to be a magic bullet for most performance problems. Most performance problems that happen in the real world are based on querying data from the database engine. That isn't where the in-memory OLTP table is going to solve problems. These sorts of performance problems are going to be best solved through traditional index tuning, fast storage, more RAM and clustered ColumnStore indexes.