In a previous post I talked about transaction log works, and what about using a memory-optimized table?
SQL Server has the feature Memory-Optimized Objects to improve performance. In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree. A Bw-Tree is a lock and latch-free variation of a B-Tree.
To enable an application to use In-Memory OLTP, you need to complete the following tasks:
- Create a memory-optimized data filegroup and add a container to the filegroup.
- Create memory-optimized tables and indexes.
- Load data into the memory-optimized table and update statistics after loading the data and before creating the compiled stored procedures.
Create natively compiled stored procedures to access data in memory-optimized tables. You can also use a traditional, interpreted Transact-SQL to access data in memory-optimized tables.
- As needed, migrate data from existing tables to memory-optimized tables.
In this new architecture, let’s see what happens in the logging level.
I created a table using the code:
CREATE TABLE InMemoryTable ( ID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED , FIRST_NAME VARCHAR(100) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
As you can see, the first part is equal to a normal table and to create the in-memory table we use MEMORY_OPTIMIZED and the DURABILITY. In this case, I’d like you to pay attention to the DURABILITY configured to SCHEMA_ONLY.
Let’s take a look in the transaction log after create the table.
We can see all the sys changes to create the table. Now, inserting a row in this table and see the log again:
INSERT INTO InMemoryTable VALUES('Douglas Correa') GO
You can see nothing changed, but where’s my data? The data is there in the table but only in memory. As you can imagine, both the logging and saving the data to disk are expensive operations.
That means with DURABILITY schema_only the data won’t be there after a crash or restart the server. Changing that for SCHEMA_AND_DATA and look in the log file we are going to see the log operation when inserting data.
As you can see, the log operation is LOP_HK, the row is in the log in case of crash SQL Server can redo.
The memory-optimized table is fast and can improve performance especially if don’t need to save the data, but there are limitations and one of limitation I didn’t like was I can’t detach and attach the database recreating the log file.