Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Native Compilation Advisor

Transaction performance reports tool (see Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP) informs you about which interpreted stored procedures in your database will benefit if ported to use native compilation. After you identify a stored procedure that you would like to port to use native compilation, you can use the native compilation advisor to help you migrate the interpreted stored procedure to native compilation. For more information about natively compiled stored procedures, see Introduction to Natively Compiled Stored Procedures.

To begin, connect to the instance that contains the interpreted stored procedure. You can connect to SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, or SQL Server 2014 instance. However, if you wish to perform a migration operation with the advisor, you must connect to a SQL Server 2014 instance on which In-Memory OLTP functionality is enabled. For more information about In-Memory OLTP requirements, see Requirements for Using Memory-Optimized Tables.

In Object Explorer, right click the stored procedure you want to convert, and select Native Compilation Advisor. This will display the welcome page for the Stored Procedure Native Compilation Advisor. Click Next to continue.

Stored Procedure Validation

This page will report if the stored procedure uses any constructs that are not compatible with native compilation. You can click Next to see details. If there are constructs that are not compatible with native compilation, you can click Next to see details.

Stored Procedure Validation Result

If there are constructs that are not compatible with native compilation, the Stored Procedure Validation Result page will display details. You can generate a report (click Generate Report), exit the Native Compilation Advisor, and update your code so that it is compatible with native compilation.

The following sample shows an interpreted stored procedure and the equivalent stored procedure for native compilation. The sample assumes a directory called c:\data.

CREATE DATABASE Demo
ON
PRIMARY(NAME = [Demo_data],
FILENAME = 'C:\DATA\Demo_data.mdf', size=500MB)
, FILEGROUP [Demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [Demo_dir],
FILENAME = 'C:\DATA\Demo_dir')
LOG ON (name = [Demo_log], Filename='C:\DATA\Demo_log.ldf', size=500MB)
COLLATE Latin1_General_100_BIN2;
GO
USE Demo;
GO


CREATE TABLE [dbo].[SalesOrders]
(
     [order_id] [int] NOT NULL,
     [order_date] [datetime] NOT NULL,
     [order_status] [tinyint] NOT NULL

CONSTRAINT [PK_SalesOrders] PRIMARY KEY NONCLUSTERED HASH 
(
     [order_id]
)WITH ( BUCKET_COUNT = 2097152)
)WITH ( MEMORY_OPTIMIZED = ON )

go

CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME2, @status TINYINT
AS 
BEGIN 

  INSERT dbo.SalesOrders VALUES (@id, @date, @status)

END

go

CREATE PROCEDURE [dbo].[InsertOrderXTP] @id INT, @date DATETIME2, @status TINYINT
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
BEGIN ATOMIC WITH 
(    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
     LANGUAGE = N'us_english')

  INSERT dbo.SalesOrders VALUES (@id, @date, @status)

END
go

select * from SalesOrders
go
exec dbo.InsertOrder @id= 10, @date = '1956-01-01 12:00:00', @status = 1 ;
exec dbo.InsertOrderXTP @id= 11, @date = '1956-01-01 12:01:00', @status = 2 ;
select * from SalesOrders
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.