SQL SERVER – Recompiling InMemory OLTP with SQL Server 2016

I wrote an intetresting trivia about InMemory OLTP code previously, SQL SERVER – How to take InMemory – OLTP FileGroup Offline?. Post this one of the reader was curious enough to ask me a counter question. He wrote back stating the recovery of the same is easy but he wanted to know if there is a possibility of recompiling InMemory objects?

Strangely, this got me curious. I wanted to see if anything has changed or is possible with SQL Server 2016 version that I had earlier installed. Immediately I went to my computer to hit the below code to check the possibility of recompiling Native InMemory SP.

Here are the steps:

1. Create a Standard DB with filegroup for InMemory OLTP
2. Create a InMemory table
3. Create a Native Stored Procedure for our experiment
4. Recompile the SP in SQL Server 2016 instance.

Step 1: Create DB

USE MASTER
GO
IF DB_ID('InMem_OLTP') IS NOT NULL
BEGIN
ALTER DATABASE [InMem_OLTP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [InMem_OLTP]
END
CREATE DATABASE [InMem_OLTP]
ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 50MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

Step 2: Create InMemory OLTP

CREATE TABLE dbo.SalesOrder_inmem
(
order_id INT IDENTITY NOT NULL,
order_date DATETIME NOT NULL,
order_status tinyint NOT NULL,
amount FLOAT NOT NULL,
CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Step 3: Create an Native SP

USE InMem_OLTP
GO
CREATE PROCEDURE [dbo].[InsertSalesOrder_Native]
@order_status TINYINT = 1,
@amount FLOAT = 100,
@order_count INT = 100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
DECLARE @i INT = 0
WHILE @i < @order_count
BEGIN
INSERT INTO dbo.SalesOrder_inmem VALUES (GETDATE(), @order_status, @amount)
SET @i = @i + 1
END
END
GO

Step 4: Recompile SP

-- Can this be recompiled?
sp_recompile 'InsertSalesOrder_Native'

Output for the same would be:

Object ‘InsertSalesOrder_Native’ was successfully marked for recompilation.

As you can see, we were able to successfully been able to recompile the Stored procedures without any problem. I have done this experiment on a SQL Server 2016 environment. Do let me know if you have every needed this capability in your environment. I would love to know some of the usecases for the same.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.