One of the biggest enhancement in SQL 2014 is “In Memory OLTP”, used to be called “Hekaton”.
Microsoft introduce complete new storage engine “Memory Optimized” to move all the data from specific table into the memory .
Here are couple key points that memory optimized table has better performance than the traditional row base table.
- Access as row level . No page, no lock and latch
- All data has pointer to the row –> Index, only in memory.
- All index are covering index
- Since there is no lock and latch. Storage engine use Multi-version optimistic concurrency to support concurrent access. Similar to Snapshot isolation.
- Native compiled store procedure –> assembly
Below test is base on the TPC-H database.
I user HammerDB to create the TPCH database and test dataset with scalar factor = 1
I also change the database to simple recovery mode to reduce the log file growth.
Create Memory optimize file group
Memory optimize file group is for durable table only. Only one memory optimize file group allow per database. In memory file group needs the fast sequence read not random access. so not necessary needs the SSD.
We can specify the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option on the database, which will set the isolation level for memory-optimized tables to SNAPSHOT (as if we included WITH(SNAPSHOT) hints to every memory-optimized table).
File group type is FX.
Create Memory optimize TABLE
Table can be durable ( SCHEMA_AND_DATA) or non durable ( SCHEMA_ONLY).
- Require Primary key and require index on the primary key
- All Index are Non-Clustered . Must be NOT NULL. NONCLUSTERED keyword is optional
- Index needs to created at the table creation time.
- Maxim 8 Index per table
- BUCKET_COUNT , recommend 1.5 ~ 2X of unique value on the column.
- No Foreign key constrain on the table.
- xml, user defined and lob data ( such as varchar(max) ) type is not supported in the index.
- Row size is limit 8060 bytes. ( disk base table have off page storage)
After we have create the table, we can get the db_id for the database and the object_id for the table. In this case, the database id is 8.
On the database folder, we can find the folder name 8 which has the table definition. The ID 677577452 match our object_id for the orders_mem table. As you can see, the table structure has been compile as C code. That is why we can not change the table structure after table is created. ( No Alter table action allow) and all the index needs to be created at table creation time.
Under the memory optimized file group directory, it has all the data that related to the table.
Finally, below query show us what dll related memory optimized table has been loaded into SQL Server memory. the dll name is DatabaseID_objectID
ERROR related to MEMORY OPTIMIZED TABLE
Below are the some of the errors you may see if we violate the limitation for creating table.
Msg 12301, Level 16, State 7, Line 40
Nullable columns in the index key are not supported with indexes on memory optimized tables.
Msg 1750, Level 16, State 0, Line 40
Could not create constraint or index. See previous errors.
Msg 41321, Level 16, State 7, Line 38
The memory optimized table 'orders_mem2' with DURABILITY=SCHEMA_AND_DATA must have a primary key.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint or index. See previous error
Msg 41307, Level 16, State 1, Line 44
The row size limit of 8060 bytes for memory optimized tables has been exceeded. Please simplify the table definition.
Memory usage report
Click Database –> Reports –> Memory Usage By Memory Optimized Objects