Infrastructure at your Service

Stéphane Savorgnano

SQL Server 2016: In-Memory OLTP enhancement

The CTP2.1 has been released for some weeks and it is time to check which interesting enhancements have been already deployed for the In-Memory OLTP feature.
I will first have a look to the memory optimized table.

Memory Optimized table

I have a Memory Optimized table named command_queue_HK and I will add a column to this table. This table is saved on my disk in a XPT folder with its corresponding files:

b2ap3_thumbnail_In-memory1.jpg

I execute the following command:

b2ap3_thumbnail_In-memory2.jpg

If I have a look to my XTP folder I see that I have six new files:

b2ap3_thumbnail_In-memory3.jpg

Does it mean that each time I make an alter table I have 6 new files? Let’s try another alter table:

b2ap3_thumbnail_In-memory4.jpg

Go back to my XTP container:

b2ap3_thumbnail_In-memory5.jpg

I have six new files for my last alter. Each time you run an alter table you create six new files corresponding to the new structure of your table… could by really disk consuming if you update a lot your tables… which hopefully could not arrive too often.
For the time being I cannot find a process which cleans obsoletes files, but when you restart you SQL Server Engine this process runs and deletes all files corresponding to your tables and recreates just six for the current structure:

b2ap3_thumbnail_In-memory6.jpg

This alter table is an offline process and need twice the size of the table in memory… don’t forget it.

It is also possible now to use Non-BIN2 collations in index key columns:

b2ap3_thumbnail_In-memory7.jpg

Some functionalities are not yet available (no exhaustive list).

Foreign Keys for Memory Optimized table:

b2ap3_thumbnail_In-memory8.jpg

Check constraint:

b2ap3_thumbnail_In-memory9.jpg

Natively Compiled Stored Procedure

For Natively Compiled Stored Procedures, we also have some enhancements and as for Memory Optimized table the first one is the possibility to alter them.
If I check on my container folder I see the six files for my Natively Compiled Stored Procedure:

b2ap3_thumbnail_In-memory10.jpg

If I right click on my SP in Management Studio I see that I have two possibility to update my SP:

  • select Modify
  • go through “Script Stored Procedure as”, “ALTER to…”, select where to script it

Once my Stored Procedure is scripted, I can make the modifications I need and execute the code:

b2ap3_thumbnail_In-memory11.jpg

Once the code is executed, I don’t have like for table six files more but directly six new files and just six:

b2ap3_thumbnail_In-memory12.jpg

Execute function in Natively Compiled Stored Procedure is also available but just with natively compiled modules:

b2ap3_thumbnail_In-memory13.jpg

It is not working if we try to call a nested Stored Procedure.

Like for Memory Optimized table some functionalities are not already available in this Technology Preview 2.1.

Subqueries are also not available for the time being:

b2ap3_thumbnail_In-memory14.jpg

There are interesting enhancements for In-Memory OLTP already available with this CTP 2.1 like Alter Table or Alter Natively Compiled Procedure but some important ones are not yet getting out like Foreign keys or Constraints for In-Memory tables. We will have to wait the RTM version to be fixed on remaining caveats.

 

Leave a Reply


nine + = 10

Stéphane Savorgnano
Stéphane Savorgnano

Consultant