Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,822 hits
  • Select GETDATE()

    December 2013
    M T W T F S S

Archive for December, 2013

A-Z of In-Memory OLTP : Checkpoint – Part 1

Posted by blakhani on December 19, 2013

In today’s advance world, RAM is still volatile. Once you pull the power plug out, all the content of RAM is flushed. Since we are talking about in-memory tables, what would happen to data in tables? If we have defined a table as durable (SCHEMA_AND_DATA) in SQL Server, durability has to be maintained. During recovery of the database (also known as crash recovery), all the content should come back. To achieve this, SQL Server does logging into transaction log and writes checkpoint for in-memory tables.

If you are a SQL Server DBA and worked with earlier versions of SQL, checkpoint is definitely known to you. You can read more about earlier version of checkpoint in books online. In short, checkpoint is a process which writes dirty pages from memory to data file. This is done to reduce the recovery time of the database during crash recovery. Checkpoint can be done by system thread based on recovery interval or triggered on demand when system is low on memory, or other operations such as log backup etc.

“Similar” concept of checkpoint exists for In-Memory tables as well. I quoted similar because mechanism is different than the known checkpoint process for disk-based tables. To maintain durability of data for in-memory tables, SQL Server does logging of changes in transaction log (which is also different than disk based tables) and writes information during checkpoint. In this blog, we would try to understand the various things which happens during checkpoint. We should remember that transaction logging is sufficient to recover the data but if there is no checkpoint then we need to read complete transaction log during crash recovery, which would be inefficient. So, periodically checkpoint is fired and data is dumped into checkpoint files. Now, during recovery, we recover from checkpoint and replay only that portion of transaction log which is after checkpoint.

Checkpoint files are created into filegroup MEMORY_OPTIMIZED_DATA which is container for these files. During checkpoint, we try to use log buffer in memory to write information into checkpoint files. There are chances that log buffer is not having sufficient log blocks and then we would end-up in reading transaction log file. SQL Server uses FILESTREAM as underlying storage mechanism for checkpoint files. The checkpoint information is written into the disk by checkpoint streams, which comes in two flavors:

  • DATA streams:
    • These file are approximately 128 MB in size.
    • They contain only inserted rows of the table in the given timestamp range.
  • DELTA streams:
    • These are paired (1:1) with DATA files.
    • Size of these file is not constant.
    • Store the deleted rows with IDs.

Note that both files are “append only” files which means the IO would always be sequential. As soon as table is created, DATA files are pre-allocated with 128 MB size. DATA and DELTA files form a pair for a timestamp range. Once current DATA file becomes full, SQL Server would switch to new DATA file.

Enough of theory? Let’s see a checkpoint in action.

For demo purpose, I am creating table in the database which is having wide row. Here is the complete script.

