In my previous articles, I discussed the new SQL Server 2014 In-Memory OLTP tables and their benefits. In this article, I will focus on natively compiled stored procedures and how they can significantly improve performance.
What are Natively Compiled Stored Procedures?
SQL Server is an interpretive language, meaning that code is compiled at the time of execution. However, with natively compiled stored procedures, the compilation process occurs when the stored procedure is created, rather than when it is executed. The code is translated into machine language and stored as a DLL, allowing it to be executed directly by the CPU without interpretation. This results in improved efficiency and reduced machine instructions.
It is important to note that natively compiled stored procedures can only access In-Memory OLTP tables and have certain limitations. For example, cursors, multi-row insert statements, common table expressions, and subqueries are not supported.
Creating a Natively Compiled Stored Procedure
To create a natively compiled stored procedure, you need to follow a few additional steps. First, you must create a database that supports In-Memory tables. Then, you can create the In-Memory OLTP table that will be referenced in the stored procedure. Finally, you can create the natively compiled stored procedure itself.
Here is an example of creating a natively compiled 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
In this example, the natively compiled stored procedure inserts 1 million records into the In-Memory OLTP table named InMemoryTest. The code that makes this a natively compiled stored procedure is specified at the top of the stored procedure using the “WITH NATIVE_COMPILATION” clause.
Measuring the Performance Gains
To measure the performance benefits of natively compiled stored procedures, you can compare them to normal stored procedures that are not natively compiled. By running both types of stored procedures and measuring the execution time, you can determine the performance gains.
Here is an example of comparing the performance of a natively compiled stored procedure and a non-natively compiled stored procedure:
SET NOCOUNT ON;
EXEC NativelyCompiledInsertTest;
SET NOCOUNT ON;
BEGIN TRAN;
EXEC InsertTest;
COMMIT TRAN;
By running these stored procedures multiple times and measuring the execution time, you can observe the performance differences. In my example, the natively compiled stored procedure ran more than 6 times faster than the non-natively compiled stored procedure.
The Native Compilation Advisor
To assist with migrating stored procedures to natively compiled stored procedures, SQL Server provides the Native Compilation Advisor. This tool helps identify any issues that may arise during the migration process.
By right-clicking on a stored procedure and selecting “Native Compilation Advisor,” you can analyze the stored procedure for any validation errors. The advisor will provide suggestions on how to modify the stored procedure to make it compatible with natively compiled stored procedures.
Conclusion
Natively compiled stored procedures can significantly improve performance in SQL Server, especially when working with In-Memory OLTP tables. However, it is important to thoroughly test and compare the performance of natively compiled stored procedures against non-natively compiled stored procedures to ensure the best solution for your specific scenario.
By exploring SQL Server 2014 In-Memory OLTP tables and natively compiled stored procedures, you may discover opportunities to achieve great performance gains in your database applications.
See all articles by Greg Larsen