SQL Server Stored Procedure Native Compilation Advisor


By:   |   Updated: 2016-05-19   |   Comments   |   Related: More > 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".

Launch the Native Compilation Advisor in SQL Server Management Studio

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

SQL Server Stored Procedure Native Compilation Advisor Introduction Screen

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

SQL Server Stored Procedure Validation in the Native Compilation Advisor

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.

Unsupported Features in the SQL Server Stored Procedure Native Compilation Advisor

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.

SQL Server Stored Procedure Native Compilation Advisor Report

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.

SQL Server Stored Procedure Native Compilation Advisor Success

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.

Unable to ALTER a stored procedure from non native to native mode

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.

Determine if a Stored Procedure is Natively Compiled in SQL Server Management Studio
Next Steps

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



Last Updated: 2016-05-19


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools