Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

Demonstration: Performance Improvement of In-Memory OLTP

The code sample in this topic demonstrates memory-optimized tables. This performance improvement is evident when data in a memory-optimized table is accessed from traditional, interpreted Transact-SQL. This performance improvement is even greater when data in a memory-optimized table is accessed from a natively compiled stored procedure.

Another sample demonstrating memory-optimized tables is available at SQL Server 2014 In-Memory OLTP Sample.

This sample is single-threaded and does not take advantage of the concurrency benefits of In-Memory OLTP. A workload that uses concurrency will see a greater performance gain. This sample shows only one aspect of performance improvement, data access efficiency for insert.

The performance improvement offered by memory-optimized tables is fully realized when data in a memory-optimized table is accessed from a natively compiled stored procedure.

First create a database that has a memory-optimized file group. This sample assumes a directory called c:\data.

CREATE DATABASE imoltp2
ON
PRIMARY(NAME = [imoltp2_data],
FILENAME = 'c:\data\imoltp2_mod1.mdf', size=500MB)
, FILEGROUP [imoltp2_mod] CONTAINS MEMORY_OPTIMIZED_DATA( -- name of the memory-optimized filegroup
NAME = [imoltp2_dir],  -- logical name of a memory-optimized filegroup container
FILENAME = 'c:\data\imoltp2_dir') -- physical path to the container


LOG ON (name = [imoltp2_log], Filename='C:\data\imoltp2_log.ldf', size=500MB)
GO

Next, let's create the tables and the natively compiled stored procedure.

use imoltp2
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'xx')
   DROP PROCEDURE xx
GO


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sql')
   DROP TABLE sql
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash')
   DROP TABLE hash
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash1')
   DROP TABLE hash1
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_and_data)
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_and_data)
go

CREATE PROCEDURE xx 
       @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

Now we can execute the queries that will demonstrate the performance of memory-optimized tables. Before you execute the following code sample, you must execute the previous code sample, which drops the existing tables and natively compiled stored procedure, and recreates them.

Discard the performance of the first execution, which is negatively affected by initial memory allocation.

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 varchar(10)) + ' 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 w/ hash index and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'

-----------------------------
--- Compiled Hash
-----------------------------
set @starttime = sysdatetime()

exec xx @rowcount, @c

set @timems = datediff(ms, @starttime, sysdatetime())
select 'memory-optimized table w/hash index and native SP:' + cast(@timems as varchar(10)) + ' ms'
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.