Bad performance when inserting to memory optimized table
-
When using normal (not natively compiled) SQL to insert values into a memory-optimized table-valued variable the performance is lower than expected.
There seems to also be some kind of concurrency problem meaning that two different sessions can not execute the code simultaneously even on a multi-core server.
Run the following script to create a test database with some test procedures:
/*
-- Use this code to remove the database when finished testing
use master
go
alter database test1654287 set single_user with rollback immediate
go
drop database test1654287
*/
-- Change the path names below to put the files in some safe location
use master
go
-- Use a database name that is unlikely to clash with any existing database
create database test1654287
on primary
( NAME = N'test1654287', FILENAME = N'D:\DATA\test1654287.mdf' , SIZE = 200MB , FILEGROWTH = 100MB)
LOG ON
( NAME = N'test1654287_log', FILENAME = N'D:\DATA\test1654287_log.ldf' , SIZE = 200MB , FILEGROWTH = 100MB)
COLLATE Finnish_Swedish_100_BIN2;
ALTER DATABASE test1654287 ADD FILEGROUP [test1654287_inmem_data] CONTAINS MEMORY_OPTIMIZED_DATA ;
ALTER DATABASE test1654287 ADD FILE (name='test1654287_inmem_data', filename='D:\DATA\test1654287_inmem') TO FILEGROUP test1654287_inmem_data
ALTER DATABASE [test1654287] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
ALTER DATABASE [test1654287] SET RECOVERY SIMPLE WITH NO_WAIT
go
use test1654287
go
-- create a disk-based table with testdata
if object_id('dbo.table1') is not null drop table dbo.table1
go
select top 1000000
row_number() over (order by (select null)) as rn,
column_id = c1.column_id
into dbo.table1
from sys.all_columns c1
cross join sys.all_columns c2
-- Create a table valued memory type
if type_id('dbo.InputTypeMem') is not null drop type dbo.InputTypeMem
go
CREATE TYPE dbo.InputTypeMem AS TABLE (
rn int not null,
column_id int not null,
INDEX IX_rn HASH (rn) WITH ( BUCKET_COUNT = 10000000)
) WITH (MEMORY_OPTIMIZED = ON)
go
-- Create a table valued diskbased type
if type_id('dbo.InputTypeDisk') is not null drop type dbo.InputTypeDisk
go
CREATE TYPE dbo.InputTypeDisk AS TABLE (
rn int not null,
column_id int not null
)
go
-- Create a procedure that populates the memory based type
if object_id('dbo.TestMem') is not null drop procedure dbo.TestMem
go
create procedure dbo.TestMem
as
declare @input dbo.InputTypeMem
insert into @input (rn, column_id)
select rn,column_id from dbo.table1
go
-- Create a procedure that populates the disk based type
if object_id('dbo.TestDisk') is not null drop procedure dbo.TestDisk
go
create procedure dbo.TestDisk
as
declare @input dbo.InputTypeDisk
insert into @input (rn, column_id)
select rn,column_id from dbo.table1
go
Run Profiler and catch BatchCompleted events
On my machine running TestDisk gives a duration of 380ms and TestMem gives 1200ms
This is bad enough, but it is even worse when you try to run several procedures like this at the same time.
Create two cmd files like this:
testdisk.cmd:
start /b sqlcmd -S . -E -d test1654287 -Q "exec dbo.TestDisk"
start /b sqlcmd -S . -E -d test1654287 -Q "exec dbo.TestDisk"
testmem.cmd:
start /b sqlcmd -S . -E -d test1654287 -Q "exec dbo.TestMem"
start /b sqlcmd -S . -E -d test1654287 -Q "exec dbo.TestMem"
When running TestDisk.cmd, two sessions run dbo.TestDisk at the same time in parallel - total duration 460ms
When running TestMem.cmd, two sessions run dbo.TestMem at the same time in parallel - total duration 2100ms
I have attached a screen dump from running profiler on my system.
So, it seems that there is a serious concurrency problem when two sessions are writing to memory optimized tables at the same time.
I have tested this with both memory optimized table variables, and memory optimized tables - the results are similar.
This is very disappointing to me. I thought the point of memory optimized tables was to increase performance...
Any comments?
-
One thing I have noticed is that your procedure that inserts into the in memory table is reading from a disk based table to do the insert.
Having not used in memory tables outside of trying out the functionality this may be the cause of poor performance. In all my tests I was loading the table from flat files, simple insert into statements with supplied values or naively compiled stored procedures that accepted a set of values.
When comparing those methods with disk tables and in memory tables the inserts were always faster with in memory tables. Obviosuly my tests were a limited set of scenarios though.
Here is a quote from this article that prompted my reply:
https://www.simple-talk.com/sql/performance/the-promise---and-the-pitfalls---of-in-memory-oltp/
"disk-based tables cannot be accessed from natively compiled stored procedures, reducing the performance gains when accessing disk-based tables alongside In-Memory memory tables. Instead, a capability called interop needs to be employed instead, where interpreted Transact-SQL is used to access the memory-optimized table. In this instance, the improvement in performance is typically a factor of three."
I know that your not using natively compiled stored procedures but I thought it was worth mentioning.
MCITP SQL 2005, MCSA SQL 2012
-
I have previously tested this on SQL2014 and SQL2016 CTP2
I just made the same test on SQL2016 CTP3.
It seems like they have fixed this issue in CTP3. It is now possible for multiple threads to efficiently update a memory-based table variable.
This is the corresponding profiler trace from CTP3 on the exact same hardware:
Nice to see the product improve!
/SG
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply