Bewerken

Delen via

Monitor In-Memory OLTP storage in Azure SQL Database

  • Artikel

Applies to: Azure SQL Database

With In-Memory OLTP, data in memory-optimized tables and table variables resides in the In-Memory OLTP storage, which is a portion of the database memory set aside for in-memory data.

Determine whether data fits within the In-Memory OLTP storage cap

Determine the storage caps of the different service objectives. Each Premium and Business Critical service objective has a maximum In-Memory OLTP storage size.

Estimating memory requirements for a memory-optimized table works the same way for SQL Server as it does in Azure SQL Database. Review Estimate memory requirements.

Table and table variable rows, as well as indexes, count toward the cap. In addition, ALTER TABLE statements need enough memory to create a new version of the entire table and its indexes.

Once the cap is reached, insert and update operations might start failing. At that point you need to either delete data to reclaim memory, or scale up the service objective of your database or elastic pool. For more information, see Correct out-of-In-memory OLTP storage situations - Errors 41823 and 41840.

Monitor and alert

You can monitor In-Memory OLTP storage use as a percentage of the storage cap for the service objective in the Azure portal:

  1. On the Overview page of your SQL database, select the chart in the Monitoring page. Or, in the navigation menu, locate Monitoring and select Metrics.
  2. Select Add metric.
  3. Under Basic, select the metric In-memory OLTP Storage percent.
  4. To add an alert, select on Resource Utilization box to open the Metric page, then select New alert rule. Follow the instructions to create a metric alert rule.

Or, use the following query to show the in-memory storage utilization:

SELECT xtp_storage_percent 
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

Resolve out-of-memory errors with In-Memory OLTP

Reaching the In-Memory OLTP storage cap in your database or elastic pool might result in the INSERT, UPDATE, ALTER and CREATE statements failing with error 41823 (for single databases) or error 41840 (for elastic pools). Both errors cause the active transaction to abort.

Errors 41823 and 41840 indicate that the size of memory-optimized tables and table variables in the database or elastic pool reached the maximum In-Memory OLTP storage size.

To resolve these errors, either:

  • Delete data from the memory-optimized tables, potentially offloading the data to traditional, disk-based tables; or,
  • Upgrade the service objective to one with enough In-Memory OLTP storage for the data you need to keep in memory-optimized tables and table variables.

Note

In rare cases, errors 41823 and 41840 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.

Monitor with DMVs

  • By monitoring memory consumption proactively, you can determine how memory consumption is growing and how much headroom you have left toward the resource limits. Identify how much memory is being consumed by the objects in your database or instance. You can use the sys.dm_db_xtp_table_memory_stats or sys.dm_os_memory_clerks DMVs.

    • 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 the In-Memory OLTP engine and the memory-optimized objects is managed the same way as other memory consumers within a database. The memory clerks of type MEMORYCLERK_XTP account for all the memory allocated to the In-Memory OLTP engine. Use the following query 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 OLTP 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 Database 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;

Related content