SQL Server Stored Procedure Native Compilation Advisor

By: Mohammed Moinudheen   |   Updated: 2016-05-19   |   Comments   |   Related: > In Memory OLTP

Problem

From the SQL Server Analysis, Migrate and Report (AMR) tool (Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014), I could see there are recommendations for some tables and stored procedures to be memory optimized. I also used the In-Memory OLTP migration checklists (Using In-Memory OLTP migration checklists in SQL Server 2016) to evaluate any incompatibilities in memory optimization. From this tip (SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP), I learned how to migrate tables over to memory optimized tables. Is there any tool that could be used to migrate the interpreted (disk-based) stored procedures for native compilation?

Solution

Starting in SQL Server 2014, we could make use of the Native Compilation Advisor to evaluate the interpreted (disk-based) stored procedures for native compilation.

In order to test this feature, just create a sample stored procedure in a database, use the script below as an example. For the table script, refer this tip: SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Example]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from First.dbo.Example
END
GO

In SQL Server Management Studio (SSMS), right click on the procedure and select "Native Compilation Advisor".

Once you select this option, you will see this window as shown below which is the Stored Procedure Native Compilation Advisor.

Click "Next" on the Introduction screen, which will take you to the "Stored Procedure Validation" screen as shown below.

Make sure to refer this tip (SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP) on how to migrate disk based tables referenced in the stored procedure to In-Memory OLTP as it is one of the prerequisites for enabling native compilation for stored procedures. In order to view a detailed list of unsupported T-SQL elements in the stored procedure, click "Next" and you will see the "Stored Procedure Validation Result" window.

Click on the "Generate Report" option on the bottom right and save the output to the desktop (or any folder) to view the report in HTML format. Once done, the report would be displayed as shown below.

From the above report, you know the components in your stored procedure that have compatibility issues with native compilation. Once you fix these compatibilities and go through the steps again as shown above, you would see a screenshot as shown below.

With this, you can confirm that there are no unsupported T-SQL elements in the stored procedure which would prevent it to become memory optimized. When you try to directly alter the non-native stored procedure to compile natively, you would encounter an error message as below.

As it is not possible to directly alter the existing interpreted stored procedure to support native compilation, you would need to drop and re-create the stored procedure with the "NATIVE_COMPILATION" option. Use the example code as shown below.

-- Drop stored procedure if it already exists
IF OBJECT_ID('dbo.sp_Example','P') IS NOT NULL
DROP PROCEDURE dbo.sp_Example
GO
CREATE PROCEDURE dbo.sp_Example
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
SELECT ID FROM dbo.Example
END

Once done, you could determine if the stored procedure is natively compiled or not by right clicking on the stored procedure and determining if the "Natively Compiled" option is enabled or not. Refer to the screenshot below.

Next Steps

There are many useful tips available for further reading which are posted below.

• For an overview of In-Memory OLTP, refer to this tip:
  • SQL Server 2014 In Memory OLTP Durability, Natively Compiled Stored Procedures and Transaction Isolation Levels
• Learn about the AMR tool and find out which tables in SQL Server 2016 would benefit from migrating to In-Memory OLTP:
  • Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014
• If you are using SQL Server 2014, refer to this tip:
  • Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables
• For checking tables that have incompatibilities with the In-Memory OLTP feature, refer to this tip:
  • Using In-Memory OLTP migration checklists in SQL Server 2016
• For detailed steps to migrate tables to In-Memory OLTP, refer to this tip:
  • SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP
• For detailed steps to migrate to natively compiled stored procedures, refer to this tip:
  • Migrate to Natively Compiled SQL Server Stored Procedures for Hekaton
• For debugging natively compiled stored procedures, refer to this tip:
  • Debugging Natively-Compiled Stored Procedures Statements
• Guidelines for using natively compiled stored procedures in SSIS packages are described in this tip:
  • Guidelines for Using Natively-Compiled Stored Procedures in SSIS Packages

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

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

View all my tips