-- Create database having container C:\IMO_Database\SQLSeverHelp_IMO_dir
    NAME = [SQLSeverHelp_IMO_data]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
    NAME = [SQLSeverHelp_IMO_dir]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
    LOG ON (
    NAME = [SQLSeverHelp_IMO_log]
    ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'

-- Create In-Memory table
Use SQLSeverHelp_IMO
CREATE TABLE MyFirstMemporyOptimizedTable
vFName CHAR(4000) NOT NULL,
vLName CHAR(4000) NOT NULL

As soon as table is created, we would see DATA and DELTA file in the container. We can use DMV sys.dm_db_xtp_checkpoint_files to see the information. Let’s first run a very basic query to understand the files.

SELECT file_id, 
FROM   sys.dm_db_xtp_checkpoint_files 
ORDER  BY is_active DESC;   

Here is the output with some highlights. I have put the same color for the matching file_id with pair_file_id column for clarity.


As we can see in output, DATA and DELTA files are having pair. I have highlighted only two pairs but if you look carefully, all files are paired. Note that the number of files created is a function of number of CPU cores on the system. My machine has 8 logical CPU so I am seeing 9 pairs. Is_Active column represents whether the files is currently in use or not. Another way to find out paired files is to use column called internal_storage_slot. Only those files which are having not null slot would be used by checkpoint.

SELECT internal_storage_slot,
FROM   sys.dm_db_xtp_checkpoint_files 
WHERE  internal_storage_slot is NOT NULL

Here is the output. Two files having same slot form a pair of DATA-DELTA (slot 3 in below screenshot).


Here is how the physical files on operating system look like:


Now, you may ask – how would I associate the file in the OS with the file_id and pair_file_Id column in sys.dm_db_xtp_checkpoint_files? Let’s use other columns of DMV to explore that information.


I have tried to make the picture self-explanatory. Highlight with the same color are the matching texts.

Now, lets try and check what happens when we insert some rows in the table. As we discussed earlier, DATA file would contain the rows which are inserted and DELTA file would have entries of deleted rows. I have used below script to load ~10K rows into the table.

DECLARE @counter INT 
SET @counter = 0 
WHILE @counter <= 10000 
      INSERT INTO myfirstmemporyoptimizedtable 
      VALUES      (@counter, 
                   'Balmukund Lakhani'), 
                  (@counter + 1, 
                   'Vinod Kumar M'), 
                  (@counter + 2, 
                   'Pinal Dave') 

      SET @counter = @counter + 3 

Now, Let’s go ahead and query the DMV again to see if we have something interesting! But Wait! We don’t know if checkpoint has automatically fired or not. So before running the query, I fired manual checkpoint via “CHECKPOINT” command in management studio.

SELECT internal_storage_slot,
       file_size_in_bytes / 1024.0 / 1024.0      [File Size in MB], 
       file_size_used_in_bytes / 1024.0 / 1024.0 [File Used in MB], 
       total_bytes / 1024.0 / 1024.0             [Total MB], 
 FROM   sys.dm_db_xtp_checkpoint_files 
 WHERE  total_bytes > 0        -- non zero size
 and       internal_storage_slot is not null 

As we can see that at this point only one DATA file is having non-zero size and number of rows shown are 10002, which were inserted earlier.


As we insert more and more rows, data would be accumulated in DATA file and once its full, next file would be opened used and current file becomes read-only. Please remember that 128 MB is NOT the limit of the checkpoint DATA file. A transaction can’t span across two DATA files, so a bigger transaction could cause a file to go beyond 128 MB size. For demo purposes, instead of 10K rows, I have inserted ~40K rows in one transaction.


If you are wondering why we are not seeing any DELTA file in any of the query output then recall that DELTA file would contain the DELETEed rows identifier and so far I have been doing only INSERTs. To give you some time to grasp information in this blog, I would stop here and we would discuss other aspects of checkpoint in next blog for this A-Z series.

In case you have any question, post them in comment section and I would be happy to answer them.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisement

    Posted in Hekaton Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , | 11 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 »

    Did you know? You can generate Insert Statement with data using Management Studio!!!

    Posted by blakhani on December 13, 2013

    I must confess that at least I didn’t know about that option till I saw an internal email thread. And my first reaction was – Damn! all these years and never spotted that! Yes, we can get all of the INSERT statements for the data straight out of SSMS. Another hidden feature of SSMS.

    Earlier, I used to search on internet and get some stored procedure to get the insert statements generated for me. Here were few search strings which used to give me proper results “SQL Generate Insert Script”, “SQL Server Generate Insert Script”, "SQL Server Script Insert Statements", "Create Insert Script SQL Server", "Generate Insert Script from Table"

    Now I can avoid all those searches and do that right from the Management Studio. For demo purpose I would create a small database and show that option. Please note that I am using SQL Server 2012 Management Studio (SSMS 2012)

    Use myAppDB
    Create table HelloSQL (iID int, vFname varchar(100), vLName varchar(100))
    insert into HelloSQL values 
    (1, 'Balmukund', 'Lakhani'),
    (2, 'Vinod', 'Kumar M'),
    (3, 'Pinal', 'Dave'),
    (4, 'Sourabh', 'Agarwal'),
    (5, 'Amit', 'Banerjee')
    Select * from HelloSQL


    To achieve our insert statements with script use Object Explorer and connect to an instance of the SQL Server. Expand Databases folder, Choose the database for which we want the script, right click, Tasks > Generate Scripts.

    Once Wizard is launched, its self-explanatory to navigate through screen.


    After welcome screen, we need to choose the object (table HelloSQL in our case) for which we want script to be generated.


    In next screen, we need to choose the destination of script. I have selected “Save to new query window”


    In the same screen, we have a magical button called “Advanced”. That’s where we have many options about scripting.


    I have selected “Schema and Data” under “Type of data to script” and hit OK. Hit Next on previous screen


    hit Next and Finish.


    … and we have what we needed. Schema creation as well as Insert Statement for the data in table.


    There are many other options in advanced tab. Play with them and get the desired script. I have learned this and it was new to me. Hope this is useful to you also. SQL Server generate script with data is a powerful SQL Server tool in order to create SQL script to move data from one database to another database. SQL programmers can export data as sql script by using one of the existing SQL Server tools, SQL Server Generate Script Wizard.



    Balmukund Lakhani

    Twitter @blakhani

    Author: SQL Server 2012 AlwaysOnPaperback, Kindle

    Posted in Screenshot, SQL Server, SQL Server Management Studio, SSMS, Step by Step | Tagged: , , , , , , , | 9 Comments »