SQL Server In-Memory tables – Where is data stored?

By | In Blog, Database Administration, SQL Server | February 20th, 2015

SQL Server In-Memory tables - Where is data stored?SQL Server 2014 introduced the concept of InMemory OLTP which has been a lesser explored topic for many. Previously we saw how these InMemory tables can be created and how one can access the same. Once the table is created, the syntax, TSQL construct and access seems to be almost similar to how a normal table is created. The interesting aspect of using InMemory OLTP is that it can be configured to be purely an in-memory tables or can be configured as in-memory durable tables. The latter being the default behavior.

Isn’t it ironic that InMemory tables are also durable? One of the major questions asked by developers implementing the same revolves around, where are these tables stored? How are entries written into them? How can we know more about their architecture? All these are valid questions and this blog will try to demystify some of these basic questions.

Understanding by code

The first step is to start creating our database with InMemory capability enabled. A Simple TSQL construct for the same would look like:

In-Memory Tables

The next logical step is to create an InMemory Optimized table in this database.

Note how we have specified the Memory Optimized table and Schema_and_data as part of durability. This makes sure the data is persistent and can withstand any server reboots. Let us once again check the metadata for this database.

You will see the InMemory table has been implemented as FILESTREAM and can be checked in the UI like below. Select the database properties, Filegroups and the Memory Optimized Data section for details.

In-Memory Tables by Datavail

Where is my data?

Now that we have created the filegroups, we need to next explore the files that SQL Server creates as part of InMemory tables. These files are called as Checkpoint files. They come in pairs called as DATA and DELTA files. To check all the checkpoint files created, execute the following DMV:

I am running on an 8 core machine and have got 18 files – Data files are 128 MB in size each and Delta files are 8MB in size. Check for the active checkpoint files also note how the data and delta files are made as a pair:

Datavail In-Memory Table Example

In the above diagram you can see the Data file has a Delta file as pair and vice versa. Next step is to insert few rows, Delete a row and Update few rows into this table:

As you can see we have:
INSERTED: 1000 rows + (50 which was updated)

DELETED: 1 row + (50 which was updated)

UPDATED: 50 rows

An update in the system has to be considered as a DELETE followed by an INSERT. Now let us query the DMVs to see what these files contain.

In-Memory Tables

As you can see this perfectly adds-up to the calculations we made just a while back.

Now that we understand how InMemory OLTP checkpoint files work, you can say with confidence where the data is getting stored. SQL Server efficiently manages these files as it rolls over and created new files once they get full. In this blog, we showed where data for InMemory gets stored and how one can query the same to get vital information about InMemory tables.

Routine healthchecks have proven to improve database performance. Check out this white paper, Top SQL Server Issues where we will share the top issues, the consequences of not taking action, and why consistent use of a SQL Server Health Check in conjunction with ongoing database management can lead to improved database environments and maximize the investment of time and resources.

If you are interested in learning more, please visit my other SQL Server blog posts as well as our latest SQL Server blog: In-MemoryOLTP – New Index – Hash Index.

Contact Us
Pinal Dave
Consultant to Datavail
Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “SQL Server In-Memory tables – Where is data stored?”
  1. Hey, Thanks for the article.
    I have a question on the data management in hash table.
    We are running multiple stored procedures ex : proc 1,proc2 , one by one in another procedure proc3

    If I use the same has table in proc1 and proc2 what happens to data of botht proc has insert statmenets into the same HASH table?

    Is it Append? or Ovverride?

    Appreciate your help here.