Natively Compiled Stored Procedures with SQL Server 2014

In my last few articles I’ve been discussing the new SQL Server 2014 In-Memory OLTP tables.  With this new in-memory feature, stored procedures can be now be natively compiled.  In this article I will be discussing natively compiled stored procedures and showing you an example where they can improve performance.

What are Natively Compiled Stored Procedures?

SQL Server is an interpretive language, which means code is not compiled in advance of execution.  Instead, when a stored procedure is executed SQL Server compiles the stored procedure the first time it is executed.  With a natively compiled stored procedure that all changes.

A natively compiled stored procedure is compiled when it is created, rather than when it is executed.  That compile process first translates the store procedure code into “C”, and then into machine language and stores the code as a DLL.  The DLL is stored in a special folder on the SQL Server machine.   Because the code is compiled into machine code and can be run directly by the CPU without interpretation it is more efficient than normally created stored procedures, and it uses less machine instructions. 

You cannot access disk based tables in a natively compiled stored procedure. All tables accessed by a natively compiled stored procedure have to be In-Memory OLTP tables.  This is just one of the limitations of natively compiled stored procedures.  Here is a partial list of some of the other TSQL constructs that cannot be used in a natively compiled stored procedure:

• Cursors are not supported
• Cannot use multi-row insert statements
• Cannot use common table expressions
• Cannot use subqueries

A more comprehensive list of limitations can be found here:  http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx

In order to better understand how to create a natively compiled stored procedure let me go through an example. 

Creating a Natively Compiled Stored Procedure

In order to show you how to create a natively compiled stored procedure that uses an In-Memory OLTP table I first need to create a demo database that supports In-Memory tables. Here is the script to create my demo database:

USE [master]
GO
CREATE DATABASE [NativelyCompiledSPDemo]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'NativelyCompiledSPDemo_Data', 
  FILENAME = N'C:\MSSQL\DATA\NativelyCompiledSPDemo_Data.mdf' , 
  SIZE = 3264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [NativelyCompiledSPDemo_mod] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'NativelyCompiledSPDemo1_mod', 
  FILENAME = N'C:\MSSQL\DATA\NativelyCompiledSPDemo1_mod' ,
  MAXSIZE = 2097152KB ),
( NAME = N'NativelyCompiledSPDemo2_mod', 
  FILENAME = N'C:\MSSQL\DATA\NativelyCompiledSPDemo2_mod' , 
    MAXSIZE = 2097152KB )
 LOG ON 
( NAME = N'NativelyCompiledSPDemo_Log', 
  FILENAME = N'C:\MSSQL\DATA\NativelyCompiledSPDemo_log.ldf' , 
  SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

In this script I created a database named “NativelyCompiledSPDemo”.  Once my demo database is created I can then create the In-Memory OLTP table that I will be referencing in my natively compiled stored procedure.  Below is the code for creating the In-Memory OLTP table:

USE NativelyCompiledSPDemo;
GO
CREATE TABLE dbo.InMemoryTest 
(
       ID int NOT NULL,
       CalculatedField float NOT NULL
 
CONSTRAINT PK_Test PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 16777216))
WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
 
GO
 

In this script I created a very simple table named “InMemoryTest”.   This is a very simple table designed to not meet any real business requirements, but instead to demonstrate the advantages of natively compiled stored procedures.

Once my table is created I’m now ready to create my natively compiled stored procedure.  Below is the script to create that stored procedure:

CREATE PROC NativelyCompiledInsertTest
       WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
  ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english')
DECLARE @I int = 1;
DECLARE @X float;
WHILE @I < 1000000 
BEGIN
       INSERT INTO dbo.InMemoryTest 
              VALUES (@I, @I + 1 * @I * log(@I) + tan (@I) + rand() * cos(@I));
       INSERT INTO dbo.InMemoryTest 
              VALUES (@I + 1, @I + 1 * @I * log(@I) + tan (@I) + rand() * cos(@I));
       SET @I += 2;
 
END
END
GO

As you can see my natively compiled stored procedure is named “NativelyCompiledInsertTest “.  This stored procedure is also very simple.  All it does is insert 1 million records into the In-Memory OLTP table named InMemoryTest, by processing through a loop.  Each time through the loop two INSERT statements are executed.  These INSERT statements populate the two columns in my InMemoryTest table.    The code that makes this a natively compiled stored procedure is right at the top of the stored procedure.  I’ve also listed that code below:

WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
  ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english')

By specifying “NATIVE_COMPILATION” in the WITH clause I am identifying that I want this stored procedure to be natively compiled.  When I specify “NATIVE_COMPILATION” I also have to specify “SCHEMA_BINDING” and “EXECUTE AS OWNER”.  SCHEMA_BINDING says the tables referenced in my natively compiled stored procedure cannot be dropped, without first dropping the natively compiled stored procedure.  Natively compiled stored procedures cannot be executed using the “EXECUTE AS CALLER” option, which is the default.  Therefore I had to specify one of the following three “EXECUTE AS” options:  EXECUTE AS OWNER, EXECUTE AS USER, or EXECUTE AS SELF.  Therefore I specified the “EXECUTE AS OWNER” option. 

Additionally a natively compiled stored procedure needs to consist of one atomic block of code. An atomic block of code means that all statements in the code block are contained within a single transaction.  This means if any part of the code block should fail all database changes that were made in the code block are rolled back.  Either all commands succeed or all command are rolled back.    In the “ATOMIC WITH” clause you need to specify the “TRANSACTION ISOLATION LEVEL” and “LANGUAGE”.  To find out more about the ins and outs of the supported transaction level supported for memory optimized tables I suggest you read this this Books Online documentation: http://msdn.microsoft.com/en-us/library/dn133187(v=sql.120).aspx

