By: Daniel Farina | 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.
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
Step 2: Choose Template
Step 3: Select Events To Capture
Step 4: In the next two screens leave default values
Step 5: Specify Session Data Storage
Step 6: 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.
Next Steps
- If you still don't have a version of SQL Server 2014, download a trial version here.
- If you don't know what a stored procedure is, then this tip is for you: Getting started with SQL Server stored procedures.
- Also, you can read the following SQL Server Stored Procedure Tutorial.
- Read more about SQL Server 2012 Sequence Numbers.
- Learn about query parameters in this tip: Using Parameters for SQL Server Queries and Stored Procedures.
- View this tip about Table-Valued parameters: Using Table Valued Parameters (TVP)in SQL Server 2008.
- Read this tip about SCHEMABINDING usage: Using schema binding to improve SQL Server UDF performance.
- If you need to find schema-bound dependencies, then this is the tip for you: Different Ways to Find SQL Server Object Dependencies.
- To get more information about EXECUTE AS read this: Granting permission with the EXECUTEAS command in SQL Server.
- Read this tip about Transaction isolation levels: Demonstrations of Transaction Isolation Levels in SQL Server.
- Review SNAPSHOT isolation: Snapshot Isolation in SQL Server 2005.
- Learn how to raise an exception with THROW statement: SQL Server 2012 THROW statement to raise an exception.
- For more info about TRY and CATCH exception handling read the following tip: SQL Server 2005 Try and Catch Exception Handling.
- Also check out the error handling tips category.
- If you don't know what Extended Events are, then read this: An Overview of Extended Events in SQL Server 2008.
- Also, if you have SQL Server 2012, you can read this tip about Managing SQL Server Extended Events in Management Studio.
- For more tips on Extended Events, review the Extended Events Tips category.
About the author
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
