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

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.”

Источник

Microsoft SQL Server throws error 701 if there is any insufficient memory issue.

Here at Bobcares, we have seen several such SQL related issues as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at the cause for this error and how to fix it.

Why does Microsoft SQL Server error 701 occur

Now let’s see in detail why actually does this error occurs.

During any bulk data load or during any memory-intensive operations, the database instance might face some SQL Server memory issues.

We may also come across some insufficient memory issue while trying to execute numerous queries on large data sets at the same time in SQL Server.

In such cases, SQL will throw 701 errors. Here is an image of the error message ‘Application Log error 701’

Mainly this error occurs if SQL Server is running out of memory and has no more memory available to allocate to its transactions.

How we fix Microsoft SQL Server error 701

Now let’s see what solution our Support Engineers provide.

If we are facing any memory issue in the SQL server then we first need to troubleshoot the issue and find the root cause for the memory consumption.

Here is the step by step process to find the cause of memory consumption and possible solutions.

1. SQL Server instance running on a dedicated server

First, make sure that the SQL Server instance is running on a dedicated server and not with other applications.

In case, if other applications are also using the database server, then make sure they are not eating most of the memory.

If other applications are taking more memory then migrate it to other database servers. If it is not possible to migrate then allocate the maximum memory value to your SQL Server instance. You can do it using SQL Server Management Studio.

First, right-click on SQL Server Instance and select properties.
The server properties window will appear on the screen, now click on the “memory” tab from the left side pane.
You can change the value of min and max server memory as per your need in the right-side panel.

2. Check server memory configuration

Secondly, check the server memory configuration. Here are the SQL Server memory configuration parameters that we need to check:

max server memory
min server memory
min memory per query

If there is any difference in the value of max server memory and min server memory then increase the value of ‘max server memory’.

3. Check the workload

Check the workload in terms of the number of concurrent sessions. Also, check the SQL Server transactions that are using huge memory values. Make a report on them and try to optimize such that it will take minimum memory.

4. Find Index usages for long-running queries

Find out index usages for long-running queries. We need to do this because without proper indexing your system DISK I/O increases and it directly affects your memory.

5. Run DBCC commands

We also recommend running the below DBCC commands to free several SQL Server memory caches.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

6. The output of the DBCC MEMORYSTATUS command

Getting the output of DBCC MEMORYSTATUS command is also a great way to look into the memory status.

7. Performance monitor counters for memory

Start collecting performance monitor counters for memory. Performance counters like the value of SQL Server: Buffer ManagerBuffer Cache Hit Ratio, SQL Server: Memory Manager, and Page Life Expectancy will be of great help in identifying memory pressure on the SQL Server system.

Then you can analyze these counters and decide whether the 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 the load is minimum on the system.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL Server Memory pressure or insufficient memory issue can be raised due to huge data load or other memory-intensive operations that lead to a 701 error in the SQL server.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

Содержание

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

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

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

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

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

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

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

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

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

PSSDIAG или SQL LogScout

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

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

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

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

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

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

Ниже приведен пример коллекции журналов Perfmon с помощью PowerShell.

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

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

Ниже приведен пример скрипта PowerShell для запроса к журналам событий System и Applicaiton по ключевому слову «memory». Вы можете использовать для поиска другие строки, такие как «resource»:

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

Если приложения, кроме 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:

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

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

Вы также можете подробнее проанализировать выходные данные DBCC MEMORYSTATUS и происходящие в них изменения при появлении сообщений об ошибках.

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

Если память потребляется клерком MEMORYCLERK_SQLQERESERVATIONS, определите, каким запросам выделяется большой временно предоставляемый буфер памяти, а затем оптимизируйте эти запросы с помощью индексов, перепишите их (например, удалите ORDER by) или используйте указания запроса.
Если кэшируется большое число нерегламентированных планов запросов, то клерк памяти CACHESTORE_SQLCP будет использовать большой ее объем. Определите непараметризованные запросы, планы запросов которых нельзя использовать повторно, и параметризуйте их путем преобразования в хранимые процедуры или с помощью sp_executesql или параметризации FORCED.
Если хранилище кэша планов объектов CACHESTORE_OBJCP потребляет много памяти, определите, какие хранимые процедуры, функции или триггеры используют большой объем памяти и, если возможно, спроектируйте приложение иным образом. Такое обычно происходит при больших объемах баз данных или схем с сотнями процедур в каждой.
Если клерк памяти OBJECTSTORE_LOCK_MANAGER показывает большие объемы ее выделения, определите, какие запросы применяют множество блокировок, и оптимизируйте эти запросы с помощью индексов. Сократите транзакции, которые создают длительные блокировки на определенных уровнях изоляции, а также проверьте, не включено ли укрупнение блокировки.

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

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

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

max server memory

min server memory

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

Если вы не настроили 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 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

Источник

Error 701 or Error 802: Root Cause

Solution

Why does Microsoft SQL Server error 701 occur

How we fix Microsoft SQL Server error 701

1. SQL Server instance running on a dedicated server

2. Check server memory configuration

3. Check the workload

4. Find Index usages for long-running queries

5. Run DBCC commands

6. The output of the DBCC MEMORYSTATUS command

7. Performance monitor counters for memory

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

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 …