As you can see creating a natively compiled stored procedure only requires a few extra clauses at the beginning of a stored procedure to identify that a stored procedure is a natively compiled object.   If you need to alter your natively compiled stored procedure you cannot issue an ALTER command on a natively compiled stored procedure.  Instead you will have to drop and re-create your stored procedure.

To get a better understanding of the real value of natively compiled stored procedures you need to understand the performance gains that can be obtained using natively compiled stored procedures verses a normal stored procedure.  In order to show the performance benefits let me show you an example.

Measuring the Performance Gains of Natively Compiled Stored Procedures

In order to measure the performance benefits of natively compiled stored procedures I’m going to run two different store procedures–one stored procedure that is natively compiled and the second stored procedure contains similar code that is not natively compiled.  Each of these stored procedures will insert 1 million rows into the table dbo.InMemoryTest that I created in the prior section.   The natively compiled stored procedure is the one I created in the last section.  The stored procedure that is not natively compiled can be found below: 

CREATE PROC InsertTest
AS
DECLARE @I int = 1;
DECLARE @X float;
WHILE @I < 1000000  
BEGIN
       INSERT INTO dbo.InMemoryTest 
              VALUES (@I, @I + 1 * @I * log(@I) + tan (@I) + rand() / cos(@I)),
                     (@I + 1, @I + 1 * @I * log(@I) + tan (@I) + rand() / cos(@I));
       SET @I += 2;
 
END

To test these two different stored procedures I ran each stored procedure 5 times.  Before each run I deleted all the records in my InMemoryTest table.  Using this command:

DELETE FROM dbo.InMemoryTest;

I used the following code to test my natively compiled stored procedure:

SET NOCOUNT ON;
EXEC NativelyCompiledInsertTest;  

This is the code I used to test my procedure that was not natively compiled:

SET NOCOUNT ON;
BEGIN TRAN;
EXEC InsertTest;  
COMMIT TRAN;

When I ran the 5 different tests of these two stored procedures I used the time output in the query window to measure how long each execution took.  Here are the timings of each of those stored procedure executions:

As you can see from this single example the natively stored procedure ran a little more than 6 times faster.   Remember this is a contrived example to show that for this one specific case the natively compiled stored procedure was faster than my stored procedures that was not natively compiled.   The performance of natively compiled stored procedures and those that are not natively compiled might vary depending on the logic within the stored procedures.  Therefore if you plan to explore using natively compiled stored procedures, I would recommend you first test out both the natively compiled solution against a non-natively compiled solution to verify which solution provides the best performance.

The Native Compilation Advisor

After you have identified which stored procedures you might want to migrate to natively compiled stored procedures, you can use the Native Compilation Advisor to determine what issues you might have in migrating each of your stored procedures. To help with this Microsoft has provided the Native Compilation Advisor.

To use the advisor, I will right click on my InsertTest store procedure, which is not natively compiled and then select “Native Compilation Advisor” as show below:

Native Compilation Advisor

When I click on the “Native Compilation Advisor” the following welcome screen is displayed:

Welcome to the Native Compilation Advisor

On this screen I just click “Next>” to continue on with the advisor.  When I do that the following window is displayed:

Stored Procedure Validation

Here you can see my InsertTest stored procedure has a validation error.  To display the exact error the advisor found I can click on the “Next>” button.   When I do that here is what is displayed:

Stored Procedure Validation Result

Here you can see I have a Multi-row table valued construct starting in Line 6 of my store procedure.  In order to migrate this stored procedure I would have to change my stored procedure InsertTest to look like this:

ALTER PROC InsertTest
AS
DECLARE @I int = 1;
DECLARE @X float;
WHILE @I < 1000000  
BEGIN
       INSERT INTO dbo.InMemoryTest 
              VALUES (@I, @I + 1 * @I * log(@I) + tan (@I) + rand() / cos(@I));
       INSERT INTO dbo.InMemoryTest
              VALUES (@I + 1, @I + 1 * @I * log(@I) + tan (@I) + rand() / cos(@I));
       SET @I += 2;
 
END

Now that I’ve changed my InsertTest stored procedure I can run it back through the advisor to see if it is ready to be migrated to a natively compiled stored procedure.  When I do that this window is displayed:

Stored Procedure Validation

Note there are no validate errors this time, and the “Next>” button is grayed out.  This tells me that my InsertTest stored procedure is ready to be changed to a natively compiled stored procedure.  The Native Compilation Advisor doesn’t actually migrate stored procedures, it only report problems you might have when migrating a stored procedure to be a natively compiled stored procedure.

Is Creating a Natively Compiled Stored Procedure the Way to Go?

As my single example showed, a natively compiled stored procedure can out perform a normal stored procedure that uses an In-Memory table.  Keep in mind this may not be true in every situation.  Therefore before you go hog wild converting your normal stored procedures to natively compiled stored procedures it is important to remember to run test comparisons of your natively compiled stored procedure solutions against the normally stored procedures.  Only by running benchmark comparisons can you conclusively say your natively compiled stored procedures are a more efficient way to go.  Hopefully as you start exploring SQL Server 2014 In-Memory OLTP tables and natively compiled stored procedures you will find opportunities to get great performance gains by implementing natively compiled stored procedures.

See all articles by Greg Larsen