In this Article we will go-over on the following topics with extensive list of examples:

• How To Create In-Memory OLTP (a.k.a. Hekaton) Enabled Database and it’s Internals.
• CHECKPOINT Files And It’s Internals
• How To Create Memory Optimized Tables and it’s Internals.
• How To Create Natively Compiled Stored Procedures and it’s Internals.

This is the third article in the series of the articles on the topic “In-Memory OLTP (a.k.a.HEKATON a.k.a. eXtreme Transaction Processing) and its Internals – Sql Server 2014”. I request you to go through the other two articles listed below prior to this article

• Introduction to In-Memory OLTP (a.k.a. Hekaton) – Sql Server 2014
• Myths and Facts about In-Memory OLTP (a.k.a. Hekaton) – Sql Server 2014

Note: The learning experience expressed in this article are based on the Sql Server 2014-CTP2.

How To Create In-Memory OLTP (a.k.a. Hekaton) Enabled Database and it’s Internals

To support In-Memory OLTP features (i.e. Memory Resident Tables, Natively compiled Tables and Stored Procedures etc), the Database need to have MEMORY_OPTIMIZED_DATA filegroup. We can create only one filegroup per db which can have MEMORY_OPTIMIZED_DATA. This Memory Optimized filegroup can have multiple containers/folders/volumes. Each of these containers will have Durable Memory Optimized Tables Checkpoint files (i.e. Data and Delta file pairs) containing copy of the Memory Optimized Table Data on the disk.

Below is the script to Create a Brand New In-Memory OLTP (a.ka. Hekaton) Enabled Database

CREATE DATABASE SqlHintsXTPDemo
ON PRIMARY(NAME = SqlHints_DATA, 
  FILENAME = 'C:\XTPDBS\SqlHints_Data.mdf'),
FILEGROUP SqlHints_XTP_FG CONTAINS MEMORY_OPTIMIZED_DATA
	(NAME = SqlHints_XTP_CHKPOINT, 
		FILENAME = 'C:\XTPDBS\SqlHints_XTP_CHKPOINT')
LOG ON (NAME = SqlHints_LOG, 
                FILENAME='C:\XTPDBS\SqlHints_Log.ldf')

Immediately after the Database creation the Checkpoint Container folder in this example “C:\XTPDBS\SqlHints_XTP_CHKPOINT” and it’s sub-folders will be empty as shown below:

After few Minutes of Database Creation or Immediately after Creating the Memory Optimized Table. If we revisit the Checkpoint Container folder, we will see File Stream File’s as shown below.

Already Have a Regular Database? Want to Make them In-Memory OLTP Enabled?

Below script demonstrates this converting  Regular DataBase to a DataBase which can have Memory Optimized Tables.

--CREATE CLASSIC REGULAR DATABASE
CREATE DATABASE SqlHintsXTP
ON PRIMARY(NAME = SqlHints_DATA, 
	FILENAME = 'C:\XTPDBS\SqlHints_Data.mdf')
LOG ON (NAME = SqlHints_LOG, 
	FILENAME='C:\XTPDBS\SqlHints_Log.ldf')
GO
--ADD MEMORY_OPTIMIZED_DATA File Group to the DB 
ALTER DATABASE SqlHintsXTP 
ADD FILEGROUP SqlHints_XTP_FG CONTAINS MEMORY_OPTIMIZED_DATA
GO
--ADD FILEGROUP CONTAINER FOLDER WHICH will have the 
--MEMORY OPTIMIZED TABLES 
--CHECKPOINT FILES(i.e. DATA and DELTA File Pairs)
ALTER DATABASE SqlHintsXTP
ADD FILE(NAME = SqlHints_XTP_CHKPOINT, 
FILENAME = 'C:\XTPDBS\SqlHints_XTP_CHKPOINT') 
TO FILEGROUP SqlHints_XTP_FG

Creating Memory Optimized Database with Multiple Containers for the Memory Optimized Table’s Checkpoint Files

Script:

