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