Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Behind the scenes

Posted by blakhani on December 12, 2013


If you have a kid in your life, you probably get inundated every day with questions starting with word “why” and “what”. Why do kids ask why? One of the answers is, that children are naturally curious about the world around them. After all, there’s much to learn, and much to know. We all have a kid inside us who is very curious, that’s why I thought of writing this blog post.

In A-Z Series, we have discussed about T-SQL constructs and SSMS enhancements to create In-Memory objects. In this part, we would discuss “behind the scene” of those commands.

CREATE DATABASE

Once database is created and one (and only one) filegroup with CONTAINS MEMORY_OPTIMIZED_DATA clause, SQL Server would create a folder which is specified in filename parameter for that filegroup. In our sample database SQLSeverHelp_IMO we have added filename = “C:\IMO_Database\SQLSeverHelp_IMO_dir” which would create a folder behind the scene as shown below.

image

If we look inside the folders, the would be empty because there is no in-memory table created so far. We also have MDF and LDF file which are same as earlier version of SQL, nothing new there. We can use catalog view to get details about Memory optimized filegroup

Use SQLSeverHelp_IMO
GO
Select * from sys.data_spaces
Here is the query in SSMS and the output:
image 

CREATE TABLE

Next logical step is to create a In-Memory table in the database. In our last post, we have created table called MyFirstMemporyOptimizedTable. As soon as table is created, SQL Server is going to create few files into the folder which were created during previous step of database creation.

image

Above files are created as soon as I created one table. Don’t worry by seeing so many files as we would discuss about these file later part of the series. All you need to remember that per file is 131072 KB = 128 MB by default. There are files having zero KB size as we have not inserted/updated/deleted any data in the table. In contrast to disk-based tables having 8K size pages in buffer pool, the In-memory tables don’t have pages in buffer pool. Think of this as rows in the memory linked to each other because we have index on table. In-memory data is not stored on data files at all. Instead, its stored as blob data using filestream files, which are shown in picture above. At this point just remember that they are called CheckPoint files which combination is DATA files and DELTA files. We would go deeper into those files once we go into blog about checkpoint for in-memory tables.

Another thing which has happened behind the scene is to generate the DLL for the table definition and load into SQL Server Memory. Why? Well, In-Memory engine doesn’t have any knowledge about format of data record. It doesn’t know how to access the data which is part of in-memory table. Whenever we create table, a C file is generated which would have all callback functions used by In-Memory Engine. The C file, it’s compiled by C complier and DLL is generated. If you are a developer in C/C++, you can open the file and check the content to get a feeling.

Here is the query

use SQLSeverHelp_IMO
go
SELECT name, 
       description 
FROM   sys.dm_os_loaded_modules 
WHERE  description = 'XTP Native DLL' 
       AND name LIKE '%xtp_t_' + Cast(Db_id() AS VARCHAR(10)) + '_' 
+ Cast(Object_id('dbo.MyFirstMemporyOptimizedTable') AS VARCHAR(10)) + '.dll'
Here is the query in SSMS and the output:

image

Let’s look at windows explorer and see what we have in that folder. If we look at xtp folder, we would see numbered folders. These are databases IDs which have in-memory objects. Default directory of XTP folder is DATA directory (C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\). Do you remember what XTP stands for? Go back and read first post.

image

For database SQLSeverHelp_IMO the database_id is 8 (you may have different ID) so let’s go inside the folder 8 and check what we have got there.

image

If you go back and look at the query, which we executed earlier, its easy to understand that DLL (and other files) have name as xtp_t_<DB_ID>_<Object_ID> where t stands for table and rest is self explanatory. Each in-memory table will have one corresponding DLL generated which would be loaded in the memory at all times.The end goal of converting table definition to machine level instruction (using DLL) is to avoid compilation during execution phase. There is no need for a DBA to worry about backup of those files. They would be re-generated as soon as database is opened and recovered. Once SQL Server restarts, it performs recovery of database and table would be loaded into memory again.

CREATE PROCEDURE

Before moving any forward, remember that here we are talking about procedure which are natively compiled. In earlier post, we created stored procedure [InsertName]. During creation time of stored procedure, compiler will convert interpreted T-SQL, query plans and expressions into native code.

Here is the query

use SQLSeverHelp_IMO
go
SELECT name, 
       description 
FROM   sys.dm_os_loaded_modules 
WHERE  name LIKE '%xtp_p_' + Cast(Db_id() AS VARCHAR(10)) + '_'        
       + Cast(Object_id('dbo.InsertName') AS VARCHAR(10)) + '.dll' 
Here is the query in SSMS and the output:

image

Now, lets open the same XTP folder and look at content.

image

As we can see above, now we have new files having “p” in it and they are for stored procedure. The naming standard of those files is is xtp_p_<DB_ID>_<Object_ID>. Native Compilation of stored procedure takes an abstract tree representation of a stored procedure including queries, table and index metadata, and compiles them into native code designed to execute against tables and indexes managed by In-memory engine.

What are the other files (.c, .mat, .obj, .pdb) ? Well, they are intermediate files created by complier. They are used for troubleshooting compilation issues. Here is the quick summary table

 

File Extension

Usage

.c

C source file generated by In-Memory engine

.dll

Natively compiled DLL to be loaded into SQL Server process

.mat.xml

MAT export file

.obj

Object file generated by C compiler

.out

Compiler output file

.pdb

Symbol file for the dll. Used for debugging code issues.

 

If you are C++ developer, you can understand those files easily. Since they are not cause of worry for DBAs and Developers, I would not go deeper into explanation.

With this, I would conclude this blog post and hopefully this has given you some insights about inner working of In-Memory object.

Stay tuned for more..

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOnPaperback, Kindle

Advertisements

7 Responses to “A-Z of In-Memory OLTP : Behind the scenes”

  1. […] 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 […]

  2. […] Since both tables are memory optimized, both would have their own DLLs generated. We have discussed DLLs for table in earlier blog here. […]

  3. […] Let’s use XEvent to understand a concept. We have learned that DLLs of table is loaded as soon as database is started whereas the stored procedure DLL is loaded when its first executed. We have already discussed this concepts in Behind the Scenes […]

  4. […] My friend Balmukund explains this concept very well on his blog over here. […]

  5. […] c, dll, obj etc means. My friend Balmukund Lakhani explains that very well in the his blog post, however for the reference it is listed here once […]

  6. […] A-Z of In-Memory OLTP : Behind the scenes Link […]

  7. […] A-Z of In-Memory OLTP : Behind the scenes Link […]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: