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

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

Существуют распространенные ошибки, указывающие на низкую память в SQL Server. Примеры ошибок:

• 701. Сбой выделения достаточного объема памяти для выполнения запроса.
• 802. Сбой получения памяти для выделения страниц в буферном пуле (страницы данных или индексов).
• 1204: сбой выделения памяти для блокировок.
• 6322: сбой выделения памяти для средства синтаксического анализа XML.
• 6513:Сбой инициализации СРЕДЫ CLR из-за нехватки памяти.
• 6533: appDomain выгружается из-за нехватки памяти.
• 8318: сбой загрузки счетчиков производительности SQL из-за нехватки памяти.
• 8356 или 8359: трассировка ETW или SQL не выполняется из-за низкой памяти.
• 8556: сбой загрузки MSDTC из-за нехватки памяти.
• 8645: сбой выполнения запроса из-за отсутствия памяти для предоставления памяти (сортировка и хэширование) Дополнительные сведения см. в разделе "Устранение неполадок с ошибкой SQL Server 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 начинает сокращать использование памяти, его пул памяти уменьшается, и все компоненты, необходимые для памяти, могут не получить его. В результате вы начинаете получать 701 или другие ошибки, связанные с памятью. Дополнительные сведения о том, как SQL динамически выделяет и освобождает память, см. в статье "Архитектура памяти 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 не сообщит об использовании большого ее объема, хотя в процессе SQL Server происходит именно это. Этот рост памяти из библиотеки DLL связанного сервера, например, приведет к тому, что SQL Server начнет сокращать использование памяти (см. выше) и создаст условия низкой памяти для компонентов в SQL Server, вызывая ошибки памяти. Дополнительные сведения о диагностика и решениях по этой проблеме см. в статье о нехватке внутренней памяти, а не из SQL Server.

Внутреннее давление на память, поступающее из компонентов в подсистеме SQL Server, также может привести к ошибкам памяти. Существует сотни компонентов, отслеживаемых с помощью клерков памяти, которые выделяют память в SQL Server. Чтобы устранить эту проблему, необходимо определить, какие клерки памяти отвечают за наибольшее выделение памяти. Например, если вы обнаружите, что OBJECTSTORE_LOCK_MANAGER клирк памяти отображает большое выделение памяти, необходимо понять, почему диспетчер блокировки потребляет так много памяти. Вы можете найти запросы, которые получают много блокировок. Эти запросы можно оптимизировать с помощью индексов, сокращения всех транзакций, удерживающих блокировки в течение длительного времени, или проверки отключения эскалации блокировки. Каждый компонент и клерк памяти имеет свой уникальный способ доступа к памяти и ее использования. См. дополнительные сведения о типах клерков памяти и их описания. Дополнительные сведения об диагностика и решениях по этой проблеме см. в статье об использовании внутренней памяти подсистемой SQL Server.

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

Для сбора данных об устранении неполадок можно использовать следующие средства диагностики:

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

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

Вы можете использовать sys.dm_os_memory_clerks или DBCC MEMORYSTATUS для наблюдения за общим объемом использования памяти в SQL Server.

Просмотр использования памяти в SQL Server Management Studio:

1. Запустите SQL Server Management Studio и подключитесь к серверу.
2. В обозреватель объектов щелкните правой кнопкой мыши имя экземпляра SQL Server.
3. В контекстном меню выберите "Стандартные отчеты>>о потреблении памяти".

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

• Если вы используете PSSDiag, настройте его для записи сборщика Perfmon и сборщика пользовательской диагностики\sql Memory Error .

• Если вы используете SQL LogScout, настройте его для записи сценария памяти .

В следующих разделах описаны более подробные шаги для каждого сценария (давление на внешнюю или внутреннюю память).

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

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

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

  • Память:доступные MBytes
  • Процесс — Рабочий набор
  • Процесс — Байт исключительного пользования

  Ниже приведен пример сбора журналов 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, отражается в счетчике SQL Server:Memory Manager: Total Server Memory Memory (KB). Если вы найдете значительное различие между процессом: Private Bytes и SQL Server:Memory Manager: Total Server Memory Memory (KB) (Total Server Memory (KB)), это различие, скорее всего, происходит из библиотеки DLL (связанный сервер, XP, SQLCLR и т. д.). Например, если частные байты составляют 300 ГБ, а общая память сервера составляет 250 ГБ, то около 50 ГБ общей памяти в процессе поступает из-за пределов ядра SQL Server.

• Если SQL Server использует заблокированные страницы в памяти (API AWE), это более сложно определить проблему, так как Монитор производительности не предлагает счетчики AWE, которые отслеживают использование памяти для отдельных процессов. Общее использование памяти в подсистеме SQL Server отражается в счетчике SQL Server:Memory Manager: Total Server Memory Memory (KB). Значения счетчика Процесс — Байт исключительного пользования обычно в совокупности составляют от 300 МБ до 1–2 ГБ. Если вы обнаружите значительное использование Process:Private Bytes за пределами этого типичного использования, разница, скорее всего, происходит из библиотеки DLL (связанный сервер, XP, SQLCLR и т. д.). Например, если счетчик частных байтов составляет 4–5 ГБ, а SQL Server использует заблокированные страницы в памяти (AWE), большая часть частных байтов может поступать из-за пределов ядра 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,sp_OA* можно настроить объект для выполнения в процессе за пределами SQL Server, указав контекстное значение 4 (.exe) только сервера OLE. Дополнительные сведения: sp_OACreate.

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

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

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

  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 , чтобы ограничить размер кэша.
  • Если вы наблюдаете проблемы с памятью в памяти OLTP, поступающие из MEMORYCLERK_XTP клерка памяти, можно обратиться к ошибкам отслеживания и устранения неполадок использования памяти для olTP в памяти и метаданных tempdb, оптимизированных для памяти (HkTempDB).

Следующие действия могут освободить память и сделать ее доступной для SQL Server:

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

• max server memory
• min server memory

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

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

Для рабочих нагрузок, доступных только для чтения, рекомендуется переместить их в вторичную реплику только для чтения в среде AlwaysOn. Дополнительные сведения см. в разделе "Разгрузка рабочей нагрузки только для чтения" во вторичную реплику группы доступности AlwaysOn и настройка доступа только для чтения к вторичной реплике группы доступности AlwaysOn.

Если вы запускаете SQL Server на виртуальной машине, убедитесь в отсутствии на ней избыточного выделения памяти. Сведения о настройке памяти для виртуальных машин см. в статье "Виртуализация — перезагрузка памяти" и ее обнаружение в виртуальной машине и устранение неполадок с производительностью виртуальных машин ESX/ESXi (перезагрузка памяти).

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

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

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

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

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