Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Transaction Performance Analysis reports tells you which interpreted stored procedures in your database will benefit if ported to use native compilation. For details see Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP.
After you identify a stored procedure that you would like to port to use native compilation, you can use the Native Compilation Advisor (NCA) to help you migrate the interpreted stored procedure to native compilation. For more information about natively compiled stored procedures, see Natively Compiled Stored Procedures.
In a given interpreted stored procedure, the NCA allows you to identify all the features that are not supported in native modules. The NCA provides documentation links to work-arounds or solutions.
For information about migration methodologies, see In-Memory OLTP - Common Workload Patterns and Migration Considerations.
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.
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.
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.
Note
As usual, the FILEGROUP element, and the USE mydatabase statement, apply to Microsoft SQL Server, but do not apply to Azure SQL Database.
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
-- Interpreted.
CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME2, @status TINYINT
AS
BEGIN
INSERT dbo.SalesOrders VALUES (@id, @date, @status);
END
go
-- Natively Compiled.
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
EXECUTE dbo.InsertOrder @id= 10, @date = '1956-01-01 12:00:00', @status = 1;
EXECUTE dbo.InsertOrderXTP @id= 11, @date = '1956-01-01 12:01:00', @status = 2;
SELECT * from SalesOrders;
Migrating to In-Memory OLTP
Requirements for Using Memory-Optimized Tables
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Create stored procedures and user-defined functions - Training
This content is a part of Create stored procedures and user-defined functions.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.