Help: SQL Server

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.

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:

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.

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:

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.

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.

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:

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

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

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 AlwaysOn – Paperback, Kindle