Archive

Archive for September, 2013

Behind the scenes with Hekaton Tables & Native Compiled SPs | SQL Server 2014

September 25, 2013 1 comment

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:
SQLServer2014_C_Code_DLL

– 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:


 


Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012

September 20, 2013 6 comments

I was working on a legacy T-SQL script written initially on SQL Server 2005 and I was facing an unexpected behavior. The code was giving me unexpected records, I tried to dig into it and found that ISNUMERIC() function applied to a column was giving me extra records with value like “,” (comma) & “.” (period).

–> So, to validate it I executed following code and found that ISNUMERIC() function also passes these characters as numbers:

SELECT 
	 ISNUMERIC('123') as '123'
	,ISNUMERIC('.') as '.' --Period
	,ISNUMERIC(',') as ',' --Comma

Function ISNUMERIC() returns “1” when the input expression evaluates to a valid numeric data type; otherwise it returns “0”. But the above query will return value “1” for all 3 column values, validating them as numeric values, but that’s not correct for last 2 columns.

–> And not only this, ISNUMERIC() function treats few more characters as numeric, like: – (minus), + (plus), $ (dollar), \ (back slash), check this:

SELECT 
	 ISNUMERIC('123') as '123'
	,ISNUMERIC('abc') as 'abc'
	,ISNUMERIC('-') as '-'
	,ISNUMERIC('+') as '+'
	,ISNUMERIC('$') as '$'
	,ISNUMERIC('.') as '.'
	,ISNUMERIC(',') as ','
	,ISNUMERIC('\') as '\'

This will return “0” for second column containing value “abc”, and value “1” for rest of the column values.

So, you will need to be very careful while using ISNUMERIC() function and have to consider all these possible validations on your T-SQL logic.

– OR –

Switch to new TRY_PARSE() function introduced in SQL Server 2012.

–> The TRY_PARSE() function returns the result of an expression, translated to the requested Data-Type, or NULL if the Cast fails. Let’s check how TRY_PARSE() validates above character values as numeric:

SELECT 
	 TRY_PARSE('123' as int) as '123'
	,TRY_PARSE('abc' as int) as 'abc'
	,TRY_PARSE('-' as int) as '-'
	,TRY_PARSE('+' as int) as '+'
	,TRY_PARSE('$' as int) as '$'
	,TRY_PARSE('.' as int) as '.'
	,TRY_PARSE(',' as int) as ','
	,TRY_PARSE('\' as int) as '\'

So, the above query gives me expected results by validating first column value as numeric and rest as invalid and returns NULL for those.

–> TRY_PARSE() can be used with other NUMERIC & DATETIME data-types for validation, like:

SELECT 
	 TRY_PARSE('123' as int) as '123'
	,TRY_PARSE('123.0' as float) as '123.0'
	,TRY_PARSE('123.1' as decimal(4,1)) as '123.1'
	,TRY_PARSE('$123.55' as money) as '$123.55'
	,TRY_PARSE('2013/09/20' as datetime) as '2013/09/20'

… will give expected results 🙂