Microsoft sql server ошибка 701 - Исправление ошибок и поиск оптимальных решений проблем

description	title	ms.custom	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords	author	ms.author	ms.reviewer
MSSQLSERVER_701	MSSQLSERVER_701		03/16/2021	sql	supportability	reference	701 (Database Engine error)	MashaMSFT	mathoma	wiassaf

MSSQLSERVER_701

[!INCLUDE SQL Server]

Details

Attribute	Value
Product Name	SQL Server
Event ID	701
Event Source	MSSQLSERVER
Component	SQLEngine
Symbolic Name	NOSYSMEM
Message Text	There’s insufficient system memory to run this query.

[!NOTE]
This article is focused on SQL Server. For information on troubleshooting out of memory issues in Azure SQL Database, see Troubleshoot out of memory errors with Azure SQL Database.

Explanation

Error 701 occurs when [!INCLUDEssNoVersion] has failed to allocate sufficient memory to run a query. Insufficient memory can be caused by a number of factors that include operating system settings, physical memory availability, other components use memory inside SQL Server, or memory limits on the current workload. In most cases, the transaction that failed isn’t the cause of this error. Overall, the causes can be grouped into three:

External or OS memory pressure

External pressure refers to high memory utilization coming from a component outside of the process that leads to insufficient memory for SQL Server. You have to find if other applications on the system are consuming memory and contribute to low memory availability. SQL Server is one of the few applications designed to respond to OS memory pressure by cutting back its memory use. This means, if some application or driver asks for memory, the OS sends a signal to all applications to free up memory and SQL Server will respond by reducing its own memory usage. Very few other applications respond because they aren’t designed to listen for that notification. So if SQL starts cutting back its memory usage, its memory pool is reduced and whichever components need memory may not get it. You start getting 701 and other memory-related errors. For more information, see SQL Server Memory Architecture

Internal memory pressure, not coming from SQL Server

Internal memory pressure refers to low memory availability caused by factors inside the SQL Server process. There are components that may run inside the SQL Server process that are «external» to the SQL Server engine. Examples include DLLs like linked servers, SQLCLR components, extended procedures (XPs), and OLE Automation (sp_OA*). Others include anti-virus or other security programs that inject DLLs inside a process for monitoring purposes. An issue or poor design in any of these components could lead to large memory consumption. For example, consider a linked server caching 20 million rows of data that come from an external source into SQL Server memory. As far as SQL Server is concerned, no memory clerk will report high memory usage, but memory consumed inside the SQL Server process will be high. This memory growth from a linked server DLL, for example, would cause SQL Server to start cutting its memory usage (see above) and will create low-memory conditions of for components inside SQL Server, causing errors like 701.

Internal memory pressure, coming from SQL Server component(s)

Internal memory pressure coming from components inside SQL Server Engine can also lead to error 701. There are hundreds of components, tracked via memory clerks, that allocate memory in SQL Server. You must identify which memory clerk(s) are responsible for the largest memory allocations to be able to resolve this further. For example, if you find that the OBJECTSTORE_LOCK_MANAGER memory clerk is showing the large memory allocation, you need to further understand why the Lock Manager is consuming so much memory. You may find there are queries that acquire a large number of locks and optimize them by using indexes, or shorten transactions that hold locks for long periods, or check if lock escalation is disabled. Each memory clerk or component has a unique way of accessing and using memory. For more information, see memory clerk types and their descriptions.

User action

If error 701 appears occasionally or for a brief period, there may be a short-lived memory issue that resolved itself. You may not need to take action in those cases. However, if the error occurs multiple times, on multiple connections and persists for periods of seconds or longer, follow the steps to troubleshoot further.

The following list outlines general steps that will help in troubleshooting memory errors.

Diagnostic tools and capture

The diagnostics tools that will allow you to collect troubleshooting data are Performance Monitor, sys.dm_os_memory_clerks, and DBCC MEMORYSTATUS.

Configure and collect the following counters with Performance Monitor:

Memory:Available MB
Process:Working Set
Process:Private Bytes
SQL Server:Memory Manager: (all counters)
SQL Server:Buffer Manager: (all counters)

Collect periodic outputs of this query on the impacted 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 or SQL LogScout

An alternative, automated way to capture these data points is to use tools like PSSDIAG or SQL LogScout.

If you use Pssdiag, configure to capture Perfmon collector and the Custom DiagnosticsSQL Memory Error collector
If you use SQL LogScout, configure to capture the Memory scenario

The following sections describe more detailed steps for each scenario — external or internal memory pressure.

External pressure: diagnostics and solutions

To diagnose low memory conditions on the system outside of SQL Server process, collect Performance monitor counters. Investigate if applications or services other than SQL Server are consuming memory on this server by looking at these counters:
- Memory:Available MB
- Process:Working Set
- Process:Private Bytes
Here is a sample Perfmon log collection using PowerShell
```
clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\$serverName" +"MemoryAvailable 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)) }
 }
}
```
Review the System Event log and look for memory related errors (for example, low virtual memory).
Review the Application Event log for application-related memory issues.

