Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,037,790 hits
  • Select GETDATE()

    March 2023
    M T W T F S S

Posts Tagged ‘CTP’

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
  • Advertisement

    Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , | 7 Comments »

    A-Z of In-Memory OLTP : Native Compilation

    Posted by blakhani on December 17, 2013

    Imagine that you are participating in a 400 meters running competition and you are the only one to get discount of 300 meters and allowed to start 100 meters before the finish line. What would be the result? All participants have to run 400 meters and you would run only 100 meters. Of course, You would win!!! (terms and condition apply). In the same way, what would happen in SQL world, if we finish a significant amount of work i.e. compilation of stored procedure during creation phase. Yes, you are correct. The time spent in execution of the stored procedure would be less. In this post we would try to learn internals of native compilation.

    In earlier “T-SQL Constructs” post of this series, we have seen syntax of creation of natively compiled stored procedure. Native compilation of the stored procedure causes machine language code (i.e. DLL) to be generated and loaded in SQL Server Memory. Natively compiled stored procedures allows us to execute T-SQL in the fastest way, which includes accessing data in memory-optimized tables. In addition, disk-based tables are not allowed to be accessed at all inside natively compiled stored procedures. If we attempt to create a natively compiled procedure which accesses disk based table, we would get below error. In my test, the table name was DiskBasedTable.

    Msg 10775, Level 16, State 1, Procedure InsertName, Line 14
    Object ‘dbo.DiskBasedTable’ is not a memory optimized table and cannot be accessed from a natively compiled stored procedure.

    The advantages of natively complies stored procedure is that most of the work (permission check, object existence check, query plan generation) is done during compilation/creation of stored procedure. Due to this there are many limitations on the Transact-SQL that is allowed, the data types and collations that can be accessed and processed in natively compiled procedures. SQL Server Product Documentation team has done good amount of work in documenting them (refer Supported Data Types, Unsupported Transact-SQL Constructs)

    To get a list of natively complied stored procedures we can use catalog views as shown below. Highlighted below is the new column added to support in-memory OLTP.

    SELECT uses_native_compilation, 
    FROM   sys.sql_modules 
    WHERE  uses_native_compilation = 1 
    SELECT uses_native_compilation, 
    FROM   sys.all_sql_modules 
    WHERE  uses_native_compilation = 1 


    Here is the output in SSMS


    To convert a stored procedure to DLL, SQL Server engine uses C compiler. Does this mean we need to have C installed on the machine when we install in-memory OLTP? Well, we just need to complie the C code so we just need compiler related files. SQL Server product comes with compiler files. As soon as SQL Engine is installed, these files are installed under <SQL Server Installation Directory>\MSSQL\Binn\Xtp folder. Main compilation files are located under ..\VC\bin folder.


    Sometimes, we might face stored procedure creation failure due to issues in compilation of the C code. To make our life easier, SQL Server generates intermediate files and they are useful for troubleshooting by members of product support team. Here is the text of .out file.

    Microsoft (R) C/C++ Optimizing Compiler Version 17.00.60605.1 for x64

    Copyright (C) Microsoft Corporation.  All rights reserved.


    Microsoft (R) Incremental Linker Version 11.00.60605.1

    Copyright (C) Microsoft Corporation.  All rights reserved.






    "/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\gen\lib"

    "/LIBPATH:C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Xtp\VC\lib"















    Generating code

    Finished generating code

    If you are a C developer, it may be easy for you to understand the switches passed during calling of CL.exe. There is no real need for a DBA to know all these options. In case you are too excited, read here for the parameters.

    Before I conclude, I would like you to throw ideas about below error, while creation of natively compiled stored procedure.


    Here is the text of the message:

    Msg 41312, Level 16, State 4, Procedure InsertName1, Line 2

    Unable to call into the C compiler. GetLastError = 2.

    Of course, I broke something and above is the error I am getting. Can you guess? Go ahead and comment on the post.

    Stay Tuned for next part of the series.


    Balmukund Lakhani

    Twitter @blakhani

    Author: SQL Server 2012 AlwaysOnPaperback, Kindle

    Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP | Tagged: , , , , , , | 1 Comment »

    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.


    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
    Select * from sys.data_spaces
    Here is the query in SSMS and the output:


    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
    SELECT name, 
    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.


    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
    SELECT name, 
    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


    File Extension



    C source file generated by In-Memory engine


    Natively compiled DLL to be loaded into SQL Server process


    MAT export file


    Object file generated by C compiler


    Compiler output file


    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..


    Balmukund Lakhani

    Twitter @blakhani

    Author: SQL Server 2012 AlwaysOnPaperback, Kindle

    Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, Internals, SQL Server 2014, SSMS | Tagged: , , , , , , | 7 Comments »