Устранение неполадок нехватки памяти или нехватки памяти в SQL Server

Статья
01/30/2024

Симптомы

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

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

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

Причина

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

Причина 1: нехватка внешней памяти или памяти ОС

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

Существует три широкие категории проблем, которые могут вызвать нехватку памяти ОС:

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

Подробное описание этих действий и инструкций по устранению неполадок см. в MSSQLSERVER_17890.

Причина 2. Внутренняя нехватка памяти, не поступающая от SQL Server

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

Примечание.

Некоторые библиотеки DLL Майкрософт, используемые в пространстве процессов SQL Server (например, MSOLEDBSQL, собственный клиент SQL), могут интерфейсировать с инфраструктурой SQL Server памяти для создания отчетов и выделения. Вы можете выполнить команду select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' , чтобы получить их список и отследить потребление памяти для некоторых их выделений.

Причина 3. Внутренняя нехватка памяти из-за SQL Server компонентов

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

Визуальное представление типов нехватки памяти

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

Снимок экрана: типы нехватки памяти.

Средства диагностики для сбора данных об устранении неполадок

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

Системный монитор

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

Память:доступные Мбайты
Процесс:рабочий набор
Process:Private Bytes
SQL Server:Диспетчер памяти: (все счетчики)
SQL Server:Диспетчер буферов: (все счетчики)

Динамические административные представления или DBCC MEMORYSTATUS

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

Стандартный отчет о потреблении памяти в SSMS

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

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

PSSDiag или SQL LogScout

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

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

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

Методология устранения неполадок

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

Нехватка внешней памяти

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

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

Память:доступные Мбайты
Процесс:рабочий набор
Process:Private Bytes

Ниже приведен пример сбора журналов 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 для запроса журналов событий системы и приложения для ключевое слово "память". Не стесняйтесь использовать для поиска другие строки, такие как "resource":
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Устранение проблем с кодом или конфигурацией для менее важных приложений или служб, чтобы сократить использование ими памяти.
Если приложения, помимо SQL Server, потребляют ресурсы, попробуйте остановить или перепланировать эти приложения или запустить их на отдельном сервере. Эти действия устраняют нехватку внешней памяти.

Внутренняя нехватка памяти, не исходящая от SQL Server

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

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

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

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

Быстрое облегчение, которое может сделать память доступной

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

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

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

max server memory
min server memory

Примечание.

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

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

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

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

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

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

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

Освобождение памяти внутри SQL Server

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

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Перезапуск службы SQL Server

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

Рассмотрите возможность использования Resource Governor для конкретных сценариев

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

Добавление дополнительных ОЗУ на физическом или виртуальном сервере

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