Here is a sample PowerShell script to query the System and Applicaiton Event logs for the keyword «memory». Feel free to use other strings like «resource» for your search:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Address any code or configuration issues for less critical applications or services to reduce their memory usage.
If applications besides [!INCLUDEssNoVersion] are consuming resources, try stopping or rescheduling these applications, or consider running them on a separate server. These steps will remove external memory pressure.

Internal memory pressure, not coming from SQL Server: diagnostics and solutions

To diagnose internal memory pressure caused by modules (DLLs) inside SQL Server, use the following approach:

If SQL Server isn’t* using Locked Pages in Memory (AWE API), then most its memory is reflected in the Process:Private Bytes counter (SQLServr instance) in Performance Monitor. The overall memory usage coming from within SQL Server engine is reflected in SQL Server:Memory Manager: Total Server Memory (KB) counter. If you find a significant difference between the value Process:Private Bytes and SQL Server:Memory Manager: Total Server Memory (KB), then that difference is likely coming from a DLL (linked server, XP, SQLCLR, etc.). For example if Private bytes is 300 GB and Total Server Memory is 250 GB, then approximately 50 GB of the overall memory in the process is coming from outside SQL Server engine.
If SQL Server is using Locked Pages in Memory (AWE API), then it is more challenging to identify the issue because Performance monitor doesn’t offer AWE counters that track memory usage for individual processes. The overall memory usage coming from within SQL Server engine is reflected in SQL Server:Memory Manager: Total Server Memory (KB) counter. Typical Process:Private Bytes values may vary between 300 MB and 1-2 GB overall. If you find a significant usage of Process:Private Bytes beyond this typical use, then the difference is likely coming from a DLL (linked server, XP, SQLCLR, etc.). For example, if Private bytes counter is 5-4 GB and SQL Server is using Locked Pages in Memory (AWE), then a large part of the Private bytes may be coming from outside SQL Server engine. This is an approximation technique.
Use the Tasklist utility to identify any DLLs that are loaded inside SQL Server space:
```
 tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
You could also use this query to examine loaded modules (DLLs) and see if something isn’t expected to be there
```
SELECT * FROM sys.dm_os_loaded_modules
```
If you suspect that a Linked Server module is causing significant memory consumption, then you can configure it to run out of process by disabling Allow inprocess option. See Create Linked Servers for more information. Not all linked server OLEDB providers may run out of process; contact the product manufacturer for more information.
In the rare case that OLE automation objects are used (sp_OA*), you may configure the object to run in a process outside SQL Server by setting context = 4 (Local (.exe) OLE server only.). For more information, see sp_OACreate.

Internal memory usage by SQL Server engine: diagnostics and solutions

Start collecting performance monitor counters for [!INCLUDEssNoVersion]:SQL Server:Buffer Manager, SQL Server: Memory Manager.

Query the SQL Server memory clerks DMV multiple times to see where the highest consumption of memory occurs inside the engine:

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

Alternatively, you can observe the more detailed DBCC MEMORYSTATUS output and the way it changes when you see these error messages.
If you identify a clear offender among the memory clerks, focus on addressing the specifics of memory consumption for that component. Here are several examples:
- If MEMORYCLERK_SQLQERESERVATIONS memory clerk is consuming memory, identify queries that are using huge memory grants and optimize them via indexes, rewrite them (remove ORDER by for example), or apply query hints.
- If a large number of ad-hoc query plans are cached, then the CACHESTORE_SQLCP memory clerk would use large amounts of memory. Identify non-parameterized queries whose query plans can’t be reused and parameterize them by either converting to stored procedures, or by using sp_executesql, or by using FORCED parameterization.
- If object plan cache store CACHESTORE_OBJCP is consuming much memory, then do the following: identify which stored procedures, functions, or triggers are using lots of memory and possibly redesign the application. Commonly this may happen due to large amounts of database or schemas with hundreds of procedures in each.
- If the OBJECTSTORE_LOCK_MANAGER memory clerk is showing the large memory allocations, identify queries that apply many locks and optimize them by using indexes. Shorten transactions that cause locks not to be released for long periods in certain isolation levels, or check if lock escalation is disabled.

Quick relief that may make memory available

The following actions may free some memory and make it available to [!INCLUDEssNoVersion]:

Check the following SQL Server memory configuration parameters and consider increasing max server memory if possible:
- max server memory
- min server memory
  
  Notice unusual settings. Correct them as necessary. Account for increased memory requirements. Default settings are listed in Server memory configuration options.
If you haven’t configured max server memory especially with Locked Pages in Memory, consider setting to a particular value to allow some memory for the OS. See Locked Pages in Memory server configuration option.
Check the query workload: number of concurrent sessions, currently executing queries and see if there are less critical applications that may be stopped temporarily or moved to another SQL Server.
If you’re running SQL Server on a virtual machine (VM), ensure the memory for the VM isn’t overcommitted. For ideas on how to configure memory for VMs, see this blog Virtualization – Overcommitting memory and how to detect it within the VM and Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment)
You can run the following DBCC commands to free several [!INCLUDEssNoVersion] memory caches.
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
If you’re using Resource Governor, we recommend that you check the resource pool or workload group settings and see if they aren’t limiting memory too drastically.
If the problem continues, you’ll need to investigate further and possibly increase server resources (RAM).

Источник

Содержание

KB982854 — FIX: Error «There is insufficient system memory in resource pool ‘internal’ to run this query» in Microsoft SQL Server
Symptoms
Cause
Resolution
Cumulative update information
SQL Server 2008 Service Pack 1
SQL Server 2008 Service Pack 2
SQL Server 2008 R2
Status
References
SQL Server сообщает 701 «Недостаточно памяти для выполнения этого запроса» при выполнении больших пакетов
Симптомы
Причина
Решение
Дополнительная информация
MSSQLSERVER_701
Сведения
Пояснение
Использование памяти внешними компонентами или ОС
Внутреннее использование памяти независимо от SQL Server
Внутреннее использование памяти компонентами SQL Server
Рекомендуемые действия
Средства диагностики и сбор данных
PSSDIAG или SQL LogScout
Использование памяти внешними компонентами: диагностика и решения
Внутреннее использование памяти независимо от SQL Server: диагностика и решения
Внутреннее использование памяти ядром SQL Server: диагностика и решения
Простые способы быстро освободить память

KB982854 — FIX: Error «There is insufficient system memory in resource pool ‘internal’ to run this query» in Microsoft SQL Server

Microsoft distributes Microsoft SQL Server 2008 and Microsoft SQL Server 2008 R2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 or SQL Server 2008 fix release.

Symptoms

When you run a Full-Text query that uses compound words in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2, you receive the following error message in the Error log file:

Date Time spid ID Error: 701, Severity: 17, State: 123.
Date Time spid ID There is insufficient system memory in resource pool ‘internal’ to run this query.

Additionally, if you run a Full-Text query successfully after this problem occurs, you receive the same error message.

Cause

This problem occurs for all the following reasons:

There is a bug in the code that is used to evaluate how many rowsets are required to perform a sorting operation. Therefore, the code greatly overestimates the numbers of rowsets. The required grant estimate in full-text is 64 pages for each rowset that is required to sort.

There is a large request that blocks the system from using the memory that is reserved for the full-text query.

Resolution

Cumulative update information

SQL Server 2008 Service Pack 1

The fix for this issue was first released in Cumulative Update 9 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2083921 cumulative update 9 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008 Service Pack 2

The fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2289254 Cumulative update 1 for SQL Server 2008 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2402659 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released

SQL Server 2008 R2

The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:

2345451 Cumulative Update package 4 for SQL Server 2008 R2 Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

References

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

935897An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:

822499New naming schema for Microsoft SQL Server software update packagesFor more information about Full-Text Search, visit the following Microsoft Developer Network (MSDN) website:

Full-Text Search (SQL Server)For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

Источник

SQL Server сообщает 701 «Недостаточно памяти для выполнения этого запроса» при выполнении больших пакетов

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

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 2001221

Симптомы

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

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

Обратите внимание на большие выделения для кэша USERSTORE_SXC .

Кроме того, при запросе sys.dm_os_memory_clerks динамического административного представления (DMV) во время выполнения single_pages_kb пакета в столбце USERSTORE_SXC кэша отображается непрерывный рост в течение периода, который приводит к ошибке 701.

Пример приложения, которое может потенциально проявлять такое поведение, см. в разделе Дополнительные сведения.

Причина

Объем памяти, выделенной для хранения запроса в SQL Server, зависит от:

Размер пакета (количество RPC на запрос).

Для некоторых типов параметров (например, sql_variant) SQL Server могут сохранять запросы в памяти потенциально неэффективным способом. Когда клиент отправляет большой пакет запросов, использующих эти типы параметров, в одном запросе может быть отправлено несколько RPC. В этом сценарии сервер накапливает весь запрос в памяти перед его выполнением. Это может привести к ошибке 701, описанной в разделе Симптомы.

Эта проблема гораздо чаще встречается в более ранних версиях SQL Server (особенно при использовании sql_variant типа данных). SQL Server 2008 и более поздних версиях имеют некоторые усовершенствования, которые сокращают объем используемой памяти в определенных случаях и более эффективны в целом.

Решение

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

Уменьшение размеров пакетов.
Изменение типов параметров. Например, замените sql_variant другими типами.

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

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

Дополнительная информация

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

Источник

MSSQLSERVER_701

Область применения: SQL Server (все поддерживаемые версии)

Сведения

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

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

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

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

max server memory

min server memory

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

Если вы не настроили max server memory, особенно при использовании блокировки страниц в памяти, попробуйте задать конкретное значение, которое оставит некоторый объем памяти для ОС. См. параметр конфигурации сервера Блокировка страниц в памяти.

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

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

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

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

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

Источник

Remove From My Forums

Question
Hi experts,

We got several SQL Errors: 701 today and I found one document mentioning this (

http://blogs.msdn.com/b/poojakamath/archive/2014/12/24/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query-error-701-severity-17-state-123-quot-on-ssb-target-server-cachestore-brokerto-is-consuming-memory.aspx )

but it seems my Server Broker is ok. Anyway, how to analyze the result of DBCC MEMORYSTATUS and identify which processes/components consume too much memory?

The SQL server version is Microsoft SQL Server 2012 (SP2-CU7) (KB3072100) — 11.0.5623.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

and runnning on HP DL980, 64 cores, 1TB DRAM.

— errorlog —

2016-02-16 04:26:39.26 spid2121    Error: 14421, Severity: 16, State: 1.
2016-02-16 04:26:39.26 spid2121    The log shipping secondary database DL980-3.TCP has restore threshold of 45 minutes and is out of sync. No restore was performed for 408579 minutes. Restored latency is 17 minutes. Check agent log and logshipping
monitor information.
2016-02-16 04:27:59.42 spid2284     Failed allocate pages: FAIL_PAGE_ALLOCATION 207
2016-02-16 04:28:00.10 spid2284
Process/System Counts                         Value
—————————————- ———-
Available Physical Memory                23512313856
Available Virtual Memory                 74109546496
Available Paging File                    337114435584
Working Set                              44043255808
Percent of Committed Memory in WS               100
Page Faults                                26218401
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        0
2016-02-16 04:28:00.10 spid2284
Memory Manager                                   KB
—————————————- ———-
VM Reserved                              8515836296
VM Committed                               32772696
Locked Pages Allocated                    950265632
Large Pages Allocated                       6604800
Emergency Memory                               1024
Emergency Memory In Use                        1024
Target Committed                          983040024
Current Committed                         983038328
Pages Allocated                           883841392
Pages Reserved                                14696
Pages Free                                  4449992
Pages In Use                              142901680
Page Alloc Potential                      790986320
NUMA Growth Phase                                 2
Last OOM Factor                                   3
Last OS Error                                     0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 0                               KB
—————————————- ———-
VM Reserved                              8515446968
VM Committed                               32537016
Locked Pages Allocated                     90342984
Pages Allocated                            66683032
Pages Free                                   131632
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            359536
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 1                               KB
—————————————- ———-
VM Reserved                                   35840
VM Committed                                  35860
Locked Pages Allocated                    122844136
Pages Allocated                           116877064
Pages Free                                   618016
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1600924
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 2                               KB
—————————————- ———-
VM Reserved                                  189456
VM Committed                                  35860
Locked Pages Allocated                    122842716
Pages Allocated                           116615568
Pages Free                                   757064
Target Committed                          122880000
Current Committed                         122878576
Foreign Committed                            715576
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 3                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           117247616
Pages Free                                   578216
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1373532
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 4                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           117208608
Pages Free                                   556024
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            745884
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 5                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           116304184
Pages Free                                  1117072
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           5846644
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 6                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122846956
Pages Allocated                           116393200
Pages Free                                   304096
Target Committed                          122880000
Current Committed                         122879744
Foreign Committed                           4729832
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 7                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           116511992
Pages Free                                   387880
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1912704
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 64                              KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                     20
Locked Pages Allocated                            0
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 0)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              139792
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 1)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  24
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 3)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  16
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 4)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 6)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 7)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 144
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (Total)                   KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              140408
2016-02-16 04:28:00.10 spid2284

2016-02-16 04:28:17.39 spid2284    Error: 701, Severity: 17, State: 123.
2016-02-16 04:28:17.39 spid2284    There is insufficient system memory in resource pool ‘default’ to run this query. Please check complete DBCC MEMORY STATUS below.
- Edited by
  
  Tuesday, February 16, 2016 6:41 AM

Answers

Hi Shanky,

I google and find this bug. (
https://support.microsoft.com/en-gb/kb/3074434 )

I prepare to install SP3. Any thoughts?

—

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

<cite class=»_Rm» style=»font-style:normal;color:#006621;font-size:14px;»>https://twitter.com/sqlcrossjoin/status/623525334441312256</cite>

Jul 21, 2015 — @sqlcrossjoin … @sqlcrossjoin …. New Trace
Flag: 8075 fixes VAS exhaustion (on x64!) problem after applying SQL 2012
SP2 CU7.

—

If you see continuous growth in virtual address space even after you apply the fix, you can determine which queries or operations are requesting large chunks of memory by using the Page_allocated extended
event. A sample script looks like this:
```
CREATE EVENT SESSION [memory_tracking] ON SERVER
ADD EVENT sqlos.page_allocated(
    ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_address,sqlos.worker_address,sqlserver.database_id,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([number_pages]>(1)))
