Устранение проблем с нехваткой памяти или недостаточным объемом памяти в SQL Server

Сводка

В этой статье описывается, как устранять проблемы с памятью в SQL Server, включая ошибки нехватки памяти и ошибки, связанные со сбоем выделения памяти, состояния нехватки памяти и связанные с этим проблемы производительности. В нем описываются симптомы, три основные категории давления на память (внешние, внутренние из модулей, отличных от модулей, а также внутренние из компонентов подсистемы SQL Server), средства диагностики, которые можно использовать для сбора данных, и шаги, которые можно предпринять для устранения или облегчения давления памяти на экземпляре SQL Server.

Симптомы ошибок из-за нехватки памяти

SQL Server использует сложную архитектуру памяти, соответствующую сложному и богатому набору функций. Из-за разнообразия потребностей в памяти потребление памяти и дефицит памяти могут быть вызваны многими источниками, что может привести к состояниям нехватки памяти.

Распространенные ошибки указывают на низкую память в SQL Server. Ниже приведены примеры следующих ошибок:

• 701. Сбой выделения достаточного объема памяти для выполнения запроса.
• 802. Сбой получения памяти для выделения страниц в буферном пуле (страницы данных или индексов).
• 1204: сбой выделения памяти для блокировок.
• 6322: сбой выделения памяти для средства синтаксического анализа XML.
• 6513: Не удалось инициализировать CLR из-за нехватки памяти.
• 6533: AppDomain выгружен из-за нехватки памяти.
• 8318: сбой загрузки счетчиков производительности SQL из-за нехватки памяти.
• 8356 или 8359: трассировка ETW или SQL не выполняется из-за низкой памяти.
• 8556: сбой загрузки MSDTC из-за нехватки памяти.
• 8645: сбой выполнения запроса из-за отсутствия памяти для предоставления памяти (сортировка и хэширование).
• 8902: сбой выделения памяти во время выполнения DBCC.
• 9695 или 9696: сбой выделения памяти для операций Service Broker.
• 17131 или 17132: сбой запуска сервера из-за нехватки памяти.
• 17890: сбой выделения памяти из-за того, что память SQL выстраивается операционной системой.
• 18053: ошибка печатается в режиме terse, так как во время форматирования произошла ошибка. Трассировка, ETW и уведомления пропускаются.
• 22986 или 22987: изменение сбоев записи данных из-за нехватки памяти.
• 25601: модулю Xevent недостаточно памяти.
• 26053: сетевые интерфейсы SQL не могут инициализироваться из-за нехватки памяти.
• 30085, 30086, 30094: сбой полнотекстовых операций SQL из-за нехватки памяти.

Причина проблем с низкой памятью

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

Давление на внешнюю или операционную память

Внешнее давление относится к высокой загрузке памяти от компонента за пределами процесса SQL Server, что приводит к нехватке памяти для SQL Server. Проверьте, потребляют ли другие приложения в системе память и способствуют низкой доступности памяти. SQL Server является одним из немногих приложений, предназначенных для реагирования на давление памяти ОС, уменьшая использование памяти. Если приложение или драйвер запрашивает память, ОС отправляет сигнал всем приложениям, чтобы освободить память, и SQL Server реагирует, уменьшая собственное использование памяти. Мало какие другие приложения реагируют, поскольку они не предназначены для отслеживания такого уведомления. Если SQL Server снижает использование памяти, пул памяти сжимается, а компоненты, которым требуется память, могут не получить его. В результате вы начинаете получать 701 или другие ошибки, связанные с памятью. Дополнительные сведения о том, как SQL Server динамически выделяет и освобождает память, см. в статье SQL Server архитектура памяти. Подробные сведения о диагностике и решениях см. в статье о давлении внешней памяти .

Три широких категории проблем могут привести к нехватке памяти ОС:

• Проблемы, связанные с приложениями: один или несколько приложений вместе исчерпают доступную физическую память. ОС реагирует на новые запросы приложений для ресурсов, пытаясь освободить некоторую память. Найдите приложения, которые исчерпаны памятью, и выполните действия по балансировке памяти между ними без исчерпания ОЗУ.
• Проблемы с драйвером устройства: драйверы устройств могут вызвать разбиение на страницы всех процессов, если драйвер неправильно вызывает функцию выделения памяти.
• Проблемы с продуктом операционной системы.

