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.
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:
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
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
SSIS | Power BI |
SSRS | SharePoint |
SSAS | SQL Server DBA |
SCCM | BizTalk Server |
Team Foundation Server | BizTalk Server Administrator |
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:
Name | Dates | |
---|---|---|
SQL Server Training | Dec 07 to Dec 22 | View Details |
SQL Server Training | Dec 10 to Dec 25 | View Details |
SQL Server Training | Dec 14 to Dec 29 | View Details |
SQL Server Training | Dec 17 to Jan 01 | View Details |
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.