SQL Server In-Memory Tables Impact on Startup and Recovery

Problem

By now we’ve all read the improvements that can be gained with respect to performance by using the new SQL Server 2014 In-Memory OLTP feature, but are there any drawbacks to the feature? One possibility I thought about was how using this feature could have an effect on instance startup/recovery duration, assuming we are not using the “DURABILITY=SCHEMA_ONLY” option, as it has to take some amount of time to load this data into memory.

Solution

In order to test this theory we’ll need to create a table that uses this new feature, but before we can create it we’ll need to create a filegroup that will store the In-Memory data as outlined in this tip. Once that is done we can use the following T-SQL to create our In-Memory table.

-- Table creation logic for in-memory storage                        
CREATE TABLE testtable_inmemory ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON);

Our test scenario will consist of multiple iterations of loading data into the table and performing an instance restart. We’ll also perform a manual CHECKPOINT before each instance restart to ensure all the data is written to the filestream filegroup for this object and there is no extra time spent on startup reading the transaction log. We’ll run another small test at the end of this tip to see if there is a difference without the CHECKPOINT. Our first iteration will just be an instance restart to get our baseline. For each subsequent iteration we will update the code below to add 2,500,000 more records to the table. For example, the second iteration will have “SELECT @val=2500000” and “WHILE @val < 5000000”. Here is the T-SQL to perform these tasks.

DECLARE @val INT
SELECT @val=1
WHILE @val < 2500000
BEGIN  
   INSERT INTO testtable_inmemory (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END
GO
CHECKPOINT
GO

After each iteration we can see how long the database took to come online by looking at the SQL Error Log. In there you should see a line similar to the following.

Recovery completed for database test (database ID 5) in 1 second(s) (analysis 3 ms, redo 2 ms, undo 1833 ms.)

Now that we have our test scenario defined we can execute it and take a look at the results.

It’s easy to see from these results that as the table gets larger it takes longer for SQL Server to bring this database online. The data shows an almost linear relation between the table size and startup/recovery duration.

As mentioned above let’s perform a similar test, this time without the CHECKPOINT, and see if there is any difference in our results. We’ll again start with an empty table but this time we’ll load 2000000 records. Here is the T-SQL for these steps.

-- Table creation logic for in-memory storage                        
CREATE TABLE testtable_inmemory ([col1] [int] NOT NULL primary key nonclustered,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL) WITH (MEMORY_OPTIMIZED=ON);
DECLARE @val INT
SELECT @val=1
WHILE @val

We’ll now perform our restart and record the same metrics as above. We’ll also include the size of the transaction log since even though we are running this database in simple recovery mode and our transaction has committed, the way In-Memory table transactions are processed is the log file is still used. To check the transaction log usage we can use the DBCC SQLPERF(logspace) command.

Now let’s perform a CHECKPOINT and another instance restart and see if our results go back to how they were during our original test. The results below confirm that this is the case.

Summary

My test was performed on a standard desktop machine. If you are running higher end enterprise level hardware your numbers will most likely be different than above, but it’s important to note that this new feature will have an effect on startup/recovery duration and you should take this into account when deciding whether or not you want to use this new feature as opposed to the original disk-based table structure.

Next Steps

• More information on In-Memory OLTP Internals
• Read other tips on SQL Server Performance Tuning