Подробное описание и устранение неполадок см. в MSSQLSERVER_17890.

Внутренняя нехватка памяти со стороны модулей, не входящих в состав движка

Нехватка памяти может быть вызвана факторами внутри процесса SQL Server. Некоторые компоненты, работающие внутри процесса SQL Server, являются внешними по отношению к механизму SQL Server. Примерами являются поставщики OLE DB (библиотеки DLL), такие как связанные серверы, процедуры ИЛИ функции SQLCLR, расширенные процедуры (XPS) и ole Automation (sp_OA*). Другие включают антивирусную программу или программы безопасности, которые внедряют библиотеки DLL в процесс для мониторинга. Проблема или плохое проектирование в любом из этих компонентов может привести к большому потреблению памяти. Например, рассмотрим кэширование связанных серверов с 20 миллионами строк данных из внешнего источника в память SQL Server. С точки зрения компонента СУБД ни один клерк памяти не сообщает о высоком использовании памяти, однако объем памяти, потребляемой внутри процесса SQL Server, велик. Этот рост потребления памяти в DLL связанного сервера приводит к тому, что SQL Server начинает сокращать использование памяти и создает условия нехватки памяти для компонентов ядра СУБД, что приводит к ошибкам нехватки памяти. Подробные сведения о диагностике и способах устранения см. в разделе Нехватка внутренней памяти из-за модулей, не относящихся к ядру.

Внутреннее давление на память от компонентов ядра SQL Server

Внутреннее давление на память от компонентов внутри подсистемы SQL Server также может привести к ошибкам вне памяти. Сотни компонентов, которые отслеживаются с помощью диспетчеров памяти, выделяют память в SQL Server. Определите, какие клерки памяти отвечают за самые большие выделения для устранения проблемы. Например, если компонент OBJECTSTORE_LOCK_MANAGER memory clerk показывает большое выделение памяти, выясните, почему Lock Manager потребляет так много памяти. Вам могут встретиться запросы, использующие большое количество блокировок. Оптимизируйте эти запросы с помощью индексов, сокращая транзакции, удерживающие блокировки в течение длительного времени, или проверяя, отключена ли эскалация блокировки. Каждый клерк или компонент памяти имеет уникальный способ использования памяти. Дополнительные сведения см. в sys.dm_os_memory_clerks и описаниях типов диспетчеров памяти. Подробные сведения о диагностике и решениях см. в разделе Использование внутренней памяти компонентом SQL Server Database Engine.

Типы давления памяти

На следующем графике показаны типы давления, которые могут привести к нехватке памяти в SQL Server:

Средства диагностики для устранения неполадок с памятью

Используйте следующие средства диагностики для сбора данных об устранении неполадок.

Монитор производительности

Настройте и соберите следующие счетчики с помощью Монитор производительности:

• Память: доступно МБ
• Процесс: Рабочий набор
• Процесс:Частные байты
• SQL Server — Диспетчер памяти (все счетчики)
• SQL Server — Диспетчер буферов (все счетчики)

DMV и DBCC MEMORYSTATUS

Используйте sys.dm_os_memory_clerks или DBCC MEMORYSTATUS для наблюдения за общим использованием памяти внутри SQL Server. sys.dm_os_memory_clerks возвращает по одной строке для каждого диспетчера памяти и является лучшей отправной точкой для поиска компонентов, потребляющих больше всего памяти. DBCC MEMORYSTATUS возвращает более детализированный снимок с группировкой информации по диспетчеру памяти, буферному пулу и клеркам памяти.

Отчет о потреблении памяти в SSMS

Чтобы просмотреть использование памяти в SQL Server Management Studio (SSMS):

1. Откройте SSMS и подключитесь к серверу.
2. В обозреватель объектов выберите и удерживайте (или щелкните правой кнопкой мыши) имя экземпляра SQL Server.
3. В контекстном меню выберите Отчеты>Стандартные отчеты>Потребление памяти.

