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.

Learn how to use SQL SERVER, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL SERVER Training Demo!

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:

Enable In-Memory OLTP The Transact-SQL statements create a database named In-MemoryOLTP and also add a memory-optimized filegroup container and filegroup to the database.
Explore SQL SERVER Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

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

 MindMajix YouTube Channel

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:

 SSIS Power BI
 SSRS SharePoint
 SSAS SQL Server DBA
 SCCM BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
SQL Server TrainingDec 07 to Dec 22View Details
SQL Server TrainingDec 10 to Dec 25View Details
SQL Server TrainingDec 14 to Dec 29View Details
SQL Server TrainingDec 17 to Jan 01View Details
Last updated: 04 Apr 2023
About Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.

read less