A brief overview of what is in-memory OLTP, how to convert tables to be memory optimized, some limitations with in-memory tables and a few additional items of interest.
What is in-memory OLTP? It was first introduced in SQL Server 2014 as code name Hekaton. It takes a traditional disk based table and loads it into memory for improved performance. Although it exists in-memory the data can be persisted to disk for data durability (it is only read from disk on server startup). Data durability can be disabled or set to delayed durability which may improve latency but is a risk in the event of a server crash or unexpected shutdown.
Since the data lives completely in-memory it eliminates latches and locks. Indexes are also in-memory and inherently covering. Fragmentation and fill-factor do not apply. In-memory tables can be used in conjunction with disk based tables but query performance will be limited by the performance capability of the disk based tables.
In-memory tables are best for high volume, short running transactions. Typical workloads would include high volume read operations and operations where low latency is needed. Many articles mention session state management or other web and social networking applications. They are not well suited for long running transactions such as ETL or OLAP.
Converting disk based tables to in-memory
A database that hosts in-memory tables will need a new FILESTREAM file group for memory optimized data. Fortunately if you use the Memory Optimization Advisor in Management Studio to convert disk based tables to in-memory this will be added for you automatically.
In-memory tables must have a primary key. This key is not a clustered index since those are not allowed for in-memory tables, but is rather a non-clustered hash key. The disk based table you are converting does not need to have this prior to migration, it will be created during the migration process, but it must have a way to uniquely identify each row. The easiest way to do this is a single column that is an integer such as a row id column. This does not even need to be a primary key prior to migration although it can be. You can use other types of columns for identifying each record however: any string data type columns that will be in the primary key need to be a BIN2 collation (SQL Server 2014), nullable columns are not allowed in the primary key and unique constraints are not allowed.
To identify other items that would prevent your candidate table from being memory optimized run the Memory Optimization Advisor from the context menu when you right click on the candidate table
You can see my table has a couple issues. I changed the text data type to character data type and dropped the default constraint. If you have to make similar adjustments you can see that it could require code updates in the application to now insert values that used to be handled by a default setting for a column.
Now it passes all the validation checks.
Note the warnings. Once the table has been memory optimized there are a variety of things that you will be unable to do on the table that you could have on a disk based table. You also cannot alter the table nor any of the indexes once the memory optimized table has been created.
Here is where the FILESTREAM file group will be created for you. Make any adjustments needed to make sure it conforms to your standards and best practices. You will notice this also renames your disk based table. If you want the data in the disk based table copied to the memory optimized table you will need to check the box. It is unchecked by default. Lastly, you can check the box to make the table without data durability.
This is where you identify what will be the primary key (non-clustered hash key) for the memory optimized table. In this example I am converting the pre-existing primary key that was an integer data type. By default it is set to create the non-clustered hash key; these are good for point lookups but not as good for ranges. You can choose a non-clustered index which will work better for ranges if that is your anticipated workload. If you do go with the non-clustered hash key you will need to give it a bucket count. Here is a good explanation of how to figure out the bucket count. This cannot be adjusted once the table has been created.
Review the settings and migrate your table.
Migration complete.
If we check our database properties we can see that the FILESTREAM file group has been added.
Once the table has been created you can’t adjust the bucket count, alter or add indexes or alter the table. So what do you do if you need to change that varchar(50) to varchar(500) or add a new column or adjust the bucket count? You have to revert the table back to disk based, make your changes then migrate the table to memory optimized once again. To revert the table back to disk based just follow these steps:
- SELECT * INTO new_table FROM memory_optimized_table
- Add needed indexes to new_table
- DROP memory_optimized_table
- Rename new_table to previous table name
What is improving with in-memory OLTP in SQL Server 2016?
Alter table after creation will be supported. Natively compiled stored procedures can be altered. Better collation support so string data types in the primary key will not be required to be a BIN2 collation. As SQL Server 2016 continues to release CTPs the list is likely to grow. Here is one other article on improvements with in-memory OLTP in SQL Server 2016.
One of the challenges you may face is deciding which of your current tables would benefit from memory optimization. There are tools in Management Studio to assist you.
If you haven’t already you will want to configure the Management Data Warehouse. You can access the wizard for this by expanding the Management folder in the Object Explorer and right clicking Data Collection to bring up the context menu. From this menu go to Tasks/Configure Management Data Warehouse. Pick the server to host it and give it a database name. Also map the logins and users you would like to use with the Management Data Warehouse. Once the warehouse is set up we need to put data into it.
To get to the data collection wizard you follow the same path as we used for configuring the warehouse but choose the Configure Data Collection option. Pick the server and database you just set up and when you are selecting data collector sets be sure to include the Transaction Performance Collection. Once you have this configured you will have to give it some time to collect data…how much time is up to you.
Once you have data you will want to analyze it. Right click on your Management Data Warehouse database and from the context menu choose Reports/Management Data Warehouse/Transaction Performance Analysis Overview. There should be links to three different analysis reports: two for tables, one for stored procedures. You will want to look at the table analysis reports.
Both of the analysis reports have a similar format. There is a performance gain axis running up and down on the left and the horizontal axis indicates the level of work required to migrate a given table. This divides the report into four quadrants. The tables you will likely want to consider migrating to memory optimized will be in the High Gain/Minimal Migration Work quadrant.
I hope this has brought some insight into in-memory OLTP, maybe answered a few question and maybe caused you to think of a few more than you previously had. There is a lot of information out there and one blog post can’t cover it all. I am sure as in-memory evolves and I have more exposure to it through use I will find additional things to write about.
