In-memory OLTP performance question

Zuluman Ross 61 Reputation points
2020-12-18T14:52:44.05+00:00

Hello experts,

Below query takes close to 54 seconds.

insert into #Table1
select * from Table........

I changed temporary table to an in-memory object,

Create TYPE dbo.Table1_Mem AS TABLE (Col1 INT INDEX IX_col1,col2 DATETIME) WITH (MEMORY_OPTIMIZED = ON)
declare @Table1 dbo.Table1_Mem;

insert into @Table1
select * from Table........

After the change the query is taking 13 seconds. CPU has also reduced. But reads nearly tripled.

Select query is same. Only difference is that In-memory temporary object was used instead of temporary table.

It will be great if I get an advise as why there is a surge in reads when In-memory object is used.

Regards;
Zulu

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2020-12-18T19:33:30.1+00:00

    That is the feature of In-Memory OLTP to provide great performance gains. See In-Memory OLTP Overview.

    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-21T02:52:03.557+00:00

    HI @Zuluman Ross ,

    Thanks for posting here in Microsoft Q&A.

    But reads nearly tripled.

    What did you refer 'reads'? Reads from memory or reads from disk? Could you please provide more details about how to find out this conclusion?

    As mentioned in this article, the primary storage for memory-optimized tables is the main memory. Rows in the table are read from and written to memory. A second copy of the table data is maintained on disk, but only for durability purposes. Data in memory-optimized tables is only read from disk during database recovery (eg. after a server restart).

    In addition, you could also consider to set the Durability to SCHEMA_ONLY to simulate the case of SQL temp table, where the table schema will be recovered only in the case of restart or crash. 

    WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments