Share via

Trying a POC to compare performance of Memory Optimized Tables Vs Disk Based Tables in Azure SQL.

Manish Agarwal 0 Reputation points
2023-04-06T06:12:25.31+00:00

Hi, I am trying to compare Performance of In-Memory OLTP Vs Disk Based Tables in Azure SQL. Have created a DB under Business-Critical Tier (BC_Gen5_2). Every .1 million records take around 130 MB memory space. I am trying to insert .1 million records in single thread. After inserting like .5 million records, I start getting insufficient in-memory quota or insufficient resource pool. I am new to the concept of In-Memory OLTP, and would appreciate if i can get help and insights around the factors to be considered while creating a DB for In-Memory OLTP. Thanks!

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. SSingh-MSFT 16,371 Reputation points Moderator
    2023-04-06T09:01:42.19+00:00

    Hi
    Manish Agarwal     •,

    Welcome to Microsoft Q&A forum and thanks for using Azure services.

    As I understand, you are getting memory quota error when inserting records in Azure SQL Database in memory OLTP and want to now considerations when choosing the same.

    Hitting the In-Memory OLTP storage cap in your database results in INSERT, UPDATE, ALTER and CREATE operations failing with error cause the active transaction to abort.

    To resolve this error, either:

    • Delete data from the memory-optimized tables, potentially offloading the data to traditional, disk-based tables; or,
    • Upgrade the service tier to one with enough in-memory storage for the data you need to keep in memory-optimized tables.

    In rare cases, this error can be transient, meaning there is enough available In-Memory OLTP storage, and retrying the operation succeeds. We therefore recommend to both monitor the overall available In-Memory OLTP storage and to retry when first encountering error 41823 or 41840. For more information about retry logic, see Conflict Detection and Retry Logic with In-Memory OLTP.

    In Memory OLTP details are illustrated here in official blogpost by Microsoft: In-Memory OLTP in Azure SQL Database

    Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know. Thank you.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.