How to Implement In-Memory OLTP SQL Server

Using In-Memory OLTP

The following examples outline how to use In-Memory OLTP and memory-optimized tables to improve the performance of OLTP applications through efficient, memory-optimized data access and native compilation of business logic.

Enabling In-Memory OLTP in a database

We are going to add a filegroup for memory_optimized_data to our database, and add a container to this filegroup. This filegroup will be used to guarantee the durability of memory-resident data in the event of a server crash or restart. During the crash recovery phase in server startup, the data is recovered from this filegroup and loaded back into memory.

When creating the container in the memory_optimized_data filegroup, you must specify the storage location. Make sure the folder exists before running the script.

In-Memory OLTP must be enabled in a database before the new capabilities can be employed. Enable In-Memory OLTP by using the following Transact-SQL statements:

The Transact-SQL statements create a database named In-MemoryOLTP and also add a memory-optimized filegroup container and filegroup to the database.

Create a memory-optimized table and natively compile stored procedures

With the filegroup and filegroup container added to the database, the next step is to create a memory-optimized table in the sample database and natively compile the stored procedures to reduce the instructions needed and improve performance. The following sample code executes this step and also creates a memory-optimized index:

use [In-MemoryOLTP] go

create table [sql]

(

c1 int not null primary key,

c2 nchar(48) not null

)

go

create table [hash]

(

c1 int not null primary key nonclustered hash with (bucket_count=1000000), c2 nchar(48) not null

) with (memory_optimized=on, durability = schema_only) go

create table [hash1]

(

c1 int not null primary key nonclustered hash with (bucket_count=1000000), c2 nchar(48) not null

) with (memory_optimized=on, durability = schema_only) go

CREATE PROCEDURE yy @rowcount int, @c nchar(48)

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC

WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’) declare @i int = 1

while @i <= @rowcount begin

INSERT INTO [dbo].[hash1] values (@i, @c) set @i += 1

end

END

GO

Execute queries to demonstrate performance when using memory-optimized tables

In-Memory OLTP introduces memory-optimized tables and natively compiled stored procedures in SQL Server.

Now that the database, memory-optimized tables, and stored procedures are created, it’s time to evaluate the performance gains by executing the following script and comparing the processing times of the disk-based table and interpreted Transact-SQL, the memory-optimized table with the hash index and interpreted Transact-SQL, and the memory-optimized table with the hash index and a natively compiled stored procedure.

set statistics time off set nocount on

— inserts – 1 at a time

declare @starttime datetime2 = sysdatetime(), @timems int

declare @i int = 1

declare @rowcount int = 100000

declare @c nchar(48) = N’12345678901234567890123456789012345678′

—————————–

— disk-based table and interpreted Transact-SQL

—————————–

begin tran

while @i <= @rowcount begin

insert into [sql] values (@i, @c) set @i += 1

end commit

set @timems = datediff(ms, @starttime, sysdatetime())

select ‘Disk-based table and interpreted Transact-SQL: ‘ + cast(@timems as v ‘ ms’

—————————–

— Interop Hash

—————————–

set @i = 1

set @starttime = sysdatetime() begin tran

while @i <= @rowcount begin

insert into [hash] values (@i, @c) set @i += 1

end commit

set @timems = datediff(ms, @starttime, sysdatetime())

select ‘ memory-optimized table with hash index and interpreted Transact-SQL timems as varchar(10)) + ‘ ms’

—————————–

— Compiled Hash

—————————–

set @starttime = sysdatetime() exec yy @rowcount, @c

set @timems = datediff(ms, @starttime, sysdatetime())

select ‘memory-optimized table with hash index and native Stored Procedure:’ timems as varchar(10)) + ‘ ms’

The processing times are illustrated in the results window of SQL Server Management Studio. Using commodity hardware such as eight virtual processors and 14 GB of RAM, the processing time of the disk-based table and interpreted Transact-SQL was 3,219ms. The memory-optimized table with a hash index and interpreted Transact-SQL took 1015ms, and the memory-optimized table with a hash index and natively compiled stored procedure took 94ms. This clearly demonstrates a significantly faster processing time—approximately 34 times faster.

Frequently Asked SQL SERVER Interview Questions & Answers

List of Related Microsoft Certification Courses: