Всем добрый день.

Перебрались недавно на новые сервера.
На двух из них уже несколько раз выскакивала ошибка: There is insufficient system memory in resource pool 'internal' to run this query.
И сервер отъедает памяти больше, чем установлено в настройках.
Про саму ошибку я почитал:
1) Не выставлено ограничение max server memory
2) Баг версии сервера, исправляется нужным обновлением.

Суть в том, что:
а) ограничение max server memory стоит: на главном 20 из 24гб, на двух других 4 и 4.5 из 6гб. На серверах, кроме MS SQL ничего не крутится. После настроек сервера были перегружены.
б) сервера обновлены до sp3. Сисадмин утверждает, что нужный апдейт входит в установленные сервис паки.

Единственное отличие от старых серверов, где подобных проблем не было, что те были sp2.

Версия сервера: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Два второстепенных сервера уже пришлось экстренно перегружать из-за того, что MS SQL съел больше памяти, чем ему положено. Но это было не критично.
А вот остановка основного сервера критична днем даже на 5минут. Там просто памяти больше 20, но он уже сожрал 21 с копейками.

Я уже и не знаю, куда копать. Это таки бага верчсии\сервис пака, или мы что-то недонастроили в дб сервере?
---
Проходя мимо разложенных граблей, ты теряешь драгоценный опыт. (с)

Всем спасибо. Буду копать.
Clr-сборок нет.
xml, ole - да, используем.

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

alexeyvgMegabyteНа двух из них уже несколько раз выскакивала ошибка: There is insufficient system memory in resource pool 'internal' to run this query.
И сервер отъедает памяти больше, чем установлено в настройках.Это не та память, которую вы указываете в настройках. В настройках задаётся страничный кэш. Так что задавайте с запасом.
Ну и ошибка говорит о баге, а не о нехватке памяти на сервере. Копайтесь, смотрите.
Вот, например, тема: http://www.sql.ru/forum/970396/error-701-there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query
Ну и вообще поищите по названию ошибки (если не поможет снижение max memory)
Да, спасибо. Тему эту конечно же читал, но поверхносно.

Гигабайт Мегабайтович КилобайтовMegabyteВсем спасибо. Буду копать.
Clr-сборок нет.
xml, ole - да, используем.

Просто странно, что функционал при переезде не менялся, а проблемы появились.
а настройки памяти менялись?))
Памяти либо добавили, либо не изменилось. :)

Или вы про какие-то другие настройки?

Реанимирую тему:
Из 2х проблемных на одном проблемы ушли, после того, как установил 4гб из 6, вместо 4.5гб.
У 2го так и было 4 из 6.
И у него же появились новые проблемы:
22.12.2017 в логе увидел такую ошибку: " Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536"
После нее была куча сообщений, связанных с памятью(для меня пока малопонятных):
автор Memory Manager KB ---------------------------------------- ---------- VM Reserved 6446192 VM Committed 4477832 Locked Pages Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0
Memory node Id = 0 KB ---------------------------------------- ---------- VM Reserved 6442544 VM Committed 4474296 Locked Pages Allocated 0 MultiPage Allocator 231040 SinglePage Allocator 1604544
Memory node Id = 64 KB ---------------------------------------- ---------- VM Reserved 2560 VM Committed 2504 Locked Pages Allocated 0 MultiPage Allocator 2416 SinglePage Allocator 1604544
MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3248 MultiPage Allocator 7176
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ---------- VM Reserved 6168576 VM Committed 4202880 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 0 MultiPage Allocator 400
MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 12952 MultiPage Allocator 21408
MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 248 MultiPage Allocator 896
MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ---------- VM Reserved 240 VM Committed 240 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 96 MultiPage Allocator 0
MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ---------- VM Reserved 11136 VM Committed 11136 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8192 MultiPage Allocator 4192
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 3752 MultiPage Allocator 0
MEMORYCLERK_SQLCLR (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 8 MultiPage Allocator 0
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 152 MultiPage Allocator 544
MEMORYCLERK_SQLXML (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 SinglePage Allocator 16 MultiPage Allocator 0
Просто раньше в логе их не наблюдал.

Заметил, что на сервере стал часто появляться тип ожидания: PAGELATCH_XX.
Почитал в статье на хабре про этот тип ожидания:
авторЭто конкуренция за доступ к копиям страниц в памяти. Наиболее известные случаи — это конкуренция PFS, SGAM, и GAM, возникающие в базе tempdb при определенных типах нагрузок (англ.). Для того, чтобы выяснить, за какие страницы идет конкуренция, вам нужно использовать DMV sys.dm_os_waiting_tasks для того, чтобы выяснить, из-за каких страниц возникают блокировки. По проблемам с базой tempdb Роберт Дэвис (его блог, твиттер) написал хорошую статью, показывающую, как их решать (англ.) Другая частая причина, которую я видел — часто обновляемый индекс с конкурирующими вставками в индекс, использующий последовательный ключ (IDENTITY).

Выполнил запрос:


Таки да, с tempDB что-то не так:
Database_Name Ср.задержка одной операции чтения Ср.задержка одной операции записи (Отсутствует имя столбца)
tempdb 92 2752 tempdb.mdf
tempdb 167 85 templog.ldf

2752 - ср. задержка записи.

По ссылке конкуренция PFS, SGAM, и GAM, возникающие в базе tempdb при определенных типах нагрузок (англ.) из статьи на хабре надеялся прочитать про причины возникающих проблем, но увидел только то, что предлагается сделать несколько файлов tempDB. Для чего, я не сильно понял...

Вопросы:
1) По какой причине могла возникнуть ошибка " Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536"?
2) Как избавляться от ожиданий PAGELATCH_XX?
3) И связан ли п.1 с п.2?
4) Надо ли что-то делать с tempDB? И если да, то что?
5) Разбирение tempDB на несколько файлов, но расположенных на одном диске, какую-то пользу вообще дает?

