Edit

Monitor in-memory OLTP storage in Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

With in-memory OLTP, data in memory-optimized tables and table variables resides in in-memory OLTP storage.

Determine whether data fits within the in-memory OLTP storage cap

The Business Critical service tier includes a certain amount of Max In-Memory OLTP memory, determined by the number of vCores.

Estimating memory requirements for a memory-optimized table works the same way for SQL Server as it does in Azure SQL Managed Instance. Take a few minutes to review Estimate memory requirements.

Table and table variable rows, as well as indexes, count toward the max user data size. In addition, ALTER TABLE needs enough room to create a new version of the entire table and its indexes.

Once this limit is exceeded, insert and update operations might start failing with error 41823.

Correct out-of-memory OLTP storage situations - error 41823

Meeting the in-memory OLTP storage cap in your database results in INSERT, UPDATE, ALTER and CREATE operations failing with error 41823. This error can cause the active transaction to abort.

Error 41823 indicate that the memory-optimized tables and table variables in the instance reached the maximum in-memory OLTP storage size.

To resolve this error, either:

  • Delete data from the memory-optimized tables, potentially offloading the data to traditional, disk-based tables; or,
  • Upgrade the vCore count add in-memory storage for the data you need to keep in memory-optimized tables.

Note

In rare cases, error 41823 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. For more information about retry logic, see Conflict Detection and Retry Logic with in-memory OLTP.

Monitor with DMVs

  • By monitoring the memory consumption periodically, you can determine how memory consumption is growing and how much head room you have left in the resource limits. Identify how much memory is being consumed by the objects in your database or instance. For example, the DMVs sys.dm_db_xtp_table_memory_stats or sys.dm_os_memory_clerks.

    • You can find memory consumption for all user tables, indexes, and system objects by querying sys.dm_db_xtp_table_memory_stats:

      SELECT object_name(object_id) AS [Name], *  
   FROM sys.dm_db_xtp_table_memory_stats;

    • Memory allocated to in-memory OLTP engine and the memory-optimized objects is managed the same way as any other memory consumer within a database. The memory clerks of type MEMORYCLERK_XTP accounts for all the memory allocated to the in-memory OLTP engine. Use the following query on sys.dm_os_memory_clerks to find all the memory used by the in-memory OLTP engine, including memory dedicated to specific databases.

      -- This DMV accounts for all memory used by the in-memory engine  
SELECT [type], [name]
     , memory_node_id  
     , pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks 
WHERE [type] LIKE '%xtp%';

      type                 name       memory_node_id pages_MB  
-------------------- ---------- -------------- --------------------  
MEMORYCLERK_XTP      Default    0              18  
MEMORYCLERK_XTP      DB_ID_5    0              1358  
MEMORYCLERK_XTP      Default    64             0

  • You can also get more information about out of memory errors in Azure SQL Managed Instance with the dynamic management view sys.dm_os_out_of_memory_events. For example:

    SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

    For more information, see Monitor and troubleshoot in-Memory OLTP memory usage.

Related content