Устранение проблем нехватки памяти в Базе данных SQL Azure

  • Статья

Применимо к:База данных SQL Azure

Сообщения об ошибках могут возникать, когда ядро СУБД SQL не удалось выделить достаточно памяти для выполнения запроса. Это может быть вызвано различными причинами, например ограничениями выбранной цели обслуживания, совокупными требования к рабочей нагрузке и потребностью запроса в памяти. Дополнительные сведения об ограничениях памяти для баз данных SQL Azure см. в статье Управление ресурсами в Базе данных Azure.

Примечание.

Сведения в этой статье относятся к Базе данных SQL Azure. Дополнительную информацию о том, как устранить проблемы с памятью в 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;

Изучите выделение памяти

Если возникают ошибки, связанные с нехваткой памяти в Базе данных SQL Azure, попробуйте хотя бы временно увеличить цель уровня обслуживания базы данных на портале Azure. Если ошибки памяти сохраняются, используйте следующие запросы, чтобы искать необычно высокие объемы памяти запросов, которые могут способствовать недостаточному состоянию памяти. Запустите следующий пример запросов в базе данных, в которой произошла ошибка (а не в базе данных master логического сервера Azure SQL).

Использование динамического административного представления для просмотра событий нехватки памяти

Позволяет sys.dm_os_out_of_memory_events просматривать события и причины нехватки памяти (OOM) в База данных SQL Azure. Расширенное summarized_oom_snapshot событие является частью существующего system_health сеанса событий для упрощения обнаружения. Дополнительные сведения см. в статье sys.dm_os_out_of_memory_events и блоге: новый способ устранения ошибок вне памяти в ядре СУБД.

Используйте динамическое административное представление для просмотра клерков памяти

Если ошибка с потерей памяти возникла недавно, начните с масштабного исследования, просмотрев выделение памяти для клерков памяти. Клерки памяти встроены в ядро СУБД для этой Базы данных SQL Azure. По самым используемым клеркам памяти по количеству выделенных страниц можно определить, какой тип запроса или функция 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 также понадобится много при использовании индексов columnstore. От этих клерков ожидается наибольшее использование.

Дополнительные сведения о типах клерков памяти см. в описании представления sys.dm_os_memory_clerks.

Используйте динамическое административное представление для изучения активных запросов

В большинстве случаев эта ошибка не возникает из-за сбоя запроса.

Следующий пример запроса для Базы данных SQL Azure возвращает важную информацию о транзакциях, которые в настоящее время находятся в удержании или ожидают временного предоставления буфера памяти. Внимательно изучите запросы, предлагаемые для рассмотрения и настройки производительности, и оцените, выполняются ли они по назначению. Подумайте о времени выполнения запросов на отчеты, требующих большого объема памяти или большого числа операций обслуживания.

--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).

Используйте хранилище запросов для изучения прошлых объемов использования памяти запросами

Хотя в предыдущем примере рассматриваются только текущие запросы, в следующем запросе используется хранилище запросов для возврата сведений о выполнении запросов в за прошедшие периоды времени. Это может быть полезно при исследовании связанной с нехваткой памяти ошибки, которая произошла в прошлом.

В приведенном ниже примере запроса для Базы данных SQL Azure возвращаются важные сведения о выполнении запросов, записанные хранилищем запросов. Внимательно изучите запросы, предлагаемые для рассмотрения и настройки производительности, и оцените, выполняются ли они по назначению. Обратите внимание на фильтр по времени для 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;

Расширенные события

Помимо предыдущих сведений, может быть полезно записать трассировку действий на сервере, чтобы тщательно изучить проблему без памяти в База данных SQL Azure.

Существует два способа записи трассировок в SQL Server: расширенные события (XEvents) и трассировки профилировщика. Однако SQL Server Profiler представляет устаревшую технологию трассировки, которая не поддерживается для Базы данных SQL Azure. Расширенные события представляют более новую технологию трассировки, которая отличается большей гибкостью и меньшим влиянием на наблюдаемую систему, а ее интерфейс встроен в SQL Server Management Studio (SSMS). Дополнительные сведения о запросах расширенных событий в Базе данных Azure SQL см. в статье Расширенные события в Базе данных Azure SQL.

См. документ, в котором объясняется, как использовать мастер создания сеанса расширенных событий в SSMS. Однако для баз данных SQL Azure 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 . Уменьшите объем данных в таблицах, оптимизированных для памяти, и параметрах, оптимизированных для памяти, или переведите базу данных на более высокий уровень обслуживания, чтобы получить больше памяти. Дополнительные сведения о проблемах с нехваткой памяти при использовании выполняющейся в памяти OLTP SQL Server см. в статье Устранение проблем нехватки памяти.

Получение поддержки База данных SQL Azure

Если проблемы с нехваткой памяти в Базе данных SQL Azure не удастся устранить, отправьте запрос в службу поддержки Azure, выбрав Получить поддержку на сайте службы поддержки Azure.

Связанный контент