Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Database Startup and Recovery Process

Posted by blakhani on December 31, 2013

“If you are a seasoned driver you have a number of parameters to check before you start your vehicle. It can range from a simple task like if all the doors are locked and there is no indicator, or if the safety seatbelt signs are off or even more critical can be activities like if the fuel tank readings are showing us values that is satisfactory keeping our destination in mind. If reaching a destination using your vehicle can be this strenuous task that we do it with such ease, the process of SQL Server startup also goes through a simple steps of readings that we can start assuming. Adding an In-Memory dimension to an already complex process is always going to be a learning by itself.”  These words are from Vinod (b|t) when I asked him to give me database startup co-relation in real life. Hats off to you Vinod!

Yes, in this blog post. we would be discussing about database startup and recovery for in-memory tables. Database startup is also known as recovery of database. Whenever SQL Server (re)starts, all databases go though start-up subroutine. Below are the situations where database startup or recovery is performed.

  • Restart of SQL Service
  • Restore from a backup.
  • Failover to Mirror or AlwaysOn secondary.
  • Failover of SQL Server in cluster.

In traditional databases having only disk based tables, the database startup/recovery has three phases: analysis, roll-forward (redo) and rollback (undo). These three phases are well documented in many places. Ref1, Ref2. In this blog, we will talk about startup of databases which have in-memory tables.

Since we are talking about in-memory tables, one of the step during database startup would be to load data for all in-memory tables into memory (RAM). In earlier post, we have talked about DLLs of in-memory table and learned that they are generated during database startup. We also discussed here that indexes information is not logged into transaction log and data files, hence index needs to rebuild during loading of data. So, to summarize, here are the major step of recovery of database having in-memory tables.

  • Generate DLLs of in-memory tables
  • Load data into the memory using checkpoint files (DATA & DELTA files) and transaction log file of database.
  • Create index which are defined on in-memory tables.

If any of above steps fail, the database recovery fails and the database would not come online. Let’s verify few theoretical points via demo.

Generate DLLs of in-memory tables

To demonstrate this, I would go ahead and take our database offline using below command

Use master
Alter database SQLSeverHelp_IMO set offline with rollback immediate

Once database is offline, let’s look at folder which had DLLs for in-memory tables. On my this SQL Instance there is no other database having in-memory tables.


As we learned earlier, this folder generally has folder for DatabaseID and each of those would have DLLs for the objects in respective database. On my SQL instance, at this point there is no database which is online and having in-memory object, so this folder is empty. Now, let’s bring the database online.

Alter database SQLSeverHelp_IMO set online

The database id for SQLServerHelp_IMO is 8 for my SQL Instance, and here is what I see once I bring database online.


You may ask, what about the natively compiled stored procedure’s DLL. Well, they would be generated when the stored procedure would be first executed (compiled automatically)

Load data into the memory

As explained earlier, this is also a different step as compare to traditional disk based table. Once database is started, we need to load the data using checkpoint files and transaction log of database. In my previous blog of checkpoint (part 1 and part 2), I have explained about DATA and DELTA file. 

Here is the pictorial representation of data loading process. Image Reference


There are three phases – we can call them as analysis, data load and redo. Note that there is no undo phase because in-memory tables doesn’t log uncommitted data into the transaction log (refer here). During analysis phase, transaction log is read and last checkpoint is located. In data load phase, we first load the DELTA files into memory which contains the pointer for the rows which are deleted. This creates a “delta map” for each delta file. Then data file is loaded into memory and filter is applied. Only those rows are loaded into memory which are not deleted. The loading of data happens in parallel (that’s why there are multiple 1’s and 2’s in image). You can imagine that if we have slow disk sub-system, the time taken by database to come online would be longer.

Create index which are defined on in-memory tables

As we have discussed earlier, only index definition is persisted in physical files. Indexes are only available in-memory and that’s why there is no transaction information is logged in physical files. Once data is loaded and DLLs are generated, next step is to generate index on the data which is loaded into memory. We would talk about indexes in later part of this series.

Once all three steps are completed, database would be made available. If there is any system problem and we are enable to load the data in memory or failed to generated the DLL, SQL database recovery would fail and database would go to “Recovery Pending” state. It’s worth mentioning that if our database has in-memory tables as well as disk based tables and database recovery fails, we would not be able to access disk based tables as well.

In my next blog, I would take up scenarios of recovery failure and show you steps to troubleshoot them. Start tuned!

By the way, this is going to be last post of year 2013 so with you a happy near year and see you next year on another blog post in A-Z of In-Memory OLTP series.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • 6 Responses to “A-Z of In-Memory OLTP : Database Startup and Recovery Process”

    1. Thanks Sir. Another nice post. I have a quick question. How much time approximately it might take for the DB to come online if the table is say 256GB/512GB in size having 3-4 NCL indexes and 1 Clustered index (Pkey)?

      Kind Regards

      • blakhani said

        i don’t have any numbers to post but it is the factor of disk response. In current known speed, a disk can load 1GB of data per second. So if you have 100 GB of data, it would be little more than 100 seconds to load the data and rest operation is in memory and it would not very less time. We also do parallel load which is factor of number of cores on the machine.

        BTW, there is no clustered Index in-memory table.

    2. […] « A-Z of In-Memory OLTP : Database Startup and Recovery Process […]

    3. […] definition is stored on disk and index structure is created while starting of the database. Refer this […]

    4. dharmendra keshari said

      Thank you Balmukund Sir for nice article and Abhay sir for below question… it reduced one question from me😊!!

      Balmukund sir, as you said that index recreate after recovery process (basically back restoration or bringing database online). if we bring down any database which is having in-memory table and bring online the database later on, does it mean that it is going to resove the collision problem if that it was there previously in the inmemory table?

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: