Migrate to Natively Compiled SQL Server Stored Procedures for Hekaton

By:   |   Updated: 2013-10-10   |   Comments (10)   |   Related: > In Memory OLTP


Problem

In order to take full advantage of the In-Memory OLTP options in SQL Server 2014, you should migrate standard stored procedures that references Memory-Optimized tables to natively compiled ones. In this tip we will see how to achieve this goal.

Solution

In my previous tip about SQL Server Memory-Optimized tables, I showed you how SQL Server entered into the In-Memory OLTP world. However, sometimes Memory-Optimized tables aren't enough to solve performance issues. That's why Natively-Compiled Stored Procedures have come onto the scene.

What is a Hekaton Natively-Compiled SQL Server Stored Procedure?

A Natively-Compiled Stored Procedure is much like a common SQL Server stored procedure with a very important difference: it is compiled into native code. Standard stored procedures like ad hoc queries, uses an interpreter for execution. With the difference that a standard stored procedure is compiled on first run and an execution plan is created and reused.

In the other hand, a Natively-Compiled Stored Procedure is parsed and compiled on creation and is executed like an external DLL function by the Hekaton engine. Needless to say that having a stored procedure in native code improves the run time performance.

Creation of a SQL Server 2014 Natively Compiled Stored Procedure

To implement Natively-Compiled Stored Procedures in SQL Server, new arguments were added to the CREATE PROCEDURE statement.

Create Procedure

In the image above we can see how the CREATE PROCEDURE statement looks like when we want to create a Natively-Compiled Stored Procedure. Here is an explanation of the new arguments.

NATIVE_COMPILATION

Obviously this is mandatory in order to create a Natively-Compiled Stored Procedure.

SCHEMABINDING

This argument is not optional. If you think about this you will realize that SCHEMABINDING is strictly needed on Natively-Compiled Stored Procedures. Remember, these procedures are compiled in a DLL, loaded into SQL Server's memory address space and executed as native code, like a C program. So, suppose that you drop a column of a table referenced by a natively compiled stored procedure. If the code tries to access that column it will produce an access violation. Anyway, you cannot perform an ALTER TABLE on a Memory-Optimized table, you have to drop and recreate it.

Also this option avoids acquiring costly schema stability locks before execution. Remember, Hekaton engine is lock free and latch free.

EXECUTE AS

Specifying the execution context is required because natively compiled stored procedures do not support the default execution context, which is EXECUTE AS CALLER. The supported options are EXECUTE AS OWNER, EXECUTE AS user, and EXECUTE AS SELF. You may ask why, but think about this. When a stored procedure is executed with EXECUTE AS CALLER impersonation, security checks must be performed at runtime. But, when you use a predefined security context, all permission checks can be run on procedure creation, and this obviously increases execution performance.

BEGIN ATOMIC

Atomic blocks are new in SQL Server 2014 and only supported on natively compiled stored procedures. It is mandatory that a natively compiled stored procedure contain only one atomic block of code.

The natively compiled stored procedure body must consist of exactly one atomic block of code. Atomic blocks guarantee atomic execution of the stored procedure. If the procedure is invoked outside the context of an active transaction, it will start a new transaction, which commits at the end of the atomic block.

Inside an atomic block, either all statements succeed or the entire block will be rolled back to the savepoint that was created at the start of the block. Furthermore, an atomic block has its own session settings, so executing the same atomic block in sessions with different settings will result in the same behavior, independent of the settings of the current session.

Atomic blocks in natively compiled stored procedures have two required options:

Required options

TRANSACTION ISOLATION LEVEL

This option sets the isolation level of the atomic transaction. Supported values are SNAPSHOT, REPEATABLEREAD, and SERIALIZABLE.

LANGUAGE

To set date and time formats and system messages. All languages and aliases in sys.syslanguages (Transact-SQL) are supported.

Not required options

DATEFORMAT

All SQL Server date formats are supported. When specified, DATEFORMAT overrides the default date format associated with LANGUAGE.

DATEFIRST

When specified, DATEFIRST overrides the default associated with LANGUAGE.

Why should I use a Natively Compile Stored Procedure in SQL Server 2014?

The idea of Natively-Compiled Stored Procedures was conceived over the assumption that there are cases in which locking and latching prevents application to scale up, independently of hardware used. This is inherent of all databases that are optimized for disk storage. If you have an application with performance-critical parts that are expected to be fast, then Natively-Compiled Stored Procedures can address this issue.

What do we can or cannot do with SQL Server Natively Compile Stored Procedures for OTLP In Memory Databases?

You can do almost everything you do with standard stored procedures, but there are some limitations. In this page you will find an updated list of Unsupported Transact-SQL Constructs (http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx) for Memory-Optimized Tables and Natively Compiled Stored Procedures.

As with Memory-Optimized Tables, you cannot ALTER natively compiled stored procedures, you should drop and recreate them. Also, natively compiled stored procedures cannot be debugged.

You should view Hekaton like a database engine optimized to be lock free and latch free, interconnected with the traditional SQL Server engine. This is crucial in order to understand its functioning, advantages and limitations; and therefore, see possible applications into your environment.

Disk based tables and TempDB Access in SQL Server from Natively Compiled Stored Procedures

You cannot access a disk based table or TempDB from a Natively-compiled stored procedure. This may sound crazy at first sight, but it is very logic when we view Hekaton as a database engine optimized to be lock free and latch free. Let me explain my point. We have in one hand a system which has latches and locks; the SQL Server Engine. And in the other hand we have a latch free and lock free engine, in this case, Hekaton. The point is that in order to be lock free and latch free, a Hekaton's Natively-Compiled Stored procedure cannot interact with a system which has latches and locks because if so, Hekaton would need to implement a locking and latching mechanism. And that is against Hekaton purpose. By contrast, you can access to Memory-Optimized tables and execute Natively-compiled stored procedures in standard stored procedures. That is because standard stored procedures handles the locks and latches so Hekaton engine remains lock free and latch free.

A workaround to use Temp tables is the creation of a Table Type, which can be created as Memory-Optimized and therefore used in Natively-Compiled Stored Procedures. After that, we can use this table Type as a temporary table. See an example at the end of this tip.

I must add that even though you can create table variables, these cannot be passed as a parameter, because table-valued parameters are not supported.

 Error handling in SQL Server Natively Compiled Stored Procedures

Natively compiled stored procedures support the TRY, CATCH, and THROW constructs for error handling. RAISERROR is not supported.

How to use the EXISTS Clause in SQL Server from Natively Compiled Stored Procedures

Natively compiled stored procedures do not support the EXISTS clause, but we can use the following trick.

  DECLARE @Exists BIT = 0
  SELECT TOP 1
            @Exists = 1
  FROM      dbo.Worked_Hours
  IF @Exists = 1
  BEGIN
 ...

Considerations on execution of SQL Server 2014 Natively Compiled Stored Procedures

In order to maximize performance, you should avoid parameter name mapping and type conversions by matching the types of the parameters passed to the procedure with the types in the procedure definition and do not use named parameters.

You can use hekaton_slow_parameter_passing extended event to detect this issues.

SQL Server from Natively Compiled Stored Procedures Example

I have made an example to put it all together.

1. Test SQL Server database creation

CREATE DATABASE TestDB
ON PRIMARY
  (NAME = TestDB_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.mdf',
          SIZE = 100MB,          
          FILEGROWTH = 10%),
FILEGROUP TestDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
  ( NAME = TestDB_MemoryOptimized,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_MemoryOptimized')
LOG ON
  ( NAME = TestDB_log_file1,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB_1.ldf',
          SIZE = 100MB,          
          FILEGROWTH = 10%)
GO

2. Test table dbo.Employees creation in SQL Server

Remember from my previous tip that Memory-Optimized tables does not support IDENTITY column, therefore we must use a SEQUENCE object in order to implement an IDENTITY column.

USE TestDB
GO
IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
BEGIN
 DROP TABLE dbo.Employees
END
GO
CREATE TABLE dbo.Employees(
 Employee_ID int NOT NULL,
 Name nvarchar(50) NOT NULL,
 Base_Salary money NOT NULL,
 Hour_Cost money NOT NULL,
 CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED HASH  
(
 Employee_ID 
) WITH (BUCKET_COUNT=1024)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
IF OBJECT_ID('dbo.Employees_Employee_ID_Seq','SO') IS NOT NULL
BEGIN
 DROP SEQUENCE dbo.Employees_Employee_ID_Seq
END
GO
CREATE SEQUENCE dbo.Employees_Employee_ID_Seq
  AS [int]
  START WITH 1
  INCREMENT BY 1
GO
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq, 'John Doe', 1000, 10
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq,  'Sam Abolrous', 2000, 10
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq,  'Pilar Ackerman', 5000, 50
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq,  'Kim Abercrombie', 4510, 15
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq,  'Andrea Adams', 3255, 5
INSERT INTO dbo.employees (Employee_ID, Name, Base_Salary, Hour_Cost)
SELECT NEXT VALUE FOR dbo.Employees_Employee_ID_Seq,  'Jamie Alan', 2500, 20
GO

3. Test table dbo.Worked_Hours creation in SQL Server

USE TestDB
GO
IF OBJECT_ID('dbo.Worked_Hours','U') IS NOT NULL
BEGIN
 DROP TABLE dbo.Worked_Hours
END
CREATE TABLE dbo.Worked_Hours(
 Hour_ID int  NOT NULL,
 Employee_ID int NOT NULL,
 Worked_Hours int NOT NULL,
 [Date] date NOT NULL,
 CONSTRAINT PK_Worked_Hours PRIMARY KEY NONCLUSTERED HASH 
(
 Hour_ID 
)WITH (BUCKET_COUNT = 1024) 
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
IF OBJECT_ID('dbo.Worked_Hours_Hour_ID_Seq','SO') IS NOT NULL
BEGIN
 DROP SEQUENCE dbo.Worked_Hours_Hour_ID_Seq
END
GO
CREATE SEQUENCE dbo.Worked_Hours_Hour_ID_Seq
  AS [int]
  START WITH 1
  INCREMENT BY 1
GO
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date])     
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq, 1, 3, GETDATE() - 3
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,1, 5, GETDATE() - 4
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,1, 4, GETDATE() - 5
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,1, 3, GETDATE() - 6
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,1, 1, GETDATE() - 7
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,3, 5, GETDATE() - 4
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,3, 4, GETDATE() - 5
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,3, 3, GETDATE() - 6
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,2, 5, GETDATE() - 4
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,2, 4, GETDATE() - 5
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,2, 3, GETDATE() - 6
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,4, 3, GETDATE() - 6
INSERT INTO dbo.Worked_Hours (Hour_ID, Employee_ID, Worked_Hours, [Date]) 
SELECT NEXT VALUE FOR dbo.Worked_Hours_Hour_ID_Seq ,5, 5, GETDATE() - 54
GO

4. Table type creation for Natively Compiled SQL Server Stored Procedures

We need to create a table type, so we can use a variable table type like a temp table.

USE TestDB
GO
IF TYPE_ID('dbo.T_Salaries_Table') IS NOT NULL 
    DROP TYPE dbo.T_Salaries_Table
GO
CREATE TYPE dbo.T_Salaries_Table AS TABLE
(
 Employee_ID   INT NOT NULL,  
 Total_Worked_hours INT NOT NULL, 
 [Date]    DATE NOT NULL
 PRIMARY KEY NONCLUSTERED HASH (Employee_ID) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON)
GO

5. Create procedure example for Natively Compiled SQL Server Stored Procedures

USE TestDB
GO
-- Drop stored procedure if it already exists
IF OBJECT_ID('dbo.Compute_Salaries','P') IS NOT NULL
   DROP PROCEDURE dbo.Compute_Salaries
GO
CREATE PROCEDURE dbo.Compute_Salaries
@DateFrom DATE
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
   
  DECLARE @TempTable dbo.T_Salaries_Table
  DECLARE @Exists BIT = 0
  SELECT TOP 1
            @Exists = 1
  FROM      dbo.Worked_Hours
  WHERE  [Date] > @DateFrom
  
  IF @Exists = 1 
    BEGIN  
        INSERT  INTO @TempTable
                SELECT  E.Employee_ID ,
                        SUM(W.Worked_Hours),
      MAX(W.[Date])
                FROM    dbo.employees E
                        INNER JOIN dbo.Worked_Hours W ON E.Employee_ID = W.Employee_ID
    WHERE W.[Date] > @DateFrom
                GROUP BY E.Employee_ID
        SELECT  E.Employee_id ,
                E.Name ,
                E.Base_Salary + E.Hour_Cost * T.Total_Worked_hours AS 'Salary',
    T.[Date]
        FROM    dbo.employees E
                INNER JOIN @TempTable T ON E.Employee_ID = T.Employee_ID  
    END
END
GO

Now I am going to create an Extended Event Session to monitor slow parameter passing

6. Extended Event Session Creation in SQL Server

CREATE EVENT SESSION [Natively Compiled Stored Procedures] ON SERVER 
 ADD EVENT sqlserver.natively_compiled_proc_slow_parameter_passing,
 ADD EVENT sqlserver.natively_compiled_proc_execution_started 
 ADD TARGET package0.event_file(SET filename=N'Natively Compiled Stored Procedures'),
 ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH ( MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
--Start Event Session
ALTER EVENT SESSION [Natively Compiled Stored Procedures]  ON SERVER STATE = START

Also, you can use Management Studio to create the session as follows.

Step 1: Set Session Properties for Extended Events

Set Session Properties

Step 2: Choose Template

Choose Template

Step 3: Select Events To Capture

Select Events To Capture

Step 4: In the next two screens leave default values

Step 5: Specify Session Data Storage

Specify Session Data Storage

Step 6: Summary

Summary

7: SQL Server Natively Compiled Stored Procedure Execution

USE TestDB
GO
EXECUTE dbo.Compute_Salaries @DateFrom = '20130101'
GO
-----------------------------------------------------------------------------
DECLARE @Date DATE
SET @Date = '20130101'
EXECUTE dbo.Compute_Salaries @DateFrom = @Date
GO
-----------------------------------------------------------------------------
EXECUTE dbo.Compute_Salaries '20130101'
GO
-----------------------------------------------------------------------------
DECLARE @Date DATE
SET @Date = '20130101'
EXECUTE dbo.Compute_Salaries @Date

Here is the Extended Event Session log of executions of the stored procedure. Note that only the fourth execution does not generate a natively_compiled_proc_slow_parameter_passing event.

Extended Event Log View
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2013-10-10

Comments For This Article




Friday, August 25, 2017 - 9:15:36 AM - Sarfaraz Alam Back To Top (65310)

 Hi,

I'm creating stroeprocedure in memory optimized database (native compile).Problem is how to use case condition on the date my regular T-SQL is Look like this.

DATEDIFF(second, ModifiedOn,GETDATE()) >= 3) THEN 1 ELSE 0 END

But this command is not supported in SQL Server 2016. 

 

how can I fixed it:

 

 

Thursday, August 20, 2015 - 9:27:48 AM - Klaus Schillo Back To Top (38497)

The link listed above conc. 'Unsupported Transact-SQL Constructs' does not work :

https://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx)

 

Please take this one instead :

https://msdn.microsoft.com/en-US/library/dn296678(v=sql.120).aspx

 

Best regards!

Friday, November 15, 2013 - 11:28:09 AM - Daniel Farina Back To Top (27511)

 

Hi Russ

 

Now the link is operational.

 

On CTP2 those event names has changed, please use the following event names:

 

ADD EVENT sqlserver.natively_compiled_proc_execution_started,

 

ADD EVENT sqlserver.natively_compiled_proc_slow_parameter_passing

 

 Also if you want to use the wizard, on CTP 2 you need to check “Debug” on Channel Column.

 

 

 

Best regards!

 

Tuesday, October 29, 2013 - 4:15:09 PM - Russ Johnson Back To Top (27324)

The link listed above does not work:

http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx

 

Also, I got an error implementing the extended events as I did not see them in CTP2:

 

 ADD EVENT sqlserver.hekaton_slow_param_passing,
 ADD EVENT sqlserver.natively_compiled_proc_execution_started

Friday, October 25, 2013 - 2:48:24 PM - Greg Robidoux Back To Top (27288)

Hi John,

Last week at the PASS Summit in Charlotte, NC, Microsoft did some demos of this new feature.

In the tests they showed, performance was 10 to 30 times faster than how the database engine works today.

They also gave examples of real life examples of customers using this and have seen improvements of 3 to 10 times faster.

The original concept was to get a 100 times performance improvement, hence the name hekaton but those numbers have not yet been achieved.

Friday, October 25, 2013 - 1:39:02 PM - Daniel Farina Back To Top (27286)

 That depends on the workload. In some cases you can get 10 -30x more efficient (according to Microsoft) but in some scenarios migration won’t give that numbers or even it couldn’t be done.

There is a tool in SQL Server 2014 called Native Compilation Advisor within AMR (Analysis, Migrate and Report) that analyzes your workload and informs you about which interpreted stored procedures in your database will benefit if ported to use native compilation.  Microsoft has published success stories that maybe would give you the answers you find. Here you have two videos:  http://www.youtube.com/watch?v=cDrsCaXNpSk 

http://www.youtube.com/watch?v=fHTj274pu48

Friday, October 25, 2013 - 12:23:12 PM - John Sobolewski Back To Top (27284)

I understand the "in theory" arguments but I just don't trust the "in theory" until I see the "in practice" numbers.    I am interested in an actual load test showing the real performance difference. 

Are we talking 30 ms total difference over 10k requests or is this an order of magnitude difference in my expected throughput.

 

Friday, October 25, 2013 - 11:52:43 AM - Daniel Farina Back To Top (27282)

Hi John and Steven! Is exactly as Steven said. When executing a natively compiled stored proc the engine only needs to perform a call to a dll function.

The speed improvement is relative to the logic of the sp to be migrated and how much data you are dealing with. 

The performance gain is for all runs. But that depends on table statistics and indexing. Hekaton does not auto update stats so if data distribution changes you may have an issue. Also if you are using hash indexes improper number of buckets could be detrimental due to chaining.

Friday, October 25, 2013 - 8:15:54 AM - Steven J McKenna Back To Top (27279)

I will leave it to Daniel to respond fully, but i would imagine he is referring to - no locks, latches, security pre-defined, all processes in memory, by-passing the query optimzer... the procs, needless to say, would run faster.

Friday, October 25, 2013 - 6:42:18 AM - John Sobolewski Back To Top (27277)

You make this claim early in the article:

"Needless to say that having a stored procedure in native code improves the run time performance."

In what circumstances?  On the first run or all runs?  By how much?  What tests show this difference? 














get free sql tips
agree to terms