Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

A-Z of In-Memory OLTP : Performance Tip for Placement of Data and Delta Files (Checkpoint Files)

Posted by blakhani on April 29, 2014


While preparing my session about In-Memory OLTP for internal audience, I learned something new about placement of checkpoint files. In case you don’t know about checkpoint files, I would like you to read about checkpoint (part 1 and part 2).

Data files and Delta files are created in round robin fashion. Let’s assume that we have two drives W and X and we want to distribute the IO load of In-Memory table. In general, for disk based table, what we used to do? Create the file on each drive? Yes, that would work for disk bases table but there is a little behavior which we should know before doing such thing for in-memory tables.

For demo purpose, I am going to use two folder on same drive to mimic two drives.

USE master
GO
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W'
    ,filename = 'c:\dataHK\ObjectsDemo_W'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X'
    ,filename = 'c:\dataHK\ObjectsDemo_X'
    ) TO filegroup ObjectsDemo_mod
GO

So, I have two folders “ObjectsDemo_W” and “ObjectsDemo_X” to represent W and X drives. Once we create a in-memory table, we should see checkpoint file pairs (CFP) created. 
USE ObjectsDemo
GO

CREATE TABLE dbo.t1 (
    c1 INT NOT NULL PRIMARY KEY NONCLUSTERED
    ,c2 INT
    )
    WITH (memory_optimized = ON)
GO

Let’s have a look at folders now.

image

One folder (Drive W) contains only data files (pre-created at 16 MB each) and another folder (Drive X) contains only delta file (pre-created at 1 MB each). That would not put uniform load on the both folders. The files are placed in this fashion because data and delta files are created in container into round-robin fashion

Here is the tip: Since we have even number of drives, we should create two folders on each drive and place files in 4 containers instead of 2 containers. We need to remember that first two containers should be from same drive.

use master
go
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W1'
    ,filename = 'c:\dataHK\ObjectsDemo_W1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W2'
    ,filename = 'c:\dataHK\ObjectsDemo_W2'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X1'
    ,filename = 'c:\dataHK\ObjectsDemo_X1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X2'
    ,filename = 'c:\dataHK\ObjectsDemo_X2'
    ) TO filegroup ObjectsDemo_mod
GO


W1 and W2 are two container for mimicking two folder on W and same for X as well. Now, if we create a table, due to round robin, we should see below.

image

Now we have DATA file distributed and should have better usage of both driver. What you should do if you have ODD number of drives? Well, nothing because of round robin, data and delta files would be distributed automatically. 

Bottom line: If you have plans to create file stream containers on odd number of drives, create them using trick mentioned above. If you have even number of containers then no special consideration.

Hope you have learned something new today.

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

    2 Responses to “A-Z of In-Memory OLTP : Performance Tip for Placement of Data and Delta Files (Checkpoint Files)”

    1. karthik said

      Thank you sir

    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: