Поделиться через

MSSQLSERVER_701

  • Статья

Область применения: SQL Server

Сведения

Атрибут Значение
Название продукта SQL Server
ИД события 701
Источник событий MSSQLSERVER
Компонент SQLEngine
Символическое имя NOSYSMEM
Текст сообщения Для выполнения этого запроса недостаточно памяти.

Примечание.

Эта статья посвящена SQL Server. Сведения об устранении проблем нехватки памяти в Базе данных SQL Azure см. в статье Устранение ошибок нехватки памяти в Базе данных SQL Azure.

Описание

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

Использование памяти внешними компонентами или ОС

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

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

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

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

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

Действие пользователя

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

Далее перечислены основные шаги, которые помогут в устранении ошибок памяти.

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

Средства диагностики, позволяющие собирать данные для устранения неполадок, — это Системный монитор, sys.dm_os_memory_clerks и DBCC MEMORYSTATUS.

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

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

Сбор периодических выходных данных этого запроса на затронутом 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

PSSDIAG или SQL LogScout

Существует и другой, автоматический способ записи подобных точек данных — это такие инструменты, как PSSDIAG и SQL LogScout.

  • Для PSSDIAG настройте сборщики данных Perfmon (Системный монитор) и Custom Diagnostics\SQL Memory Error (Настраиваемая диагностика\Ошибка памяти SQL).
  • Для SQL LogScout настройте сбор данных по сценарию Memory (Память).

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

Использование памяти внешними компонентами: диагностика и решения

  • Чтобы продиагностировать нехватку памяти в системе вне процесса 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 для запроса журналов событий системы и приложений для ключевого слова "memory". Вы можете использовать другие строки, такие как "ресурс" для поиска:

    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 — Диспетчер памяти — Общая память сервера (КБ). Существенная разница между значениями счетчиков Процесс — Байт исключительного пользования и SQL Server — Диспетчер памяти — Общая память сервера (КБ) может с большой вероятностью возникать из-за DLL (связанного сервера, XP, SQLCLR и др.). Например, если счетчик Байт исключительного пользования показывает 300 ГБ, а Общая память сервера — 250 ГБ, то примерно 50 ГБ общей памяти процесса занято чем-то вне ядра SQL Server.

  • Если SQL Server использует страницы блокировки в памяти (API AWE), то более сложно определить проблему, так как монитор производительности не предлагает счетчики AWE, которые отслеживают использование памяти для отдельных процессов. Общее использование памяти внутри SQL Server отражено счетчиком SQL Server — Диспетчер памяти — Общая память сервера (КБ). Типичные значения Process:Private Bytes могут отличаться от 300 МБ до 1–2 ГБ в целом. Если счетчик Процесс — Байт исключительного пользования показывает существенно большее значение, то, скорее всего, эта разница возникает из-за DLL (связанного сервера, XP, SQLCLR и др.). Например, если счетчик частных байтов составляет 5–4 ГБ, а SQL Server использует страницы блокировки в памяти (AWE), то большая часть частных байтов может поступать из-за пределов ядра SQL Server. Такая методика является приблизительной.

  • Воспользуйтесь служебной программой tasklist для обнаружения DLL, загруженных в область SQL Server:

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

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

    SELECT * FROM sys.dm_os_loaded_modules

  • Если вы подозреваете, что значительный объем памяти занят модулем связанного сервера, вы можете настроить для него внепроцессное выполнение, отключив параметр Допускать в ходе процесса. Дополнительные сведения см. в статье "Создание связанных серверов" (ЯДРО СУБД SQL Server). Не все поставщики OLEDB связанного сервера не выполняются; Обратитесь к производителю продукта, чтобы получить дополнительные сведения.

  • В редких случаях используются объекты автоматизации 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) или используйте указания запроса.
    • Если кэшируются большое количество нерегламентированных планов запросов, то CACHESTORE_SQLCP клирк памяти будет использовать большие объемы памяти. Определите не параметризованные запросы, планы запросов которых не могут использоваться повторно и параметризовать их путем преобразования в хранимые процедуры или использования или с помощью sp_executesqlпараметризации FORCED.
    • Если хранилище кэша планов объектов CACHESTORE_OBJCP потребляет много памяти, определите, какие хранимые процедуры, функции или триггеры используют большой объем памяти и, если возможно, спроектируйте приложение иным образом. Обычно это может произойти из-за больших объемов базы данных или схем с сотнями процедур в каждом из них.
    • Если клерк памяти OBJECTSTORE_LOCK_MANAGER показывает большие объемы ее выделения, определите, какие запросы применяют множество блокировок, и оптимизируйте эти запросы с помощью индексов. Сократите транзакции, которые создают длительные блокировки на определенных уровнях изоляции, а также проверьте, не включено ли укрупнение блокировки.

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

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

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

    • max server memory

    • min server memory

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

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

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

  • Если вы запускаете SQL Server на виртуальной машине, убедитесь в отсутствии на ней избыточного выделения памяти. Некоторые принципы настройки памяти для виртуальных машин см. в статьях Virtualization – Overcommitting memory and how to detect it within the VM (Виртуализация — избыточное выделение памяти и обнаружение его на виртуальной машине) и Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment) (Устранение неполадок с производительностью виртуальных машин ESX/ESXi (избыточное выделение памяти)).

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

    • DBCC FREESYSTEMCACHE

    • DBCC FREESESSIONCACHE

    • DBCC FREEPROCCACHE

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

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