Create Table – Disk vs In-Memory Optimized

I had an interesting e-mail discussion related to the performance of create table.  The test being executed was a tight loop of create table statements.  The assumption put forth was in-memory optimized table creation was slower and they expected it to be faster.  We are not talking about the performance of inserts, updates, deletes and selects but instead looking at the time it takes to perform create table.

The answer is that the creation of in-memory optimized tables might be slower than disk based table creation.   Let’s take a high level look at the create table activities.

Action	Disk Based	In-Memory Optimized
Parse T-SQL	Y	Y
Build Create Plan	Y	Y
Execute Plan	Y	Y
Update System Table Info	Y	Y
Create Access DLL Code	N	Y
Compile Access DLL	N	Y

 

Note: An in-memory, optimized table creates a native DLL (placed in the XTP directory) allowing common select, update, delete and insert activities.  These are additional steps that a disk based table does not perform.   The creation of the XTP, common access DLL requires SQL Server to generate C code, invoke the C compiler and save the DLL into the XTP directory.

While your first thought might be that in-memory optimized is faster, if you take a deep breath and step back you can see the difference.   You give up a bit of time to create the native access DLL, which enables the performance improvements for select, update, delete and insert over a disk based table.  While you might be able to create the disk based table slightly faster, each time you access the table you have to generate or lookup a T-SQL plan and execute it.   The XTP DLL was optimally built at create table time to allow you fast access to your data.

Bob Dorr - Principal Software Engineer SQL Server

Comments

• Anonymous
  September 19, 2016
  Hi Bob,I am curious, are you able to share the table creation rates that were achieved in memory v disk based?I ask because earlier this year we had a case open with Microsoft as we were seeing massive system table blocking during periods of concurrent heavy table creation. The case was tracked to the allocation of the tables objectid and the app developers spread out over multiple databases but we still get problems from time t time.RegardsAndrew
  • Anonymous
    November 15, 2016
    Andrew, the purpose of the article was just to show that there is actually more work to do to create an in-memory optimized table vs a standard disk table. We didn't perform any benchmarks on table creation rates.Bob Ward