Great Articles from WWW by SQLTop

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://speedysql.com/2015/10/28/new-trace-flag-for-in-memory-oltp-hekaton/#more-1216

Undocumented In-Memory OLTP Trace Flag

Recently I was playing around with the new In-Memory OLTP feature of SQL Server 2014 when I found an undocumented trace flag. I thought I would write a quick blog post about it to show what it does.

Let’s start by creating a new database with a memory optimized filegroup.

1 2 3 4 5 6 7 8 9 10

USE [master] GO CREATE DATABASE Hekaton GO USE Hekaton; GO ALTER DATABASE Hekaton ADD FILEGROUP [FG_Hekaton] CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE Hekaton ADD FILE (name='Hekaton_data', filename='F:\SQLData\MSSQL12.B\MSSQL\DATA\Hekaton\') TO FILEGROUP [FG_Hekaton];

Next, we’ll create an in-memory table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE TABLE dbo.hekaton1 ( c1 int NOT NULL, c2 float NOT NULL, c3 decimal(10,2) NOT NULL INDEX index_c3 NONCLUSTERED (c3), CONSTRAINT PK_hekaton1 PRIMARY KEY NONCLUSTERED (c1), -- See SQL Server Books Online for guidelines on determining appropriate bucket count for the index INDEX hash_index_hekaton1_c2 HASH (c2) WITH (BUCKET_COUNT = 131072) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO INSERT dbo.hekaton1  SELECT TOP 100000       ROW_NUMBER() OVER (ORDER BY (SELECT 1)), CAST(ABS(CHECKSUM(NEWID()))%1000000/100.0 AS float), CAST(ABS(CHECKSUM(NEWID()))%1000000000/100.0 AS decimal(10,2)) FROM sys.all_objects CROSS JOIN sys.syscolumns

Trace Flag

Here’s the exciting bit. Trace flag 9830! This is an undocumented trace flag so please don’t do this on a production system. Activate the trace flag before creating a natively compiled procedure.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

--Activate trace flag. DBCC TRACEON (9830,-1) GO --Create test procedure. CREATE PROCEDURE dbo.Hekaton1_select @p1 int = 0 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) SELECT h.c1, h.c2, h.c3 FROM dbo.hekaton1 h WHERE h.c1 > @p1 END GO

If you now open up the SQL Server error log you should see the compilation process for the natively compiled procedure.

There are several phases to the compilation.

• Generation of the mixed abstract tree (MAT).
• Conversion of the MAT to the pure imperative tree (PIT) for easier conversion to C.
• The PIT is used to generate C code.
• The C code is compiled into a DLL.
• The OS loader imports the DLL into SQL Server.

Here is the process displayed graphically from MSDN: