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:
- Create a Standard DB with filegroup for InMemory OLTP
- Create a InMemory table
- Create a Native Stored Procedure for our experiment
- Recompile the SP in SQL Server 2016 instance.
Step 1: Create DB
IF DB_ID('InMem_OLTP') IS NOT NULL
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)
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
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)
Step 3: Create an Native SP
CREATE PROCEDURE [dbo].[InsertSalesOrder_Native] @order_status TINYINT = 1,
@amount FLOAT = 100,
@order_count INT = 100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
DECLARE @i INT = 0
WHILE @i < @order_count
INSERT INTO dbo.SalesOrder_inmem VALUES (GETDATE(), @order_status, @amount)
SET @i = @i + 1
Step 4: Recompile SP
-- Can this be recompiled?
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)