CREATE DATABASE SqlHintsXTP
	ON PRIMARY(NAME = SqlHints_DATA, 
		FILENAME = 'C:\XTPDBS\SqlHints_Data.mdf'),
	FILEGROUP SqlHints_XTP_FG CONTAINS MEMORY_OPTIMIZED_DATA
		(NAME = SqlHints_XTP_CHKPOINT1, 
			FILENAME = 'C:\XTPDBS\SqlHints_XTP_CHKPOINT1'),
		(NAME = SqlHints_XTP_CHKPOINT2, 
			FILENAME = 'D:\XTPDBS\SqlHints_XTP_CHKPOINT2')
	LOG ON (NAME = SqlHints_LOG, 
		FILENAME='C:\XTPDBS\SqlHints_Log.ldf')

Physical Folder’s:

Points to Note About In-Memory OLTP Enabled Databases:

• Memory Optimized Databases doesn’t support Database Mirroring and Replication. But it’s alternative Always-ON Availability Component is supported.
• It also doesn’t support differential back-up, but it supports Full and log backup and restore.
• Sql Server 2014 CTP2 can support max 4096 Data/Delta files per In-Memory OLTP database. Combined maximum size of the Durable Memory optimized table data supported by Sql Server is 256 GB.

CHECKPOINT Files And It’s Internals

As Memory is Volatile, the Server restart will cause flushing of the Memory Optimized Table data from Memory. In order to provide Durability of the Data for Memory Optimized Tables created with durability option, the In-Memory OLTP engine writes the every committed transaction data to the Transaction log. And whenever CHECKPOINT fires, it will move the data written from the Transaction Log to the Data and Delta filestream file Pairs in the Checkpoint Container. On System restart the data from these check points file’s and the active transaction file is used to rebuild the Memory Optimized table and stream it back to the Main Memory.

Data Check Point File:

• Data File’s holds the Durable Memory Optimized Table’s Data (i.e. Inserted Rows only).
• Initially the No. of Data Files Created will be equal to the No. of Cores of the DB Server.
• Approximate Size will be 128 MB

Delta Check Point file:

• Delta File’s: For each Data File there will be a corresponding Delta file
• It will have the ID’s of the Deleted Rows. It will have only the Id’s of the deleted records which are present in the corresponding Data

Checkpoint Meta data and it’s Mapping to physical Checkpoint files on the disk:

Below image depicts the Checkpoint Files Meta data maintained by Sql Server and it’s Mapping to the Physical checkpoint files on the disk. DMV sys.dm_db_xtp_checkpoint_files can used to get the Checkpoint files Meta data maintained by the Sql Server.

How To Create Memory Optimized Tables and it’s Internals.

A database can have Memory Optimized Table’s, only if it is In-Memory OLTP Enabled (i.e. Database need to have MEMORY_OPTIMIZED_DATA filegroup). A database can have both Regular Tables (i.e. Disk-based Tables) and Memory Optimized tables.

Below is the script for Creating a Durable Memory Optimized Table.

CREATE TABLE dbo.Customer
(
  CustomerId INT NOT NULL PRIMARY KEY NONCLUSTERED 
          HASH WITH (BUCKET_COUNT = 1000000),
  FirstName NVARCHAR(50) NOT NULL,
  LastName  NVARCHAR(50) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA)

• MEMORY_OPTIMIZED: This Parameter value ON indicates that the Table is Memory Optimized (i.e. Memory Resident Table).

• DURABILITY: This Parameter Value SCHEMA_AND_DATA means Table Data is Durable and if it’s value is SCHEMA_ONLY means only Table Schema is Durable and Data is NOT Durable.

• NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000): HASH index. Here choosing BUCKET_COUNT is very important, it should be equal to or greater than the possible number of unique values for the index key. It will degrade the performance if this BUCKET_COUNT is less than the number of unique values for the index key. We will discuss on this more in the future articles on the HASH and RANGE Indexes.

Restrictions/Limitations for Memory Optimized Tables:

• LOB Data Types, XML, CLR and MAX Data Types are Not Supported
• NO ALTER TABLE and CREATE, ALTER, DROP INDEX
• Foreign Key and Check Constraints are Not Supported
• Identity Columns are Not Supported
• No Unique indexes other than for the Primary Key Column
• Table Row Max Size 8060 bytes, it is enforced during Table Creation as-well.
• DML Triggers are Not Supported for Memory Optimized Table
• Memory Optimized Table need’s to have at least one Index.Memory Optimized Tables doesn’t use 8 KB Data Pages from the extent to store the table data, Instead has a Special Row Structure and Index’s are used to link the rows to get the complete Table Data.
• Supported Index Types are Non-Clustered HASH or RANGE Indexes.
• A Table Can Have Max 8 Indexes

