Compartir a través de

排查 Azure SQL 数据库的内存不足错误

  • Artículo

适用于：Azure SQL 数据库

当 SQL 数据库引擎无法分配足够的内存来运行查询时，你可能会看到错误消息。 出错的原因多种多样，包括所选服务目标的限制、聚合工作负载内存需求，以及查询内存需求。

有关 Azure SQL 数据库内存资源限制的详细信息，请参阅 Azure SQL 数据库中的资源管理。

有关排查 SQL Server 中内存不足问题的详细信息，请参阅 MSSQLSERVER_701。

尝试采取以下调查手段来应对以下错误：

  • 错误代码 701，错误消息为“资源池 '%ls' 中的系统内存不足，无法运行此查询。”
  • 错误代码 802，错误消息为“缓冲池中的可用内存不足”。

查看内存不足事件

如果遇到内存不足错误，请查看 sys.dm_os_out_of_memory_events。 此视图包含预测的内存不足原因信息，这些信息由一种启发式算法确定，可信度有限。

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

调查内存分配

如果 Azure SQL 数据库中仍旧出现内存不足错误，请至少考虑在 Azure 门户中暂时提高数据库的服务级别目标。

如果内存不足错误仍旧存在，请使用以下查询来查找可能导致内存不足状况的异常高的查询内存授予。 在出错的数据库（而不是 Azure SQL 逻辑服务器的 master 数据库）中运行以下示例查询。

使用 DMV 查看内存不足事件

sys.dm_os_out_of_memory_events 实现了对 Azure SQL 数据库中内存不足 (OOM) 事件及其原因的可见性。 summarized_oom_snapshot 扩展事件是现有 system_health 事件会话的一部分，用于简化检测。 有关详细信息，请参阅 sys.dm_os_out_of_memory_events 和博客文章：排查数据库引擎中内存不足错误的新方法。

使用 DMV 查看内存分配器

如果内存不足错误是最近才发生的，请首先查看内存分配器中的内存分配，开始进行广泛调查。 内存分配器位于此 Azure SQL 数据库的数据库引擎内部。 在分配的页面数方面，排在前面的内存分配器可能只会提供参考信息，从中可以判断 SQL Server 的哪种查询或功能消耗了大部分内存。

SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY pages_kb DESC;
GO
SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY virtual_memory_committed_kb DESC;
  • 对于一些常见的内存分配器，例如 MEMORYCLERK_SQLQERESERVATIONS，最好是通过识别内存授予较大的查询并通过更合理的索引编制和索引优化来提高其性能，来解决这些内存分配器中出现的问题。
  • 虽然 OBJECTSTORE_LOCK_MANAGER 与内存授予无关，但当查询声明许多的锁时（例如，由于锁升级已禁用或事务很大），该分配器的值预期会变得很高。
  • 某些分配器预期会出现最高利用率：MEMORYCLERK_SQLBUFFERPOOL 几乎总是排在最前面的分配器，而 CACHESTORE_COLUMNSTOREOBJECTPOOL 会在使用列存储索引时变得很高。 这些分配器预期会达到最高利用率。

有关内存分配器类型的详细信息，请参阅 sys.dm_os_memory_clerks。

使用 DMV 调查活动查询

在大多数情况下，失败的查询不是此错误的原因。

以下针对 Azure SQL 数据库的示例查询返回有关当前正在持有或等待内存授予的事务的重要信息。 将确定要检查并优化性能的前几个查询定为目标，评估它们的执行是否符合预期。 考虑内存密集型报告查询或维护操作的时间安排。

--Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb
, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan
, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads
, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;

你可能会决定使用 KILL 语句停止当前正在占用或等待大量内存授权的执行中查询。 请慎用此语句，尤其是当关键进程正在运行时。 有关详细信息，请参阅 KILL (Transact-SQL)。

使用查询存储调查过去的查询内存使用情况

前面的示例查询仅报告实时查询结果，而以下查询使用查询存储来返回有关以往查询执行的信息。 这有助于调查过去发生的内存不足错误。

以下针对 Azure SQL 数据库的示例查询返回有关查询存储记录的查询执行的重要信息。 将确定要检查并优化性能的前几个查询定为目标，评估它们的执行是否符合预期。 请注意 qsp.last_execution_time 的时间筛选器，它会将结果限制为最近的历史记录。 可以根据环境调整 TOP 子句以生成更多或更少的结果。

