In my previous posts I talked about:

1. What’s [new with SQL Server 2014 & Hekaton] and CTP-1 [download link].

2. [Installing] SQL Server 2014 with Sneak-Peek.

3. Working with [Hekaton] Tables.

Here, in this post I’ll discuss more on what the new SQL Server 2014 does behind the scene while you create Hekaton or Memory-Optimized Tables & Native Compiled Stored Procedures.

I will use the same Database (Hekaton enabled) created in my [previous post], and then we will check what SQL Server does while creating Hekaton tables & Compiled SPs:

–> Create Memory-Optimized Table:

USE [ManTest] GO CREATE TABLE dbo.Test_memoryOptimizedTable ( TestID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024), TestName NVARCHAR(100) NOT NULL, DateAdded DATETIME NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO

–> Create Native Compiled Stored Procedure:

USE [ManTest] GO CREATE PROCEDURE dbo.Test_NativelyCompiledStoredProcedure ( @param1 INT not null, @param2 NVARCHAR(100) not null ) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) INSERT dbo.Test_memoryOptimizedTable VALUES (@param1, @param2, getdate()) END GO

–> After executing above code as usual the Tables & Stored Procedure will be created. But the important thing here is what the Hekaton Engine does internally, is shown in the following image below:



– It creates total 6 files for every Table & SP with following extensions: .c, .dll, .mat, .obj, .out and .pdb.

– Most important are the C Code and the DLL files with four (4) other supporting files for each Table the Stored Procedure and stores them at following path: “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xtp\5\”.

– The “xtp” folder/directory here contains a sub-folder “5” which is nothing but the Database ID, check this:

SELECT DB_ID(); SELECT object_id, name, type FROM sys.sysobjects WHERE name IN ('Test_memoryOptimizedTable', 'Test_NativelyCompiledStoredProcedure');

– If you look closely the file are create with particular naming conventions and relate to the results of above query:

— For files xtp_t_5_277576027: xtp_t is for Table, 5 is the Database ID and 277576027 is the Object (table) ID.

— For files xtp_p_5_325576198: xtp_p is for Stored Procedure, 5 is the Database ID and 325576198 is the Object (Stored Procedure) ID.

–> Opening the xtp_t_5_277576027.c file looks like this:

#define __in #define __out #define __inout #define __in_opt #define __out_opt #define __inout_opt #define __in_ecount(x) #define __out_ecount(x) #define __deref_out_ecount(x) #define __inout_ecount(x) #define __in_bcount(x) #define __out_bcount(x) #define __deref_out_bcount(x) #define __deref_out_range(x, y) #define __success(x) #define __inout_bcount(x) #define __deref_opt_out #define __deref_out #define __checkReturn #define __callback #define __nullterminated typedef unsigned char bool; typedef unsigned short wchar_t; typedef long HRESULT; typedef unsigned __int64 ULONG_PTR; #include "hkenggen.h" #include "hkrtgen.h" #include "hkgenlib.h" #define ENABLE_INTSAFE_SIGNED_FUNCTIONS #include "intsafe.h" int _fltused = 0; int memcmp(const void*, const void*, size_t); void *memcpy(void*, const void*, size_t); void *memset(void*, int, size_t); #define offsetof(s,f) ((size_t)&(((s*)0)->f)) struct hkt_277576027 { __int64 hkc_3; long hkc_1; unsigned short hkvdo[2]; }; struct hkis_27757602700002 { long hkc_1; }; struct hkif_27757602700002 { long hkc_1; }; unsigned short GetSerializeSize_277576027( struct HkRow const* hkRow) { struct hkt_277576027 const* row = ((struct hkt_277576027 const*)hkRow); return ((row->hkvdo)[1]); } HRESULT Serialize_277576027( struct HkRow const* hkRow, unsigned char* buffer, unsigned short bufferSize, unsigned short* copySize) { return (RowSerialize(hkRow, (GetSerializeSize_277576027(hkRow)), buffer, bufferSize, copySize)); } HRESULT Deserialize_277576027( struct HkTransaction* tx, struct HkTable* table, unsigned char const* data, unsigned short datasize, struct HkRow** hkrow) { return (RowDeserialize(tx, table, data, datasize, sizeof(struct hkt_277576027), (sizeof(struct hkt_277576027) + 200), hkrow)); } unsigned short GetSerializeRecKeySize_277576027( struct HkRow const* hkRow) { struct hkt_277576027 const* row = ((struct hkt_277576027 const*)hkRow); unsigned short size = sizeof(struct hkif_27757602700002); return size; } HRESULT SerializeRecKey_27757602700002( struct HkRow const* hkRow, unsigned char* hkKey, unsigned short bufferSize, unsigned short* keySize) { struct hkt_277576027 const* row = ((struct hkt_277576027 const*)hkRow); struct hkif_27757602700002* key = ((struct hkif_27757602700002*)hkKey); (*keySize) = sizeof(struct hkif_27757602700002); if ((bufferSize < (*keySize))) { return -2013265920; } (key->hkc_1) = (row->hkc_1); return 0; } HRESULT DeserializeRecKey_277576027( unsigned char const* data, unsigned short dataSize, struct HkSearchKey* key, unsigned short bufferSize) { struct hkif_27757602700002 const* source = ((struct hkif_27757602700002 const*)data); struct hkis_27757602700002* target = ((struct hkis_27757602700002*)key); unsigned long targetSize = sizeof(struct hkis_27757602700002); if ((targetSize > bufferSize)) { return -2013265920; } (target->hkc_1) = (source->hkc_1); return 0; } __int64 CompareSKeyToRow_27757602700002( struct HkSearchKey const* hkArg0, struct HkRow const* hkArg1) { struct hkis_27757602700002* arg0 = ((struct hkis_27757602700002*)hkArg0); struct hkt_277576027* arg1 = ((struct hkt_277576027*)hkArg1); __int64 ret; ret = (CompareKeys_int((arg0->hkc_1), (arg1->hkc_1))); return ret; } __int64 CompareRowToRow_27757602700002( struct HkRow const* hkArg0, struct HkRow const* hkArg1) { struct hkt_277576027* arg0 = ((struct hkt_277576027*)hkArg0); struct hkt_277576027* arg1 = ((struct hkt_277576027*)hkArg1); __int64 ret; ret = (CompareKeys_int((arg0->hkc_1), (arg1->hkc_1))); return ret; } unsigned long ComputeSKeyHash_27757602700002( struct HkSearchKey const* hkArg) { struct hkis_27757602700002* arg = ((struct hkis_27757602700002*)hkArg); unsigned long hashState = 0; unsigned long hashValue = 0; hashValue = (ComputeHash_int((arg->hkc_1), (&hashState))); return hashValue; } unsigned long ComputeRowHash_27757602700002( struct HkRow const* hkArg) { struct hkt_277576027* arg = ((struct hkt_277576027*)hkArg); unsigned long hashState = 0; unsigned long hashValue = 0; hashValue = (ComputeHash_int((arg->hkc_1), (&hashState))); return hashValue; } struct HkOffsetInfo const KeyOffsetArray_27757602700002[] = { { offsetof(struct hkis_27757602700002, hkc_1), 0, 0, }, }; struct HkKeyColsInfo const KeyColsInfoArray_277576027[] = { { sizeof(struct hkis_27757602700002), KeyOffsetArray_27757602700002, sizeof(struct hkis_27757602700002), sizeof(struct hkis_27757602700002), }, }; struct HkOffsetInfo const OffsetArray_277576027[] = { { offsetof(struct hkt_277576027, hkc_1), 0, 0, }, { (offsetof(struct hkt_277576027, hkvdo) + 0), 0, 0, }, { offsetof(struct hkt_277576027, hkc_3), 0, 0, }, }; struct HkColsInfo const ColsInfo_277576027 = { sizeof(struct hkt_277576027), OffsetArray_277576027, KeyColsInfoArray_277576027, }; struct HkHashIndexMD HashIndexMD_277576027[] = { { 2, 1, 1024, CompareSKeyToRow_27757602700002, CompareRowToRow_27757602700002, ComputeSKeyHash_27757602700002, ComputeRowHash_27757602700002, }, }; struct HkTableMD TableMD = { sizeof(struct hkt_277576027), (sizeof(struct hkt_277576027) + 200), 1, HashIndexMD_277576027, 0, 0, 0, (&ColsInfo_277576027), 277576027, 0, GetSerializeSize_277576027, Serialize_277576027, Deserialize_277576027, GetSerializeRecKeySize_277576027, SerializeRecKey_27757602700002, DeserializeRecKey_277576027, }; __declspec(dllexport) struct HkTableBindings g_Bindings = { 277576027, (&TableMD), };

I cannot understand a single bit here, but this recalls memories when I was studying C & C++ in college 🙂

–> Final Cleanup

DROP PROCEDURE dbo.Test_NativelyCompiledStoredProcedure DROP TABLE dbo.Test_memoryOptimizedTable

Update: Know more about In-Memory tables:



