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

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

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

  • Если SQL Server использует блокировку страниц в памяти (API AWE), определить проблему будет сложнее, так как в Системном мониторе нет счетчиков для AWE, отслеживающих потребление памяти в отдельных процессах. Общее использование памяти внутри SQL Server отражено счетчиком SQL Server — Диспетчер памяти — Общая память сервера (КБ). Значения счетчика Процесс — Байт исключительного пользования обычно в совокупности составляют от 300 МБ до 1–2 ГБ. Если счетчик Процесс — Байт исключительного пользования показывает существенно большее значение, то, скорее всего, эта разница возникает из-за 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 Automation (sp_OA*), вы можете настроить запуск объекта в процессе вне SQL Server, установив context = 4 (только для локального сервера OLE, т. е. EXE-файла). Дополнительные сведения: 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 или с помощью параметризации ПРИНУДИТЕЛЬНО.
    • Если хранилище кэша планов объектов CACHESTORE_OBJCP потребляет много памяти, определите, какие хранимые процедуры, функции или триггеры используют большой объем памяти и, если возможно, спроектируйте приложение иным образом. Такое обычно происходит при больших объемах баз данных или схем с сотнями процедур в каждой.
    • Если клерк памяти OBJECTSTORE_LOCK_MANAGER показывает большие объемы ее выделения, определите, какие запросы применяют множество блокировок, и оптимизируйте эти запросы с помощью индексов. Сократите транзакции, которые создают длительные блокировки на определенных уровнях изоляции, а также проверьте, не включено ли укрупнение блокировки.

Простые способы быстро освободить память

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

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

    • max server memory

    • min server memory

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

  • Если вы не настроили max 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, рекомендуем проверить параметры пула ресурсов или группы рабочей нагрузки и убедиться, что они не ограничивают память слишком сильно.

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