ADD TARGET package0.event_file(SET filename=N'E:DataMSSQL11.MSSQLSERVERMSSQLLogmemory_tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
```
- Edited by
  dennislee124
  Tuesday, February 23, 2016 7:32 AM
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM
Dennis,

Creating event trace to find out queries taking lot of VAS is good idea but I am not sure about enabling trace flag, If I were you I would apply SP3 to be on safer side.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Wiki Articles

MVP
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM

Источник

Remove From My Forums

Question
Hi experts,

We got several SQL Errors: 701 today and I found one document mentioning this (

http://blogs.msdn.com/b/poojakamath/archive/2014/12/24/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query-error-701-severity-17-state-123-quot-on-ssb-target-server-cachestore-brokerto-is-consuming-memory.aspx )

but it seems my Server Broker is ok. Anyway, how to analyze the result of DBCC MEMORYSTATUS and identify which processes/components consume too much memory?

The SQL server version is Microsoft SQL Server 2012 (SP2-CU7) (KB3072100) — 11.0.5623.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

and runnning on HP DL980, 64 cores, 1TB DRAM.

— errorlog —

2016-02-16 04:26:39.26 spid2121    Error: 14421, Severity: 16, State: 1.
2016-02-16 04:26:39.26 spid2121    The log shipping secondary database DL980-3.TCP has restore threshold of 45 minutes and is out of sync. No restore was performed for 408579 minutes. Restored latency is 17 minutes. Check agent log and logshipping
monitor information.
2016-02-16 04:27:59.42 spid2284     Failed allocate pages: FAIL_PAGE_ALLOCATION 207
2016-02-16 04:28:00.10 spid2284
Process/System Counts                         Value
—————————————- ———-
Available Physical Memory                23512313856
Available Virtual Memory                 74109546496
Available Paging File                    337114435584
Working Set                              44043255808
Percent of Committed Memory in WS               100
Page Faults                                26218401
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        0
2016-02-16 04:28:00.10 spid2284
Memory Manager                                   KB
—————————————- ———-
VM Reserved                              8515836296
VM Committed                               32772696
Locked Pages Allocated                    950265632
Large Pages Allocated                       6604800
Emergency Memory                               1024
Emergency Memory In Use                        1024
Target Committed                          983040024
Current Committed                         983038328
Pages Allocated                           883841392
Pages Reserved                                14696
Pages Free                                  4449992
Pages In Use                              142901680
Page Alloc Potential                      790986320
NUMA Growth Phase                                 2
Last OOM Factor                                   3
Last OS Error                                     0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 0                               KB
—————————————- ———-
VM Reserved                              8515446968
VM Committed                               32537016
Locked Pages Allocated                     90342984
Pages Allocated                            66683032
Pages Free                                   131632
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            359536
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 1                               KB
—————————————- ———-
VM Reserved                                   35840
VM Committed                                  35860
Locked Pages Allocated                    122844136
Pages Allocated                           116877064
Pages Free                                   618016
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1600924
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 2                               KB
—————————————- ———-
VM Reserved                                  189456
VM Committed                                  35860
Locked Pages Allocated                    122842716
Pages Allocated                           116615568
Pages Free                                   757064
Target Committed                          122880000
Current Committed                         122878576
Foreign Committed                            715576
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 3                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           117247616
Pages Free                                   578216
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1373532
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 4                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           117208608
Pages Free                                   556024
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            745884
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 5                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           116304184
Pages Free                                  1117072
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           5846644
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 6                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122846956
Pages Allocated                           116393200
Pages Free                                   304096
Target Committed                          122880000
Current Committed                         122879744
Foreign Committed                           4729832
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 7                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           116511992
Pages Free                                   387880
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1912704
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 64                              KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                     20
Locked Pages Allocated                            0
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 0)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              139792
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 1)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  24
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 3)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  16
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 4)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 6)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 7)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 144
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (Total)                   KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              140408
2016-02-16 04:28:00.10 spid2284

2016-02-16 04:28:17.39 spid2284    Error: 701, Severity: 17, State: 123.
2016-02-16 04:28:17.39 spid2284    There is insufficient system memory in resource pool ‘default’ to run this query. Please check complete DBCC MEMORY STATUS below.
- Edited by
  
  Tuesday, February 16, 2016 6:41 AM

Answers

Hi Shanky,

I google and find this bug. (
https://support.microsoft.com/en-gb/kb/3074434 )

I prepare to install SP3. Any thoughts?

—

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

<cite class=»_Rm» style=»font-style:normal;color:#006621;font-size:14px;»>https://twitter.com/sqlcrossjoin/status/623525334441312256</cite>

Jul 21, 2015 — @sqlcrossjoin … @sqlcrossjoin …. New Trace
Flag: 8075 fixes VAS exhaustion (on x64!) problem after applying SQL 2012
SP2 CU7.

—

If you see continuous growth in virtual address space even after you apply the fix, you can determine which queries or operations are requesting large chunks of memory by using the Page_allocated extended
event. A sample script looks like this:
```
CREATE EVENT SESSION [memory_tracking] ON SERVER
ADD EVENT sqlos.page_allocated(
    ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_address,sqlos.worker_address,sqlserver.database_id,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([number_pages]>(1)))
ADD TARGET package0.event_file(SET filename=N'E:DataMSSQL11.MSSQLSERVERMSSQLLogmemory_tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
```
- Edited by
  dennislee124
  Tuesday, February 23, 2016 7:32 AM
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM
Dennis,

Creating event trace to find out queries taking lot of VAS is good idea but I am not sure about enabling trace flag, If I were you I would apply SP3 to be on safer side.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Wiki Articles

MVP
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM

Источник

Remove From My Forums

Question
Hi experts,

We got several SQL Errors: 701 today and I found one document mentioning this (

http://blogs.msdn.com/b/poojakamath/archive/2014/12/24/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query-error-701-severity-17-state-123-quot-on-ssb-target-server-cachestore-brokerto-is-consuming-memory.aspx )

but it seems my Server Broker is ok. Anyway, how to analyze the result of DBCC MEMORYSTATUS and identify which processes/components consume too much memory?

The SQL server version is Microsoft SQL Server 2012 (SP2-CU7) (KB3072100) — 11.0.5623.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

and runnning on HP DL980, 64 cores, 1TB DRAM.

— errorlog —

2016-02-16 04:26:39.26 spid2121    Error: 14421, Severity: 16, State: 1.
2016-02-16 04:26:39.26 spid2121    The log shipping secondary database DL980-3.TCP has restore threshold of 45 minutes and is out of sync. No restore was performed for 408579 minutes. Restored latency is 17 minutes. Check agent log and logshipping
monitor information.
2016-02-16 04:27:59.42 spid2284     Failed allocate pages: FAIL_PAGE_ALLOCATION 207
2016-02-16 04:28:00.10 spid2284
Process/System Counts                         Value
—————————————- ———-
Available Physical Memory                23512313856
Available Virtual Memory                 74109546496
Available Paging File                    337114435584
Working Set                              44043255808
Percent of Committed Memory in WS               100
Page Faults                                26218401
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        0
2016-02-16 04:28:00.10 spid2284
Memory Manager                                   KB
—————————————- ———-
VM Reserved                              8515836296
VM Committed                               32772696
Locked Pages Allocated                    950265632
Large Pages Allocated                       6604800
Emergency Memory                               1024
Emergency Memory In Use                        1024
Target Committed                          983040024
Current Committed                         983038328
Pages Allocated                           883841392
Pages Reserved                                14696
Pages Free                                  4449992
Pages In Use                              142901680
Page Alloc Potential                      790986320
NUMA Growth Phase                                 2
Last OOM Factor                                   3
Last OS Error                                     0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 0                               KB
—————————————- ———-
VM Reserved                              8515446968
VM Committed                               32537016
Locked Pages Allocated                     90342984
Pages Allocated                            66683032
Pages Free                                   131632
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            359536
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 1                               KB
—————————————- ———-
VM Reserved                                   35840
VM Committed                                  35860
Locked Pages Allocated                    122844136
Pages Allocated                           116877064
Pages Free                                   618016
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1600924
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 2                               KB
—————————————- ———-
VM Reserved                                  189456
VM Committed                                  35860
Locked Pages Allocated                    122842716
Pages Allocated                           116615568
Pages Free                                   757064
Target Committed                          122880000
Current Committed                         122878576
Foreign Committed                            715576
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 3                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           117247616
Pages Free                                   578216
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1373532
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 4                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           117208608
Pages Free                                   556024
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                            745884
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 5                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847212
Pages Allocated                           116304184
Pages Free                                  1117072
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           5846644
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 6                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122846956
Pages Allocated                           116393200
Pages Free                                   304096
Target Committed                          122880000
Current Committed                         122879744
Foreign Committed                           4729832
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 7                               KB
—————————————- ———-
VM Reserved                                   32768
VM Committed                                  32788
Locked Pages Allocated                    122847208
Pages Allocated                           116511992
Pages Free                                   387880
Target Committed                          122880000
Current Committed                         122880000
Foreign Committed                           1912704
Away Committed                                    0
Taken Away Committed                              0
2016-02-16 04:28:00.10 spid2284
Memory node Id = 64                              KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                     20
Locked Pages Allocated                            0
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 0)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              139792
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 1)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  24
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 3)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                  16
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 4)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 6)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 216
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (node 7)                  KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                                 144
2016-02-16 04:28:00.10 spid2284
MEMORYCLERK_SQLGENERAL (Total)                   KB
—————————————- ———-
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                              140408
2016-02-16 04:28:00.10 spid2284

2016-02-16 04:28:17.39 spid2284    Error: 701, Severity: 17, State: 123.
2016-02-16 04:28:17.39 spid2284    There is insufficient system memory in resource pool ‘default’ to run this query. Please check complete DBCC MEMORY STATUS below.
- Edited by
  
  Tuesday, February 16, 2016 6:41 AM

Answers

Hi Shanky,

I google and find this bug. (
https://support.microsoft.com/en-gb/kb/3074434 )

I prepare to install SP3. Any thoughts?

—

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

<cite class=»_Rm» style=»font-style:normal;color:#006621;font-size:14px;»>https://twitter.com/sqlcrossjoin/status/623525334441312256</cite>

Jul 21, 2015 — @sqlcrossjoin … @sqlcrossjoin …. New Trace
Flag: 8075 fixes VAS exhaustion (on x64!) problem after applying SQL 2012
SP2 CU7.

—

If you see continuous growth in virtual address space even after you apply the fix, you can determine which queries or operations are requesting large chunks of memory by using the Page_allocated extended
event. A sample script looks like this:
```
CREATE EVENT SESSION [memory_tracking] ON SERVER
ADD EVENT sqlos.page_allocated(
    ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_address,sqlos.worker_address,sqlserver.database_id,sqlserver.query_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([number_pages]>(1)))
ADD TARGET package0.event_file(SET filename=N'E:DataMSSQL11.MSSQLSERVERMSSQLLogmemory_tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
```
- Edited by
  dennislee124
  Tuesday, February 23, 2016 7:32 AM
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM
Dennis,

Creating event trace to find out queries taking lot of VAS is good idea but I am not sure about enabling trace flag, If I were you I would apply SP3 to be on safer side.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Wiki Articles

MVP
- Marked as answer by
  dennislee124
  Wednesday, March 2, 2016 7:18 AM

Источник

Remove From My Forums

Question
Hi All

We are facing an insufficient memory issue in SQL server with error code as 701. As we knew this due to multiple things since it is a generic error message.
But we are not sure how do we diagnose to find out the root cause of this issue.

Error details:
Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool ‘default’ to run this query.

Here are the details:

Environment: SQL 2014 12.0.2000.8

Server has 32GB of memory and 12GB allocated to SQL (Max server memory)

After some routine checks — I found a very low process physical memory

I checked the memory clerks to check the highest consumers of memory

MEMORYCLERK_SQLCONNECTIONPOOL is the clerk using all most of the memory — (Pages Allocated — 15614088)

Can someone please advise as to how I can diagnose this?

Thanks

Regards

Process/System Counts Value
—————————————- ———-
Available Physical Memory 1189363712
Available Virtual Memory 140706004733952
Available Paging File 3549548544
Working Set 13798641664
Percent of Committed Memory in WS 82
Page Faults 2031868464
System physical memory high 0
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2017-08-27 06:55:10.80 spid15s
Memory Manager KB
—————————————- ———-
VM Reserved 30348688
VM Committed 16381128
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 40
Target Committed 16776752
Current Committed 16381128
Pages Allocated 16084920
Pages Reserved 15648
Pages Free 132288
Pages In Use 15943696
Page Alloc Potential -5776
NUMA Growth Phase 0
Last OOM Factor 6
Last OS Error 0

MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
—————————————- ———-
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 15614088
- Edited by
  
  Thursday, January 25, 2018 4:21 PM

Answers

Hi ramsubbu,

>> There is insufficient system memory in resource pool ‘default’ to run this query.

What’s your OS version? What is the output of SELECT @@VERSION? Is there any process running when you get this error? You may share more information with us from the error log for our further analysis.

>> MEMORYCLERK_SQLCONNECTIONPOOL is the clerk using all most of the memory — (Pages Allocated — 15614088)

Memory for connection pool is to maintain user session details of front end applications. You want to check with your app developer whether the connections are properly closed after usage. And you can take this similar

thread as reference.

Besides, it seems you’re running SQL Server 2014 RTM, install the latest
SQL Server 2014 Service Pack 2(SP2), then apply
Cumulative Update Package 10 for SQL Server 2014 SP2 — KB4052725. Also, try to allocate more memory to SQL Server.

If you have any other questions, please let me know.

Regards,

Hannah

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Edited by
  Hannah Yu
  Friday, January 26, 2018 5:52 AM
  edit
- Proposed as answer by
  Hannah Yu
  Thursday, February 1, 2018 9:51 AM
- Marked as answer by
  Olaf HelperMVP
  Saturday, November 10, 2018 8:07 AM

Источник

Today I am going to discuss SQL Server memory issues that your database Instance might face during bulk data load or during any memory intensive operations. You may also face insufficient memory issue when you try to execute numerous queries on large data sets at the same time in SQL Server. In such circumstances, SQL Server throws error 701 or error 802 with error text “There is insufficient system memory to run this query.” Full error descriptions for both error codes are given below.

Error 701, Severity: 17, State: 193.
There is insufficient system memory to run this query.

Error 802, Severity: 17, State: 20
There is insufficient memory available in the buffer pool.

Error 701 or Error 802: Root Cause

If SQL Server is running out of memory and has no more memory available to allocate to its transactions then it will generate SQL Server memory error 701 or error 802. SQL Server Memory pressure or insufficient memory issue can be raised due to huge data load or other memory intensive operations.

We should check DBCC MEMORYSTATUS output to get latest snapshot of current memory status of the SQL Server. This command is very useful in troubleshooting memory issues in SQL Server. You might even have failed to run this DBCC command because system does not have enough memory. You need to reduce some memory pressure by stopping or killing some processes and then try to run this command to gather in depth information about SQL Server memory state.

The transaction that failed due to memory pressure and generates error 701 or error 802 might be or might not be the cause of this error. We should analyze the trends to reach on any conclusion.

Solution

There are mainly two reasons behind getting SQL Server memory issues for most of the SQL Server systems. Either your server has less memory to handle the current day to day workload or you are running some of the adhoc transactions that will require more memory for their execution like huge bulk data load, complex reports etc. If your SQL Server is running with insufficient memory during day to day activities then you should seriously evaluate the total workload and propose optimum memory size that can handle your current workload.

If you are planning to run any heavy ad hoc transaction then you should temporary increase the memory that can handle the transaction smoothly or you should run such transactions on a server that are built to handle such loads. You should also consider to reduce the batch size that is optimum for your system.

SQL Server is memory intensive application. We need to plan memory sizing for a server after proper evaluation of server state and overall load that will run on that SQL Server instance. But if you are facing memory issue on one of your SQL Server then first we need to troubleshoot it and find the root cause of this memory pressure. Here I am giving you step by step method to find the actual culprit that are causing the memory issue and possible solutions that can reduce some memory overheads.

First Make sure your SQL Server instance is running on dedicated server and not with other applications. If other applications are also using your database server then make sure they are not eating most of the memory. And, If most of the system memory is taken by other applications then you need to seriously think about migrating those application from this database server.

If you cannot migrate these applications to another server then workaround for such issues are to allocate the maximum memory value to your SQL Server instance. You can do it either by using sp_configure or using GUI. Read attached article to change max memory value for your SQL Server instance.

Understanding SQL Server Memory Allocation and Role of MIN/MAX Server Memory Configuration

Next thing you should check is the server memory configuration. Check below SQL Server memory configuration parameters:

max server memory
min server memory
min memory per query

Notice any unusual settings and fix them as necessary. All above three options have been covered in detain in above attached article.

Now, check the workload in terms of number of concurrent sessions, currently executing queries along with their memory grants. You should also focus on SQL Server transactions that are seeking huge memory values. Look at the transactions and gather a report for high memory intensive operations. Evaluate them and if possible optimize them so that they can take minimum memory.

Understanding Memory Wait type Resource_SEMAPHORE and how to fix it?

You can also run below DBCC commands to free several SQL Server memory caches. This will also help you and make some relief for your current running transactions but in longer run it will not help if your system will need more memory to proceed the transactions.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Another effective way to look in to memory status is by getting the output of DBCC MEMORYSTATUS command. You would get a good starting point to look in to culprit memory consumers.

We can also start collecting performance monitor counters for memory. Perfmon counters like the value of SQL Server: Buffer ManagerBuffer Cache Hit Ratio, SQL Server: Memory Manager and Page Life Expectancy will be very helpful in identifying memory pressure on SQL Server system. Analyze the trend for these counters and then decide whether issue is coming during some specific operations or system is continuously facing memory pressure.

Based on these analyses you can choose to either increase the system memory, increase the SQL Server memory allocation or reschedule some of the memory specific transactions to the off hours when load is minimum on the system. You can also reduce the batch size of the transaction to reduce the memory pressure. This will be certainly helpful in troubleshooting memory issues.

It is not necessary that your transactions are poorly designed always that is why SQL Server is taking lot of memory to process it. Sometimes even our systems on which SQL Server runs might have problem that end up with memory pressure. So you should consider all these points while fixing any performance issue.
I hope you like this article. Please follow us on our Facebook page and Twitter handle to get latest updates.

Read More:

4 Usages of DBCC SQLPERF in SQL Server
Manage Transaction Log File during Bulk Data Load
How to Improve Bulk Data Load Performance?
Overview of SQL Server Performance Tuning
Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters

Author
Recent Posts

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.

Summary

Article Name

SQL Server Error 701: There is Insufficient System Memory to Run this Query

Description

Today I am going to discuss SQL Server memory issues that your database Instance might face during bulk data load or during any memory intensive operations. In such circumstances, SQL Server throws error 701 or error 802 with error text “There is insufficient system memory to run this query.”

Источник

MSSQLSERVER_701

Details

Explanation

External or OS memory pressure

Internal memory pressure, not coming from SQL Server

Internal memory pressure, coming from SQL Server component(s)

User action

Diagnostic tools and capture

Pssdiag or SQL LogScout

External pressure: diagnostics and solutions

Internal memory pressure, not coming from SQL Server: diagnostics and solutions

Internal memory usage by SQL Server engine: diagnostics and solutions

Quick relief that may make memory available

KB982854 — FIX: Error «There is insufficient system memory in resource pool ‘internal’ to run this query» in Microsoft SQL Server

Symptoms

Cause

Resolution

Cumulative update information

SQL Server 2008 Service Pack 1

SQL Server 2008 Service Pack 2

SQL Server 2008 R2

Status

References

SQL Server сообщает 701 «Недостаточно памяти для выполнения этого запроса» при выполнении больших пакетов

Симптомы

Причина

Решение

Дополнительная информация

MSSQLSERVER_701

Сведения

Пояснение

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

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

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

Рекомендуемые действия

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

PSSDIAG или SQL LogScout

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

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

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

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

Question

Answers

Aaron Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

Question

Answers

Aaron Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

Question

Answers

Aaron Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

Question

Answers

Error 701 or Error 802: Root Cause

Solution

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …

Aaron
Morelli on Twitter: «New Trace Flag: 8075 fixes VAS …