locked
InMemory tables RRS feed

  • Question

  • What are the general guidelines when trying to identify the tables that can be upgraded to In-Memory for better performance?

    I have an existing database from from which I am trying to identify the tables that can be memory optimized.

    I tried with couple of tables that were suggested by the transaction performance analysis report , however when I run the workflows and take the average timings, I don't see much difference. 

    I know this is very very open ended question, but at this moment I dont event know what are the right questions to ask :-(


    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, November 28, 2017 6:16 AM

Answers

  • <<What are the general guidelines when trying to identify the tables that can be upgraded to In-Memory for better <<performance?

    Since SQL Server 2016 SQL Server generates report ( via SSMS) potential candidates for the im memory tables

    https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/determining-if-a-table-or-stored-procedure-should-be-ported-to-in-memory-oltp

    Also take a look at the new DMV (query_store_wait_stats)  and tables with high lock occurrences are the best candidates for in memory

    https://www.brentozar.com/archive/2017/04/sql-server-vnext-query_store_wait_stats/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, November 28, 2017 7:55 AM
  • It is very likely that the example you used isn't exactly the SQL that your application is using. This is the problem when you do performance testing. Unless you have a *realistic* workload, then the numbers will not be that relevant for you. A couple of comments.

    You use interop instead of natively compiled stored procedures. You want to have machine code as much as possible!

    A commit for each row means a synchronous I/O for each row. This applies for both disk and memory tables. This is te reason why you see little difference . the commit overhead overshadows the difference. possibly with a more realistic table, you would see differences. Here I include number of columns. Number of indexes (indexes on memory optimized tables are not logged to the transaction log). But there are other aspects as well, like how your exact SQL looks like. And then we have the concurrency issue. The in-memory engine is lock and latch free, and such advantacges you will only see when you have several people hammering the system simulaneously.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 28, 2017 10:24 AM
  • Satheesh --

    As you know, SSMS provides the "Transaction Performance Analysis Reports", which describes the likely benefit and supposed complexity of migrating a table. Assuming you ran it on a system with real-world activity. The benefit is based purely on latching/locking, and can be trusted. 

    However, the "difficulty" rating cannot be trusted. That's because of two reasons:

    1. it's never been upgraded for 2016

    2. each item that it considers is given equal weight, when in fact some are much more difficult than others. An example would be user-defined-datatypes, which are easy to migrate, versus Foreign Keys, which can be impossible to migrate. 

    I've got a blog post on this here:

    http://nedotter.com/archive/2017/06/migrating-tables-to-in-memory-oltp/

    There is a lot more to deploying In-Memory than identifying  tables that are good candidates. It changes things in your database environment that you might not expect. One example would be that CHECKDB ignores memory-optimized tables. There are ways to validate that your in-memory files are not corrupt, but it's an extra step. And it's not possible to recover from any form of corruption for in-memory files. Or the fact that foreign keys and triggers that cannot span on-disk and in-memory tables. 

    Also, the way most people do a POC is not correct: if you test with a single thread against on-disk vs. in-mem, you are not testing the thing that In-Memory will be great at: scaling the write intensive/highly concurrent workload. @TiborK mentioned concurrency with testing, and he's correct.

    The main question to initially determine is: what type of waits is your server currently experiencing? If you are currently waiting on WRITELOG, SOS_SCHEDULER_YIELD, and ASYNC_NETWORK you'll never realize the full benefit of In-Memory OLTP. 



    Ned Otter SQL Solutions www.NedOtter.com

    Tuesday, November 28, 2017 2:29 PM

All replies

  • Also I have a question related to this below thread 

    https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/demonstration-performance-improvement-of-in-memory-oltp

    When I remove the begin tran /commit, I see the transaction running for a long long time where are in with transaction the batch completes with in seconds

    --BEGIN TRAN;  
      WHILE @i <= @rowcount  
      BEGIN;  
        INSERT INTO [dbo].[sql] VALUES (@i, @c);  
        SET @i += 1;  
      END;  
    --COMMIT;  
    


    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, November 28, 2017 6:20 AM
  • <<What are the general guidelines when trying to identify the tables that can be upgraded to In-Memory for better <<performance?

    Since SQL Server 2016 SQL Server generates report ( via SSMS) potential candidates for the im memory tables

    https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/determining-if-a-table-or-stored-procedure-should-be-ported-to-in-memory-oltp

    Also take a look at the new DMV (query_store_wait_stats)  and tables with high lock occurrences are the best candidates for in memory

    https://www.brentozar.com/archive/2017/04/sql-server-vnext-query_store_wait_stats/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, November 28, 2017 7:55 AM
  • Thanks Uri fror your inputs.

    I already looked at the "Transaction Performance Analysis Reports" which is inconclusive for me and query_store_wait_stats comes with SQL 2017 only (I believe).

    Do you have any comment about the example from the below link? The question is If I remove the BEGIN TRAN/COMMIT I dont see a performance gain. any comment where I am going wrong?

    https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/demonstration-performance-improvement-of-in-memory-oltp

    Setup

    go  
    CREATE DATABASE imoltp;    --  Transact-SQL  
    go  
    
    ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
        CONTAINS MEMORY_OPTIMIZED_DATA;  
    
    ALTER DATABASE imoltp ADD FILE  
        (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')  
        TO FILEGROUP imoltp_mod;  
    go  
    
    USE imoltp;  
    go  
    
    go  
    DROP TABLE IF EXISTS sql;  
    DROP TABLE IF EXISTS hash_i;  
    go  
    
    CREATE TABLE [dbo].[sql] (  
      c1 INT NOT NULL PRIMARY KEY,  
      c2 NCHAR(48) NOT NULL  
    );  
    go  
    
    CREATE TABLE [dbo].[hash_i] (  
      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  
    
    go  
    SET STATISTICS TIME OFF;  
    SET NOCOUNT ON;  
    
    TEST

    -- Inserts, one at a time.  
    
    DECLARE @starttime DATETIME2 = sysdatetime();  
    DECLARE @timems INT;  
    DECLARE @i INT = 1;  
    DECLARE @rowcount INT = 100000;  
    DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
    
    
    --BEGIN TRAN;  
      WHILE @i <= @rowcount  
      BEGIN;  
        INSERT INTO [dbo].[sql] VALUES (@i, @c);  
        SET @i += 1;  
      END;  
    --COMMIT;  
    
    SET @timems = datediff(ms, @starttime, sysdatetime());  
    SELECT 'A: 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 [dbo].[hash_i] VALUES (@i, @c);  
          SET @i += 1;  
        END;  
    --COMMIT;  
    
    SET @timems = datediff(ms, @starttime, sysdatetime());  
    SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '  
        + cast(@timems as VARCHAR(10)) + ' ms';  
    GO
    DELETE sql;  
    DELETE hash_i;  
    DELETE hash_c;  
    go  



    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, November 28, 2017 9:12 AM
  • It is very likely that the example you used isn't exactly the SQL that your application is using. This is the problem when you do performance testing. Unless you have a *realistic* workload, then the numbers will not be that relevant for you. A couple of comments.

    You use interop instead of natively compiled stored procedures. You want to have machine code as much as possible!

    A commit for each row means a synchronous I/O for each row. This applies for both disk and memory tables. This is te reason why you see little difference . the commit overhead overshadows the difference. possibly with a more realistic table, you would see differences. Here I include number of columns. Number of indexes (indexes on memory optimized tables are not logged to the transaction log). But there are other aspects as well, like how your exact SQL looks like. And then we have the concurrency issue. The in-memory engine is lock and latch free, and such advantacges you will only see when you have several people hammering the system simulaneously.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 28, 2017 10:24 AM
  • A commit for each row means a synchronous I/O for each row. This applies for both disk and memory tables. This is te reason why you see little difference

    So if the application workflows have a few rows (few tens)being committed, there wont be much of an performance enhancement right?

    At this moment , I am ignoring the  advantage of locks/latches and looking only at CPU and Duration and not seeing much of a advantage there. 


    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, November 28, 2017 10:34 AM
  • Satheesh --

    As you know, SSMS provides the "Transaction Performance Analysis Reports", which describes the likely benefit and supposed complexity of migrating a table. Assuming you ran it on a system with real-world activity. The benefit is based purely on latching/locking, and can be trusted. 

    However, the "difficulty" rating cannot be trusted. That's because of two reasons:

    1. it's never been upgraded for 2016

    2. each item that it considers is given equal weight, when in fact some are much more difficult than others. An example would be user-defined-datatypes, which are easy to migrate, versus Foreign Keys, which can be impossible to migrate. 

    I've got a blog post on this here:

    http://nedotter.com/archive/2017/06/migrating-tables-to-in-memory-oltp/

    There is a lot more to deploying In-Memory than identifying  tables that are good candidates. It changes things in your database environment that you might not expect. One example would be that CHECKDB ignores memory-optimized tables. There are ways to validate that your in-memory files are not corrupt, but it's an extra step. And it's not possible to recover from any form of corruption for in-memory files. Or the fact that foreign keys and triggers that cannot span on-disk and in-memory tables. 

    Also, the way most people do a POC is not correct: if you test with a single thread against on-disk vs. in-mem, you are not testing the thing that In-Memory will be great at: scaling the write intensive/highly concurrent workload. @TiborK mentioned concurrency with testing, and he's correct.

    The main question to initially determine is: what type of waits is your server currently experiencing? If you are currently waiting on WRITELOG, SOS_SCHEDULER_YIELD, and ASYNC_NETWORK you'll never realize the full benefit of In-Memory OLTP. 



    Ned Otter SQL Solutions www.NedOtter.com

    Tuesday, November 28, 2017 2:29 PM
  • Also, the way most people do a POC is not correct: if you test with a single thread against on-disk vs. in-mem, you are not testing the thing that In-Memory will be great at: scaling the write intensive/highly concurrent workload. @TiborK mentioned concurrency with testing, and he's correct.

    Ned , thank you for the elaborate answer. I will go thru the post you have mentioned. 

    and you are correct (and that's exactly I was thinking ) the approach towards POC need to be addressed first and then the other things!!!

    Thank you, 


    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, November 28, 2017 3:42 PM
  • Thanks for all the inputs :-)

    Satheesh
    My Blog | How to ask questions in technical forum

    Thursday, November 30, 2017 4:09 AM
  • Prefer In-Memory tables is good in ELT. ELT Stage tables are best fit to implement as In-memory tables. Since Stage tables life time is till the data is loading and it has huge amount of data it's not required to write on disks and truncate it. Follow below code to implement  In-memory tables.

    ALTER DATABASE DBNAME ADD FILEGROUP MEMORY_OPTIMIZED_DATA CONTAINS MEMORY_OPTIMIZED_DATA 
    ALTER DATABASE DBNAME ADD FILE (name='imoltp_mod2', filename='G:\MEMORY_OPTIMIZED_DATA') TO FILEGROUP MEMORY_OPTIMIZED_DATA 

    ALter Database DBNAME SET MEMORY_OPTIMIZED_ELEVATED_TO_SNAPSHOT=ON


    CREATE TABLE TableName (  
      c1 INT NOT NULL PRIMARY KEY NONCLUSTERED ,  
      c2 NCHAR(48) NOT NULL  
    ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
    go  

    Wednesday, January 3, 2018 10:20 AM