КритикMegabyte2) Как избавляться от ожиданий PAGELATCH_XX?
5) Разбирение tempDB на несколько файлов, но расположенных на одном диске, какую-то пользу вообще дает?

2) добавить больше дисков, SSD, добавить памяти, оптимизировать свой код
5) да
2) Код оптимальный, насколько возможно. Да и в принципе этот сервер не такой нагруженный, по сравнению с двумя другими.
Дисков добавить пока нет возможности. Память теоретически можно нарастить. Но хотелось бы самому разобраться, что причина именно в этом.

5) А подробнее можно? В чем будет плюс? Если бы другие файлы были бы на другом диске, я бы понял пользу...

EleanorMegabyteА подробнее можно? В чем будет плюс? Если бы другие файлы были бы на другом диске, я бы понял пользу...
По вашей ссылке:
1) When you see PAGELATCH_XX waits on tempdb, you’ve got contention for in-memory allocation bitmaps.
2) When you see PAGEIOLATCH_XX waits on tempdb, you’ve got contention at the I/O subsystem level.

У вас первый случай, конкуренция за страницы в памяти , а не на диске .
Создавая несколько файлов, вы пытаетесь увеличить количество allocation pages в памяти, за которые идет конкуренция.

Если бы были PAGEIOLATCH_XX, то от размещения файлов tempdb на разных дисках и ускорения дисковой подсистемы была бы польза.
Спасибо за разъяснение. С PAGEIOLATCH_XX я уже сталкивался ранее понимаю, в чем суть.
С PAGELATCH_XX вот впервые...

step_ksMegabyteВсем добрый день.

Перебрались недавно на новые сервера.
На двух из них уже несколько раз выскакивала ошибка: There is insufficient system memory in resource pool 'internal' to run this query.


1. В "min memory per query" ничего неординарного не выставлено?
2. Попробуйте определить запросы, на которых падает. Возможно, их не так много и у них будет что-то общее.
1) На всех серверах 1024кб, как по умолчанию. Честно говоря, не в курсе, на что и как влияет этот параметр.
2) Запросов не так много: xml-заливка(через OPENXML) данных для мониторинга работы нашей системы, ну и много селектов из web-приложения к этим данным.
Заметил, что периодически блокировка идет именно на селективных запросах: блокируют друг друга. Сами запросы работают быстро.
Опять же заметил, что подобные проблемы появились при переезде на новый сервер, тогда как функционал не менялся.

2f000: спасибо, ознакомлюсь.

Реанимирую тему.

Просмотрел лекцию.
f000про tempdb - послушайте Диму АртЁмова
https://www.techdays.ru/videos/6565.html
Изучил типы страниц IAM, GAM, SGAM, PFS.

Увидел, что на проблемном сервере тип ожидания PAGELATCH_% занимает 25% от всех ожиданий.

Решил добавить 1 файл tempDB. В рекомендациях Мелкософта советуют 1 файл на каждый процессор(на сервере 4 процессора), но решил посмотреть, как повлияет добавление файла.

При добавлении файла кол-во блокировок только возросло, ср. время записи 2го файла так же резко подскочило.

Начал копать, наткнулся на тему "Помогите понять что с памятью SQL SERVER R2 enterprise" .
Заметил аналогичные проблемы у себя:




Результаты:
1) OBJECT_NAME Counter_name cntr_value cntr_value_MB
SQLServer:Buffer Manager Database pages 92559 723.000000000
SQLServer:Buffer Manager Free pages 23469 183.000000000
SQLServer:Buffer Manager Total pages 140832 1100.000000000
SQLServer:Memory Manager Target Server Memory (KB) 4096000 4000.000000000
SQLServer:Memory Manager Total Server Memory (KB) 1126656 1100.000000000

2) physical_memory_in_use_kb
1316256

3) type name multi_pages_mb
MEMORYCLERK_SOSNODE SOS_Node 15
MEMORYCLERK_SQLGENERAL Default 13
CACHESTORE_SQLCP SQL Plans 7
MEMORYCLERK_SQLSTORENG Default 4
CACHESTORE_OBJCP Object Plans 3
USERSTORE_TOKENPERM TokenAndPermUserStore 1
MEMORYCLERK_XE XE Engine 0
CACHESTORE_STACKFRAMES SOS_StackFramesStore 0
USERSTORE_SCHEMAMGR SchemaMgr Store 0
OBJECTSTORE_SNI_PACKET SNIPacket 0
OBJECTSTORE_LBSS LbssCache 0
MEMORYCLERK_HOST MSDART 0
MEMORYCLERK_SQLOPTIMIZER Default 0
MEMORYCLERK_SQLSERVICEBROKER Default 0
CACHESTORE_PHDR Bound Trees 0
MEMORYCLERK_SNI Default 0
MEMORYCLERK_SQLBUFFERPOOL Default 0

В теме, как лечить, увидел только предложение апгрейда до 2012. Мы это, конечно, планируем, но не в ближайшее время.

Буду благодарен, если направите в нужном направлении.
Повторюсь, проблемы появились после переезда на другой сервер: машина и SQL Server(C 2008 R2 SP2 на 2008 R2 SP3).
Хотя, возможно, проблема и была раньше, но мы ее не замечали, либо не стояла так остро.

Такой вопрос: А временные таблицы, созданные как
SELECT INTO кешируются MS SQL?

Просто в одном запросе, который очень часто вызывается и, судя по различным выборкам, юзает много ресурсов, есть такой запрос:



Имеет ли смысл для производительности\кеширования явно задать структуру временной таблицы?

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

1) Добавление файлов tempDb до 4шт на 4 процессора, как по рекомендации Микрософт, ничего не дало.
2) Начал копать запросы, нашел, что несколько удаленных запросов с другого сервера не самые оптимальные.
Переделал, после этого по статистике вылезли пара необходимых индексов. До этого статистика почему-то показывала только 1 дорогой, но реально совершенно бесполезный индекс(потому как точно такой индекс уже был).
Это снизило ср. время чтения\записи по tempDB, но полностью проблемы не исправило.
3) Сделал настройку SET READ_COMMITTED_SNAPSHOT ON на рабочей базе при стандартном уровне изоляции READ_COMMITTED.
Это уменьшило тип ожидания PAGELATCH_UP на порядок. Проверил по статистике типов ожиданий, которую сохраняю.
Ср. время чтения\записи tempDB и рабочей базы еще больше уменьшилось.

В итоге, полностью блокировки тира PAGELATCH_% полностью не ушли, но субъективно стали короче по времени.

Ошибок памяти пока не наблюдается. Но я сервер перегружал недавно. Буду мониторить.

Наконец-то дошло проверить типа страниц, за которые идет конкуренция.

Мониторил через запрос


Потом проверялв этом запросе по их PAGE_INDEX.