PSSDiag или SQL LogScout

Чтобы автоматически записать эти точки данных, используйте средство, например PSSDiag или SQL LogScout.

• Если вы используете PSSDiag, настройте его на сбор данных с помощью сборщика Perfmon и сборщика Custom Diagnostics\SQL Memory Error.
• Если вы используете SQL LogScout, настройте его для записи сценария памяти .

Быстрое облегчение для освобождения памяти

Следующие действия могут освободить память и сделать ее доступной для SQL Server. Используйте их в качестве краткосрочного облегчения при изучении первопричины.

Изменение конфигурации памяти

Проверьте следующие параметры конфигурации памяти SQL Server и попробуйте увеличить max server memory (максимальный объем памяти сервера), если это возможно:

• максимальная память сервера
• минимальная серверная память

Если вы не установили максимальный объем памяти сервера, особенно при включенной блокировке страниц в памяти, задайте для нее определенное значение, чтобы оставить память для ОС. Дополнительные сведения см. в параметре конфигурации сервера «Закрепление страниц в памяти (LPIM)».

Изменение или перемещение рабочей нагрузки

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

Для рабочих нагрузок, доступных только для чтения, рекомендуется переместить их в вторичную реплику только для чтения в среде AlwaysOn. Дополнительные сведения см. в разделах «Перенос рабочих нагрузок, предназначенных только для чтения, на вторичную реплику группы доступности Always On» и «Настройка доступа только для чтения к вторичной реплике группы доступности Always On».

Проверка конфигурации памяти виртуальной машины

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

Выпуск памяти внутри SQL Server

Выполните одну или несколько следующих команд DBCC, чтобы освободить кэши памяти SQL Server. Используйте эти команды с осторожностью в продуктивных системах, так как они очищают кэш, который затем необходимо заполнить заново:

• DBCC FREESYSTEMCACHE
• DBCC FREESESSIONCACHE
• DBCC FREEPROCCACHE

Перезапустите службу SQL Server.

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

Проверка параметров Resource Governor

Если вы используете Resource Governor, проверьте параметры пула ресурсов и группы рабочей нагрузки, чтобы убедиться, что они не ограничивают память слишком резко. Дополнительные сведения см. в разделе Resource Governor (Регулятор ресурсов).

Добавление дополнительных ОЗУ

Если проблема продолжается после предыдущих шагов, изучите дальнейшие действия и рассмотрите возможность увеличения ресурсов сервера (ОЗУ) на физическом или виртуальном сервере.

Диагностика и исправление давления памяти

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

Давление внешней памяти

Чтобы диагностировать низкое состояние памяти в системе за пределами процесса SQL Server, используйте следующие методы:

• Собрать счетчики монитора производительности. Проверьте, потребляют ли память на этом сервере приложения или службы, отличные от SQL Server, просмотрев следующие счетчики:

  • Память: доступно МБ
  • Процесс: Рабочий набор
  • Процесс:Частные байты

  Ниже приведен пример сбора журналов Perfmon с помощью PowerShell:

  clear
  $serverName = $env:COMPUTERNAME
  $Counters = @(
     ("\\$serverName" +"\Memory\Available MBytes"),
     ("\\$serverName" +"\Process(*)\Working Set"),
     ("\\$serverName" +"\Process(*)\Private Bytes")
  )
  
  Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
  $_.CounterSamples | ForEach-Object   {
     [pscustomobject]@{
        TimeStamp = $_.TimeStamp
        Path = $_.Path
        Value = ([Math]::Round($_.CookedValue, 3)) }
  }
  }
  

• Просмотрите журнал событий системы и найдите ошибки, связанные с памятью (например, низкая виртуальная память).

• Просмотрите журнал событий приложения для проблем с памятью, связанными с приложением.

  Ниже приведен пример скрипта PowerShell, который запрашивает журналы событий системы и приложения для ключевого слова "память". Для поиска можно использовать другие строки, такие как "ресурс":

  Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  