SELECT TOP 10 PERCENT --limit results
  a.plan_id, query_id, plan_group_id, query_sql_text
, query_plan = TRY_CAST(query_plan as XML)
, avg_query_max_used_memory
, min_query_max_used_memory
, max_query_max_used_memory
, last_query_max_used_memory 
, last_execution_time  
, query_count_executions
    FROM (
    SELECT 
      qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    , last_execution_time = MAX(qsp.last_execution_time)
    , query_count_executions = SUM(qsrs.count_executions) 
    , avg_query_max_used_memory = AVG(qsrs.avg_query_max_used_memory)
    , min_query_max_used_memory = MIN(qsrs.min_query_max_used_memory)
    , max_query_max_used_memory = MAX(qsrs.max_query_max_used_memory)
    , last_query_max_used_memory = MAX(qsrs_latest.last_query_max_used_memory) --only from latest result
    FROM sys.query_store_plan AS qsp 
    INNER JOIN sys.query_store_query AS qsq
        ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id 
    INNER JOIN sys.query_store_runtime_stats AS qsrs
        ON qsp.plan_id = qsrs.plan_id 
    INNER JOIN (SELECT plan_id
            , last_query_max_used_memory 
            , rownum = ROW_NUMBER() OVER (PARTITION BY plan_id ORDER BY last_execution_time DESC)
            FROM sys.query_store_runtime_stats qsrs) AS qsrs_latest
        ON qsrs_latest.plan_id = qsp.plan_id
        AND qsrs_latest.rownum = 1 --use latest last_query_max_used_memory per plan_id
    WHERE DATEADD(hour, -24, sysdatetime()) < qsp.last_execution_time --past 24 hours only
    AND qsrs_latest.last_query_max_used_memory > 0
    GROUP BY qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    ) AS a
ORDER BY max_query_max_used_memory DESC, avg_query_max_used_memory DESC;

扩展的事件

除了上述信息之外，捕获服务器上的活动跟踪以彻底调查 Azure SQL 数据库中的内存不足问题也会有帮助。

在 SQL Server 中捕获跟踪有两种方法：扩展事件 (XEvent) 和探查器跟踪。 但是，SQL Server Profiler 是已被弃用的跟踪技术，Azure SQL 数据库不支持这种技术。 扩展事件是一种较新的跟踪技术，它支持更多的通用性，对观察到的系统的影响较小，并且它的接口集成到 SQL Server Management Studio (SSMS) 中。 有关查询 Azure SQL 数据库中的扩展事件的详细信息，请参阅 Azure SQL 数据库中的扩展事件。

请参阅说明如何在 SSMS 中使用扩展事件新建会话向导的文档。 但对于 Azure SQL 数据库，SSMS 在对象资源管理器中的每个数据库下都提供了扩展事件子文件夹。 使用扩展事件会话捕获这些有用的事件，并确定生成这些事件的查询：

  • 分类错误：

    • error_reported
    • exchange_spill
    • hash_spill_details

  • 分类执行：

    • excessive_non_grant_memory_used

  • 类别内存：

    • query_memory_grant_blocking
    • query_memory_grant_usage

  • summarized_oom_snapshot

    捕获内存授予阻塞、内存授予溢出或过度内存授予可能会获得查询为何突然比过去占用更多内存的潜在线索，以及现有工作负载中开始出现的内存不足错误的潜在解释。 summarized_oom_snapshot 扩展事件是现有 system_health 事件会话的一部分，用于简化检测。 有关详细信息，请参阅博客文章：排查数据库引擎中内存不足错误的新方法。

内存中 OLTP 内存不足

如果使用内存中 OLTP，可能会遇到 Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation。 减小内存优化表中的数据量和内存优化表值参数，或将数据库扩展到更高的服务目标以获得更多内存。 有关 SQL Server 内存中 OLTP 内存不足问题的详细信息，请参阅解决内存不足问题。

获取 Azure SQL 数据库支持

如果 Azure SQL 数据库中仍旧出现内存不足错误，请在 Azure 支持站点上选择“获取支持”以提交 Azure 支持请求。

相关内容