Все страницы были типа PFS в TempDB.

Из всех источников рекомендации были только увеличить кол-во файлов TempDB, что мне лично при эксперименте не помогло.
Это единственный вариант? Куда и что еще посмотреть?

p.s. В базе активно юзается импорт из xml, данные заливаются постоянно с высокой частотой, но объем данных небольшой.
Использую OPENXML. После выгрузки данных во времянку сразу же делаю sp_xml_removedocument.
Потом эти же данные постоянно запрашиваются в нашем ПО для мониторинга менеджерами(web-страница, открыта постоянно и постоянно обновляется). Вечером, когда нагрузка ниже(меньше менеджеров работает), проблем с блокировками практически не наблюдается.

архивариусMegabyteЕсли вдруг кому будет интересно, напишу, что делал.
1) Добавление файлов tempDb до 4шт на 4 процессора, как по рекомендации Микрософт, ничего не дало.

1 шт. не на процессор, а на физическое ядро, до 8 шт не задумываясь, если больше то нужно смотреть.(даже на одном и том же диске)

авторRecommendations to reduce allocation contention in SQL Server tempdb database
Symptoms
You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. Additionally, you will notice that the wait type is PAGELATCH_UP and wait resource points to pages in tempdb. These pages might be of the format 2:1:1, 2:1:3 and so on (PFS and SGAM pages in tempdb).
Note If a page is evenly-divisible by 8088, then it is a PFS page. For example, page 2:3:905856 is a PFS in file_id=3 in tempdb.
Cause
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be unresponsive for short periods of time.
Resolution
There are several steps you can take to improve the concurrency of tempdb:

As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Consider implementing the best practice recommendations in the Technet article titled Working with tempdb in SQL Server 2005.

If the previous steps do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118. Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. Note that this trace flag affects every database on the instance of SQL Server. For information about how to determine whether the allocation contention is on SGAM pages, see Monitoring contention caused by DML operations subtopic under Working with tempdb in SQL Server 2005 page on Technet.
Starting with SQL Server 2016, some of these configuration changes are automatic and do not need user intervention. For more information
инсталятор SQL сам создает теперь несколько файлов tempdb .
Мы пока на 2008м, правда скоро планируем апгрейд.

архивариусMegabyteЕсли вдруг кому будет интересно, напишу, что делал.
1) Добавление файлов tempDb до 4шт на 4 процессора, как по рекомендации Микрософт, ничего не дало.

1 шт. не на процессор, а на физическое ядро, до 8 шт не задумываясь, если больше то нужно смотреть.(даже на одном и том же диске)

У нас сервера на виртуалках. Под проблемный сервер выделено 4 ядра.
Т.е. таки 4 файла, минимум, делать?

Yasha123Megabyteпропущено...

У нас сервера на виртуалках. Под проблемный сервер выделено 4 ядра.
Т.е. таки 4 файла, минимум, делать?
слушайте, а вообще есть ли смысл вам отвечать?
выше приведена ссылка на Рэндала, про кол-во файлов там четко написано,
но наверное, лучше всем миром проголосовать?

автор#3 ( Create multiple tempdb data files) will help to remove the PFS page contention, by spreading the allocation workload over multiple files, thus reducing contention on the individual, per-file PFS pages. But how many data files should you create?

The best guidance I’ve seen is from a great friend of mine, Bob Ward, who’s the top Escalation Engineer in Microsoft SQL Product Support. Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too. (This advice is now official Microsoft guidance in KB article 2154845.)

Я к чему спрашивал, может есть какие-то различия для виртуалок... Статью Рэндала читал и даже делал уже 4 файла: на кол-во блокировок это не особо повлияло.

Хочу включить флаг трассировки при запуске. Добавляю в параметры запуска:
"-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf; -T1118"

Перегружаю службу. Проверяю:


Результат:
TraceFlag Status Global Session
1118 0 0 0

Подскажите, почему флаг не активируется? Что не так делаю?

При активации скриптом:


Результат виден:
TraceFlag Status Global Session
1118 1 1 0

Народ, ну подскажите, что не так делаю?

Настройку делаю по статьям:
https://technet.microsoft.com/ru-ru/library/ms190699(v=sql.105).aspx
https://toster.ru/q/264853