• Исправьте все проблемы с кодом или конфигурацией для менее критически важных приложений или служб, чтобы уменьшить их использование памяти.

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

Внутренняя нехватка памяти со стороны модулей, не входящих в состав движка

Чтобы диагностировать внутреннее давление на память, вызванное модулями (DLL) внутри SQL Server, используйте следующие методы:

• Если SQL Server не использует Блокировка страниц в памяти (API AWE), большая часть используемой им памяти отображается по счётчику Process:Private Bytes (экземпляр sqlservr) в системном мониторе производительности. Счетчик SQL Server:Memory Manager: Total Server Memory Memory (KB) отображает общее использование памяти в подсистеме SQL Server. Если вы найдете значительное различие между Process:Private Bytes и SQL Server:Memory Manager: Total Server Memory Memory (KB), это различие, вероятно, происходит из библиотеки DLL (связанный сервер, XP, SQLCLR и т. д.). Например, если Private Bytes составляет 300 ГБ и Total Server Memory составляет 250 ГБ, приблизительно 50 ГБ общей памяти в процессе поступает извне ядра SQL Server.

• Если SQL Server использует функцию Lock Pages in Memory (AWE API), выявить проблему сложнее, поскольку Монитор производительности не предоставляет счётчики AWE, отслеживающие использование памяти отдельными процессами. Счетчик SQL Server:Memory Manager: Total Server Memory (KB) показывает общее использование памяти в ядре SQL Server. Типичные значения Process:Private Bytes могут отличаться от 300 МБ до 1–2 ГБ в целом. Если Процесс:Частные байты значительно превышает этот типичный диапазон, то разница, скорее всего, обусловлена DLL (например, связанным сервером, XP, SQLCLR и т. д.). Например, если Private Bytes составляет 4–5 ГБ, а SQL Server использует страницы блокировки в памяти (AWE), большая часть Private Bytes может поступать из-за пределов ядра SQL Server. Это значение является приблизительным.

• Используйте программу tasklist, чтобы определить библиотеки DLL, загруженные внутри процесса SQL Server.

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"
  

• Вы также можете использовать следующий запрос для проверки загруженных модулей (DLL) и проверки наличия каких-либо непредвиденных значений:

  SELECT * FROM sys.dm_os_loaded_modules
  

• Если вы подозреваете, что модуль связанного сервера вызывает значительное потребление памяти, настройте его на выполнение вне процесса, сняв флажок Allow inprocess. Дополнительные сведения см. в разделе "Создание связанных серверов". Не все поставщики OLE DB для связанных серверов могут выполняться вне процесса. Для получения дополнительных сведений обратитесь к изготовителю поставщика.

• В редких случаях, когда используются объекты OLE Automation (sp_OA*), можно настроить объект для выполнения в процессе вне SQL Server, указав значение контекста 4 (только локальный OLE-сервер (.exe)). Дополнительные сведения см. в разделе sp_OACreate.

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

Чтобы диагностировать внутреннее давление памяти из компонентов внутри подсистемы SQL Server, используйте следующие методы:

• Начните собирать счетчики Монитор производительности для SQL Server: SQL Server:Buffer Manager и SQL Server:Memory Manager.

• Выполните запрос к DMV SQL Server для memory clerks несколько раз, чтобы увидеть, где внутри ядра происходит наибольшее потребление памяти.

  SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
  FROM sys.dm_os_memory_clerks
  ORDER BY pages_kb DESC
  

• Либо обратите внимание на более подробный DBCC MEMORYSTATUS вывод и на то, как он меняется при появлении этих сообщений об ошибках.

  DBCC MEMORYSTATUS
  

