Memory-Optimized table memory limit - SQL Server Brain Basher of the Week #057

Let's talk about memory-optimized tables as a part of Brain Basher in this week. This question was raised during one of my workshops and it is an obvious question comes to your mind when planning on memory-optimized tables. Here is the question/

How much memory memory-optimized table can consume maximally?

If the version is SQL Server 2014, then there is a limitation on durable tables which is 256GB and no limitation on non-durable tables. However, this has been addressed with SQL Server 2016 and now NO LIMITATIONS on both durable and non-durable tables.

You can read more info on this enhancement at: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/06/23/increased-memory-size-for-in-memory-oltp-in-sql-server-2016/

Read some post made on memory-optimized tables;
In-Memory OLTP: Inserting 3 millions records in 3 seconds
How to check tables and stored procedures for in-memory migration

SQL Server needs memory optimized file group even for non-durable memory-optimized tables?

We know that SQL Server requires an additional file group that is marked as MEMORY_OPTIMIZED_FILEGROUP and a data file associated with it if memory-optimized tables need to be created. We know for sure that memory-optimized tables that are created as SCHEMA_AND_DATA require data files to write data to disk because they are coexist with disk-based tables but tables that are created as SCHEMA_ONLY require the same?

Memory-optimized tables that are created as non-durable (SCHEMA_ONLY) maintain both data and indexes in memory. Therefor, theorically it does not need anything to be written to disk. However, if you try to create a non-durable memory-optimized table without adding a memory-optimized-file-group, you get the following message;

Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

This says even for non-durable tables, it needs the file group. Although it does not maintain data in the disk, it has to create the table as the schema has to be maintained. Therefore, regardless of the type of memory-optimized tables, file group with MEMORY_OPTIMIZED_FILEGROUP option should be added to the database with a data file before creating memory-optimized tables.

How to check tables and stored procedures for in-memory migration

SQL Server introduced In-memory optimized tables with 2014 and it has been enhanced with SQL Server 2016. This improves the performance significantly and it is not that difficult to implement too. If we create a new database for new set of requirements, then it is easy to determine whether tables and stored procedures can be created using in-memory OTLP but it is bit difficult to check and see whether tables and stored procedures in existing databases are compatible for it.

We have been given a user-friendly wizard by SQL Server 2016 for determining the compatibility. This wizard helps us to go through tables and stored procedures we have in our database and check whether they can be converted. Not only that, if it is not possible, what are the possible reasons and solutions for converting them.

Let's try with one database. I have restored ContosoRetailDW database in my 2016 instance. I can find the wizard called Generate In-Memory OLTP migration checklist as below;

When click, Welcome Screen appears and the next page is for selecting objects to be checked. I can

get checked all or I can select individual items too. Remember the path set to Save checklist to. That is the place to be checked after completion of the wizard.

Last page allows to get the Powershell script generated too. If you need, you can get the code. At the end of the wizard, you see whether it has gone through all objects or not.

Now we can go through the checklist. Open the folder used, you should see three folders for; tables, stored procedures, and user defined functions.

Let's take one checklist and see. Lets open the checklist generated for DimDate table: MigrationAdvisorChecklistReport_DimDate_20160509.html file.

As you see, it clears says whether table is supported and what are the possible reasons if unsupported including possible solutions.

SQL Server 2014 CTP1: Error 41342 - The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA….

You might have already faced for this, or you will be, if you try to create a file group for your in-memory optimized tables. The reason for this is, in-memory optimized tables require a processor that supports atomic compare and exchange operations on 128-bit values (as per this:http://msdn.microsoft.com/en-us/library/dn232521(v=sql.120).aspx, I do not have much idea on it :)). This requires assembly instruction CMPXCHG16B. Certain models do not support this. Certain virtual environments do not enable this by default.

My virtual environment is VirtualBox. Here is the way of enabling CMPXCHG16B instruction set;

• Get the list of all VMs configured using VBoxManage.exe list vms
• Then enable it using VBoxManage.exe setextradata “VM Name” VBoxInternal/CPUM/CMPXCHG16B 1

The internal changes on executing this command is unknown. Hence be cautious on this before enabling this in production environments.