針對 Azure SQL Database 中的記憶體不足錯誤進行疑難排解

適用于:Azure SQL資料庫

當 SQL 資料庫引擎無法配置足夠的記憶體來執行查詢時,您可能會看到錯誤訊息。 這可能是由各種原因所造成,包括選取的服務目標限制、彙總工作負載記憶體需求,以及查詢的記憶體需求。 如需 Azure SQL Database 之記憶體資源限制的詳細資訊,請參閱 Azure SQL Database 中的資源管理

注意

本文著重於 Azure SQL Database。 如需針對 SQL Server 中記憶體不足問題進行疑難排解的詳細資訊,請參閱 MSSQLSERVER_701

嘗試下列調查的途徑,以回應:

  • 錯誤碼 701,錯誤訊息:「資源集區 '%ls' 中的系統記憶體不足,無法執行此查詢」。
  • 錯誤碼 802,錯誤訊息:「緩衝集區中的可用記憶體不足」。

檢視記憶體不足事件

如果您發生記憶體不足的錯誤,請檢閱 sys.dm_os_out_of_memory_events。 此檢視在 2022 年 1 月引進,包含由啟發學習演算法決定的預測記憶體不足原因資訊,並提供有限程度的信賴度。

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;  

調查記憶體配置

若 Azure SQL Database 中存在記憶體不足的錯誤,請考慮至少暫時增加 Azure 入口網站中資料庫的服務等級目標。 若記憶體不足的錯誤仍然存在,請使用以下查詢,尋找可能導致發生記憶體不足條件的異常高查詢記憶體授與。 在遇到錯誤的資料庫中執行下列範例查詢 (不是在 Azure SQL 邏輯伺服器的 master 資料庫中)。

使用 DMV 檢視記憶體不足事件

從 2022 年 4 月開始,已新增新的動態管理檢視 (DMV),以允許在 Azure SQL Database sys.dm_os_out_of_memory_events 中顯示記憶體不足 (OOM) 事件及其原因。 如需詳細資訊,請參閱 sys.dm_os_out_of_memory_events

使用 DMV 檢視記憶體 Clerk

從廣泛的調查開始,若最近發生記憶體不足的錯誤,請檢視記憶體 Clerk 的記憶體配置。 記憶體 Clerk 位於此 Azure SQL Database 的資料庫引擎內部。 所配置頁面最上層的記憶體 Clerk 可能會有 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;
  • 某些常見的記憶體 Clerk (例如 MEMORYCLERK_SQLQERESERVATIONS) 最適合的解決方式是找出具有大量記憶體授與的查詢,並透過更好的索引與索引調整來改善其效能。
  • 雖然 OBJECTSTORE_LOCK_MANAGER 與記憶體授與無關,但是當查詢宣告許多鎖定 (例如,因為停用的鎖定擴大或超大型交易) 時,必須是高度記憶體授與。
  • 某些 Clerk 必須是最高使用率:MEMORYCLERK_SQLBUFFERPOOL 幾乎一律是最上層的 Clerk,而當使用資料行存放區索引時,CACHESTORE_COLUMNSTOREOBJECTPOOL 會很高。 這些 Clerk 必須是最高使用率。

如需記憶體 Clerk 類型的詳細資訊,請參閱 sys.dm_os_memory_clerks

使用 DMV 調查使用中查詢

在大部分情況下,查詢失敗並非造成此錯誤的原因。

下列 Azure SQL Database 的範例查詢會傳回目前保留或等候記憶體授與之交易的重要資訊。 以找出檢查與效能調整的熱門查詢為目標,並評估這些查詢是否如預期般執行。 請考慮記憶體密集型報告查詢或維護作業的時間。

--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 Database 的範例查詢會傳回查詢存放區所記錄之查詢執行的重要資訊。 以找出檢查與效能調整的熱門查詢為目標,並評估這些查詢是否如預期般執行。 請注意 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 Database 中的記憶體不足問題。

有兩種方式可以在 SQL Server 中擷取追蹤;擴充事件 (XEvents) 與 Profiler 追蹤。 然而,SQL Server Profiler 是 Azure SQL Database 不支援的已取代追蹤技術。 擴充事件是較新的追蹤技術,可讓觀察到的系統擁有更多功能且較不受影響,而且其介面已整合至 SQL Server Management Studio (SSMS)。 如需在 Azure SQL Database 中查詢擴充事件的詳細資訊,請參閱 Azure SQL Database 中的擴充事件

請參閱說明如何在 SSMS 中使用 [擴充事件新增工作階段精靈] 的文件。 然而,針對 Azure SQL 資料庫,SSMS 會在物件總管中的每個資料庫下提供擴充事件子資料夾。 使用擴充事件工作階段擷取這些實用的事件,並找出產生這些事件的查詢:

  • 類別錯誤:

    • error_reported
    • exchange_spill
    • hash_spill_details
  • 類別執行:

    • excessive_non_grant_memory_used
  • 類別記憶體:

    • query_memory_grant_blocking
    • query_memory_grant_usage

擷取記憶體授與區塊、記憶體授與溢出或過多的記憶體授與,可能會導致查詢突然耗用比過去更多的記憶體,而且可能會說明現有工作負載中的緊急記憶體不足錯誤。

記憶體內部 OLTP 記憶體不足

若使用記憶體內部 OLTP,您可能會遇到 Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation。 減少記憶體最佳化資料表與記憶體最佳化資料表值參數中的資料量,或是將資料庫擴大至較高的服務目標,以擁有更多記憶體。 如需有關 SQL Server 記憶體內部 OLTP 中記憶體不足問題的詳細資訊,請參閱解決記憶體不足問題

取得 Azure SQL DB 支援

若 Azure SQL Database 中存在記憶體不足錯誤,請藉由選取 Azure 支援網站上的 [取得支援] 來提出 Azure 支援要求。

後續步驟