• Если вы определите четкого правонарушителя среди клерков памяти, обратите внимание на особенности потребления памяти для этого компонента. Вот несколько примеров:

  • MEMORYCLERK_SQLQERESERVATIONS Если клер памяти потребляет память, определите запросы, использующие большие ресурсы памяти, и оптимизируйте их с помощью индексов, перезаписав их (например, удалениеORDER BY), или применив указания запроса на предоставление памяти (MIN_GRANT_PERCENTиMAX_GRANT_PERCENT). Дополнительные сведения см. в подсказках к запросам. Вы также можете создать пул ресурсов для управления использованием памяти. Дополнительные сведения о выделении памяти см. в разделе Устранение неполадок, связанных с низкой производительностью или нехваткой памяти из-за выделения памяти в SQL Server.
  • Если в кэше хранится много специальных планов запросов, диспетчер памяти CACHESTORE_SQLCP использует большой объём памяти. Определите непараметризованные запросы, планы которых нельзя повторно использовать и параметризовать их, преобразовав их в хранимые процедуры, используя sp_executesqlили используя FORCED параметризацию. Если флаг трассировки 174 включен, его можно отключить, чтобы узнать, устранена ли проблема.
  • Если хранилище CACHESTORE_OBJCP кэша планов объектов потребляет слишком много памяти, определите, какие хранимые процедуры, функции или триггеры используют большие объемы памяти и рассмотрите возможность перепроектирования приложения. Это обычно происходит со многими базами данных или схемами, каждый из которых содержит сотни процедур.
  • Если OBJECTSTORE_LOCK_MANAGER диспетчер памяти показывает большие объемы выделения памяти, определите запросы, которые берут много блокировок, и оптимизируйте их, используя индексы. Сократите длительность транзакций, удерживающих блокировки на длительное время при определенных уровнях изоляции, или проверьте, отключена ли эскалация блокировок.
  • Если вы наблюдаете очень большой TokenAndPermUserStore (SELECT type, name, pages_kb FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'), вы можете использовать флаг трассировки 4618, чтобы ограничить размер кэша.
  • Если вы наблюдаете проблемы с памятью In-Memory OLTP, связанные с диспетчером памяти MEMORYCLERK_XTP, см. статьи Мониторинг и устранение неполадок использования памяти для In-Memory OLTP и Ошибки нехватки памяти в оптимизированных для памяти метаданных tempdb (HkTempDB).

Часто задаваемые вопросы

Почему SQL Server использует почти все ОЗУ на сервере?

По своей архитектуре буферный пул SQL Server увеличивает свой размер, чтобы кэшировать страницы данных и сократить число операций физического ввода-вывода, поэтому в установившемся режиме потребление памяти обычно приближается к значению параметра max server memory. Это поведение ожидается и не является утечкой. Чтобы ограничить потребление памяти и оставить резерв для ОС и других процессов, настройте max server memory. Дополнительные сведения см. в разделе "Параметры конфигурации памяти сервера".

Какова разница между максимальной памятью сервера и зафиксированной памятью, показанной в диспетчере задач?

Параметр max server memory ограничивает объём памяти, который могут выделить пул буферов SQL Server и большинство диспетчеров памяти внутри ядра. Диспетчер задач отображает зафиксированную память для всего sqlservr.exe процесса. Это представление включает выделения, которые компоненты делают за пределами буферного пула, такие как CLR, поставщики связанных серверов, расширенные хранимые процедуры и буферы резервного копирования. В результате общий объем памяти процесса может превышать максимальный объем памяти сервера. Дополнительные сведения см. в руководстве по архитектуре управления памятью.

Когда следует включить блокировку страниц в памяти (LPIM)?

Включите блокировку страниц в памяти, когда ОС сокращает рабочий набор SQL Server. Эта проблема возникает как ошибка 17890 или внезапный спад в общей памяти сервера. Используйте LPIM вместе с явным значением параметра максимальный объем памяти сервера, чтобы оставить ОЗУ операционной системе и другим процессам. Не включайте LPIM по умолчанию на каждом экземпляре. Используйте его для решения подтвержденной проблемы с разбиением страниц.

Что мне сообщает sys.dm_os_memory_clerks?

sys.dm_os_memory_clerks возвращает одну строку для каждого активного диспетчера памяти в ядре SQL Server, а также объем выделенной им памяти. Используйте это, чтобы определить, какой компонент (например, буферный пул, кэш планов, диспетчер блокировок или выделенная запросам память) потребляет больше всего памяти, и направить усилия по настройке.