监视 Azure SQL 数据库和 Azure SQL 托管实例中的内存中 OLTP 存储Monitor In-Memory OLTP storage in Azure SQL Database and Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

使用内存中 OLTP时,内存优化表中的数据和表变量驻留在内存中 OLTP 存储内。When using In-Memory OLTP, data in memory-optimized tables and table variables resides in In-Memory OLTP storage.

确定数据是否在内存中 OLTP 存储容量限制范围内Determine whether data fits within the In-Memory OLTP storage cap

确定不同服务层级的存储上限。Determine the storage caps of the different service tiers. 每个“高级”和“业务关键”服务层级都具有最大内存中 OLTP 存储大小。Each Premium and Business Critical service tier has a maximum In-Memory OLTP storage size.

估计内存优化表的内存要求,如同在 Azure SQL 数据库和 Azure SQL 托管实例中估计 SQL Server 的内存要求一样。Estimating memory requirements for a memory-optimized table works the same way for SQL Server as it does in Azure SQL Database and 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. 此外,ALTER TABLE 需要足够的空间来创建新版的完整表及其索引。In addition, ALTER TABLE needs enough room to create a new version of the entire table and its indexes.

超过此限制后,在 Azure SQL 数据库的单个数据库和 Azure SQL 托管实例的数据库中,插入和更新操作可能会开始失败,出现错误 41823,在 Azure SQL 数据库的弹性池中也会发生相应的操作失败,并出现错误 41840。Once this limit is exceeded, insert and update operations may start failing with error 41823 for single databases in Azure SQL Database and databases in Azure SQL Managed Instance, and error 41840 for elastic pools in Azure SQL Database. 到时,需要删除数据以回收内存,或升级数据库的服务层级或计算大小。At that point you need to either delete data to reclaim memory, or upgrade the service tier or compute size of your database.

监视和警报Monitoring and alerting

可以在 Azure 门户中,通过计算大小的存储上限百分比来监视内存中存储用量:You can monitor In-memory storage use as a percentage of the storage cap for your compute size in the Azure portal:

  1. 在“数据库”边栏选项卡上,找出“资源使用率”框并单击“编辑”。On the Database blade, locate the Resource utilization box and click on Edit.
  2. 选择指标 In-Memory OLTP Storage percentageSelect the metric In-Memory OLTP Storage percentage.
  3. 如果要添加警报,请单击“资源使用率”框以打开“度量值”边栏选项卡,并单击“添加警报”。To add an alert, click on the Resource Utilization box to open the Metric blade, then click on Add alert.

或者,使用以下查询来显示内存中存储的使用率:Or use the following query to show the In-memory storage utilization:

    SELECT xtp_storage_percent FROM sys.dm_db_resource_stats

更正超过内存中 OLTP 存储的情况 - 错误 41823 和 41840Correct out-of-In-Memory OLTP storage situations - Errors 41823 and 41840

数据库达到内存中 OLTP 存储上限会导致 INSERT、UPDATE、ALTER 和 CREATE 操作失败,出现错误消息 41823(针对单一数据库)或错误 41840(针对弹性池)。Hitting the In-Memory OLTP storage cap in your database results in INSERT, UPDATE, ALTER and CREATE operations failing with error message 41823 (for single databases) or error 41840 (for elastic pools). 这两个错误均会导致活动的事务中止。Both errors cause the active transaction to abort.

错误消息 41823 和 41840 指示数据库或池中的内存优化表和表变量已达到最大的内存中 OLTP 存储大小。Error messages 41823 and 41840 indicate that the memory-optimized tables and table variables in the database or pool have 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 service tier to one with enough in-memory storage for the data you need to keep in memory-optimized tables.

备注

在极少数情况下,错误 41823 和 41840 可能是暂时的,即有足够的可用内存中 OLTP 存储,重试后该操作成功。In rare cases, errors 41823 and 41840 can be transient, meaning there is enough available In-Memory OLTP storage, and retrying the operation succeeds. 因此,我们建议既要监视总体的可用内存中 OLTP 存储,又要在首次遇到错误 41823 或 41840 时重试。We therefore recommend to both monitor the overall available In-Memory OLTP storage and to retry when first encountering error 41823 or 41840. 有关重试逻辑的详细信息,请参阅内存中 OLTP 的冲突检测和重试逻辑For more information about retry logic, see Conflict Detection and Retry Logic with In-Memory OLTP.

后续步骤Next steps

有关监视指南,请参阅使用动态管理视图进行监视For monitoring guidance, see Monitoring using dynamic management views.