Native Compilation of Memory Optimized Tables

Below image depicts the Step’s performed by In-Memory OLTP engine whenever the Memory Optimized Table is created

During Memory Optimized Table creation the In-Memory OLTP engine compiles the Memory Optimized Table programming constructs (i.e. SELECT/UPDATE/DELETE FROM..) to Native code (i.e. The code that doesn’t require further compilation, CPU can directly execute these processor instructions). Basically, it provides the faster access to the Memory Optimized Table by providing routines for directly accessing the Table data. Whenever we create a memory optimized table the In-Memory OLTP engine will perform the following steps:

• Stores the Tables Meta Data for providing SCHEMA Durability (i.e. On System Restart this Meta data is used by the Sql Server to regenerate the Memory Optimized Table dll)
• Generates the C-Code for the Memory Optimized Table and it
• Uses the Visual C compiler (Shipped with Sql Server 2014) to compile and generate the Native code (i.e. Produces the table dll).
• Generated Table dll will be loaded to the SqlServer.exe’s process

DMV sys.dm_os_loaded_modules can be used to get the currently loaded Natively compiled Tables or Stored Procedures into the SqlServer.exe process.

--DMV to get the list of all the Tables and Stored Procedure's 
--dll's which are loaded in the Sql Server's Process Memory
SELECT Name, Description 
FROM   sys.dm_os_loaded_modules 
WHERE  Description = 'XTP Native DLL'

We can use query like below to get the list of all the Memory Optimized Tables in the current database. Note the Durability column value “0” Means both Schema and Data of the table are durable where as value “1” means only the Schema is durable.

USE SqlHintsXTPDemo
GO
SELECT Name, durability_desc, durability
FROM sys.tables 
WHERE is_memory_optimized = 1

How To Create Natively Compiled Stored Procedures and it’s Internals.

Below is the script for creating a simple Natively Compiled Stored Procedure. A Natively compiled Stored Procedure can Access Memory Optimized Tables but not the disk based tables. Because the disk based table’s are not Natively Compiled like Memory Optimized Table and also they don’t provide the routines to access the tables directly like the Memory Optimized Tables.

CREATE PROCEDURE dbo.AddCustomer
(
	@CustomerId INT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
WITH NATIVE_COMPILATION
        ,SCHEMABINDING
        ,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT ,
				LANGUAGE = 'us_english' )

    INSERT INTO dbo.Customer
    VALUES (@CustomerId, @FirstName, @LastName)
END

WHERE

• NATIVE_COMPILATION: Conveys to Sql Server that this stored Procedure Needs to be Compiled to Native Code during it’s creation.
• SCHEMABINDING: This block’s dropping of the Tables referenced by the Stored Procedure
• EXECUTE AS OWNER: The context in which the Stored Procedure Should Execute. It can be EXECUTE AS OWNER, EXECUTE AS user and EXECUTE AS SELF
• BEGIN ATOMIC: Guarantees the Atomic Execution (Implicit Transaction) of the Stored Procedure (i.e. Either All Statement’s will succeed or fail together)
• WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,  LANGUAGE = ‘us_english’ ) 
  TRANSACTION ISOLATION LEVEL: Transaction Isolation Level used for the Atomic Execution of the Stored Procedure.
  LANGUAGE: Language used for the DateTime Formats and System Messages in the Stored Procedure

When we issue a command to Sql Server to Create a Natively Compiled Stored Procedure. The Sql Server engine’s Parser, Algebrizer and Query Optimizer create Processing Flow, Query Tree for the T-Sql Statement’s in the Stored procedure and then an optimized execution plan is created for all the queries in the Stored Procedure. Then In-Memory OLTP engine Takes this as an input and generates C code and then by using the Visual C compiler the C code is compiled to a native code. The Generated dll will be loaded into the Sql Server’s Process Memory.

And when user Executes the stored procedure (i.e. issue ‘s an EXEC) command then first the Sql Server Engine’s Parser & Algebrizer extracts the stored procedure Name and the Parameters and if it identifies it as a natively compiled Stored Procedure then the request is passed to the In-Memory OLTP engine which locates the Stored Procedures Dll entry point. And the dll  executes Stored Procedure logic and returns the result.

 

Please correct me, if my understanding is wrong. Comments and suggestions are always welcome.