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

  • 發行項

適用於：Azure SQL Database

當 SQL 資料庫引擎無法配置足夠的記憶體來執行查詢時，您可能會看到錯誤訊息。 這可能是由各種原因所造成，包括所選服務目標的限制、彙總工作負載記憶體需求，以及查詢的記憶體需求。 如需 Azure 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 入口網站中資料庫的服務等級目標。 如果記憶體不足錯誤持續發生，請使用下列查詢來尋找異常高的查詢記憶體授與，這些授與可能造成記憶體不足狀況。 在發生錯誤的資料庫中執行下列範例查詢 (不在 master Azure SQL 邏輯伺服器的資料庫中)。

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

sys.dm_os_out_of_memory_events 允許查看 Azure SQL 資料庫中的事件和記憶體不足 (OOM) 事件的原因。 summarized_oom_snapshot 擴充事件是現有 system_health 事件工作階段的一部分，可簡化偵測。 如需詳細資訊，請參閱 sys.dm_os_out_of_memory_events 和部落格：針對資料庫引擎中記憶體不足錯誤進行疑難排解的新方式。

使用 DMV 檢視記憶體 Clerk

從廣泛調查開始，如果最近發生記憶體不足錯誤，請檢視針對 記憶體 Clerk 的記憶體配置。 記憶體 Clerk 是該 Azure SQL 資料庫的資料庫引擎內部。 就配置的頁面而言，最上層的記憶體 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 與記憶體授與無關，但當查詢宣告許多鎖定時，預期會很高，例如因為已停用的鎖定擴大或非常大型的交易。
  • 某些職員預期使用率最高：MEMORYCLERK_SQLBUFFERPOOL 幾乎都是頂級職員，而使用資料行存放區索引時，CACHESTORE_COLUMNSTOREOBJECTPOOL 會比較高。 預期這些 Clerk 的最高使用率。

如需記憶體 Clerk 類型的詳細資訊，請參閱 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 中擷取追蹤：擴充事件 (XEvents) 和 Profiler 追蹤。 然而，SQL Server Profiler 是 Azure SQL Database 不支援的已取代追蹤技術。 擴充事件是較新的追蹤技術，可讓觀察到的系統擁有更多功能且較不受影響，而且其介面已整合至 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 支援要求。

相關內容