Sql server не освобождает память
Несколько постов в нашей группе телеграмм послужили причиной для написания данной статьи.
И хоть вопросы немного разнятся, но проблема как оказалось у всех одинакова:
«MS SQL скушал, забрал, использовал всю оперативку»
Действительно не редкие случаи, когда MS SQL чрезмерно употребляет ОЗУ и если не убавить его аппетиты можно и совсем остаться без свободной оперативной памяти.
Первое так сказать быстрое и «почти универсальное» решение проблемы чрезмерного употребления ОЗУ в MS SQL это указать в свойствах MS SQL (вкладка «Память») тот объем ОЗУ, который мы можем отдать на нужды «сиквела». (Не забывайте после перезапустить MS SQL)
Более подробная информация по вопросу выделения ОЗУ, есть на курсе: Администратор 1С.
На картинке выше указанно 4 Гб которые может употребить MS SQL (И обычно за «Максимальный размер памяти сервера он и не выходит»).
Таким образом, мы снимаем «острую» проблему с потреблением ОЗУ в MS SQL.
Конечно, при всем этом сразу возникает много вопросов:
1. Почему MS SQL не освобождает память ?
2. Сколько ОЗУ для моего MS SQL установить ?
3. Как определить сколько ОЗУ нормально для MS SQL ?
4. Можно-ли не наращивать объем ОЗУ для MS SQL ?
5. Чем грозит ОЗУ в MS SQL ?
В этой статье попытаюсь дать ответы на выше перечисленные вопросы так, чтоб и новичкам было понятно и пользователи с опытом также могли, что-то почерпнуть из публикации.
Главный вопрос: «Почему MS SQL не освобождает память, неужели он не умеет это делать ?
Умеет!
MS SQL умеет динамически работать с ОЗУ!
Вот что пишет MS:
Когда SQL Server использует память динамически, он периодически опрашивает систему, чтобы определить объем свободной физической памяти. SQL Server использует API уведомления памяти QueryMemoryResourceNotification, чтобы определить, когда можно выделить и освободить память буферного пула.
Но почему же это не всегда происходит?
Все просто.
1С Предприятие по своей «натуре» создает много временных таблиц, которые вынуждают MS SQL брать больше ОЗУ, заполнять свой буферный пул теми данными, которые в 1С часто востребованы, чтоб обеспечить максимальною производительность.
Безусловно, это нормально поведение не только MS SQL, но и большинства других СУБД.
Только сведя к минимуму операции ввода /вывода с диска (работая с ОЗУ) можно добиться максимальной производительности, что собственно и пытается делать MS SQL.
К сожалению не только «природа» 1С Предприятия способствует чрезмерным аппетитам «сиквела», тут здорово помогают и «кривые запросы» и «ошибки» в коде, и конечно все это ведет к тому, что MS SQL употребляет ОЗУ больше чем мы рассчитывали, (часто всю что видит).
Другими словами, MS SQL не виноват в том, что 1С «дает повод» брать больше ОЗУ и не дает основания ее освобождать.
Благо в MS SQL есть инструмент позволяющий «руками» ограничить потребление ОЗУ, что собственно в самом начале статьи и продемонстрировали на скрине.
Конечно, помимо инструментов есть, и советы от Microsoft касательно MS SQL:
Рекомендуется устанавливать MS SQL единственным (кроме системы) софтом.
Так он не будет конфликтовать за ресурсы с другими программами и сможет взять ОЗУ сколько ему потребуется.
Объем ОЗУ (в идеале) должен быть равен размеру всех баз.
Другими словами если у Вас 3 базы по 10 Гб, размер ОЗУ для MS SQL в идеале 30 Гб.
Безусловно в идеале и «миллион» долларов вряд ли бы кого расстроил ) но исходим от того что имеем ), и 30% процентов от баз также будет очень хорошо! (Во многих случаях и меньше того).
Физика работы MS SQL, проста в базовом плане потребления ОЗУ, помещаем в буфер то, что часто используется, чтоб обеспечить как можно лучшую производительность.
Если «сиквел» обнаружит, что у него всего 30% ОЗУ он будет просто больше писать и читать с диска и обходится тем, что есть. Да, конечно, всему есть придел и слишком большой дефицит ОЗУ приведет сперва к падению производительности (хорошо будет заметно при формировании отчетов в 1С), а потом и к различным ошибкам, вплоть до «вылета» программы.
(Рекомендую время от времени просматривать журнал MS SQL не сыпется ли уже ошибки связанные с памятью, особенно обратить внимание на строки memory pressure).
Вот мы и подошли к еще одному Важному вопросу:
«Так сколько ОЗУ надо для счастливой» жизни «сиквелу» ) ?
В рамках данной статьи, попытаюсь дать ответ и на этот не простой вопрос, или как минимум указать верное направление)
Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>
(0) Во-первый SQL Server память освобождает, но не сразу, а через некоторое время.
Во-вторых, нужно выяснить, из-за чего "1С начинает работать медленнее". Может быть, совсем не из-за нехватки памяти.
Сколько памяти возьмет 1С, если ей вдруг освободить? Не 350 Гб же.
(8) (9)
Свободная память остается, за этим следим, даже rphost немного съедает. Основная нагрузка на CPU скуля и память, которую он съедает.
Для теста взял уменьшил лимит до 50 Гб и SQL потихоньку освободил память до 50 ГБ. Потом вернул назад 350 ГБ. Через час уже 80 Гб забрал, хотя расчеты крупные ночью происходят.
В общем делаю вывод, что не нужно пытаться освобождать эти 350 ГБ. Если только для проверки как быстро он назад их заберет и хоть какой-то проверки ее достаточности.
Если вы попробуете после своей фразы "SQL Server не освобождает занятую им память, хотя данные в ней уже давно не актуальны", ответить на вопрос "Что значит данные не актуальны", да ещё в свете SQL индексов, планов и статистики запросов,
то вот тут вы поймёте, что ответить на это очень сложно.
Так что оставьте SQL как есть.
Лучше поищите инфу на тему "Счётчики SQL сервера и их анализ", это будет полезнее.
вар2. Может слишком жадный запрос? убирать излишнюю детализацию, сколько раз видел - вытаскивают по 100 полей в отчет для какого нибудь аналитика, который именно сводно строит по 8 полям, а остальные 90 нужны для расшифровок.
вар3. Если такое количество ВТ нужно держать активными - может подумать чтобы вместо этих ВТ создать новые справочники/регистры?
Короче это хорошая задачка для эксперта по технологическим вопросам. )))
Может у вас, из-за больших изменений после "алгоритма" планы запроса не актуальны. попробуйте после закрытия статистику обновить.
(0) А зачем вы вообще ожидаете что СГЛ вам чтото будет возращать? Ему выделили память, он ее в какой то момент всю занял. Дальше сам будет разбираться что и как лучше с ней делать.
Удаляет все неиспользуемые элементы из всех кэшей. Компонент Компонент SQL Server Database Engine заранее автоматически очищает неиспользуемые элементы кэша в фоновом режиме, освобождая память для текущих записей. Однако можно использовать эту команду, чтобы вручную удалить неиспользуемые записи из всех кэшей или из указанного кэша пула регулятора ресурсов.
(17) Кэш создан чтобы помогать, а не чтобы его руками чистить.
(15) Скорее всего все проще. Во время тяжелого расчета вытесняются справочники и индексы из кэша, отсюда и "замедление".
то что я сказал в (13) - во первых для чего нужен такой тяжелый механизм расчета? обычно всегда можно сложный процесс разбить на более простые.
(25) Вот уж сказанул так сказанул.
Что-то не вижу по ссылке ничего про тупизну 1С-ников.
(29) Скрипт Пола искажает и прячет? Можно пруф? А то DBA всего мира им пользуются и не знают
WHERE [wait_type] NOT IN ( <73 ожидания> )
These wait types are almost 100% never a problem and so they are
-- filtered out to avoid them skewing the results. Click on the URL
-- for more information.
Tester, у нас были проблемы "внезапно начало тупить" или "конфликт блокировок на любом документе". Помогало уменьшить потом увеличить лимит памяти sql. Позже прописали в план обслуживания (раз в сутки, утром):
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'5000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
Т.е. 2 задания, в одном (условно) 5000, во втором (через 5 минут) обратно на 358400.
Такой же эффект (для описанных проблем) дает очистка процедурного кеша сервера dbcc freeproccache (а лучше аналог DBCC FLUSHPROCINDB (DB_ID()) - только для одной базы).
Это конечно не правильное решение проблемы, но за не имением лучшего, обходимся тем что есть.
каков хороший способ проверить, сколько (фактической) памяти в настоящее время используется против того, сколько SQL Server выделяется для себя?
я прибегал к memory_utilization_percentage но это, похоже, не меняется после запуска следующего, чтобы освободить память.
решение-удалить максимальная память сервера для SQL Server и увеличить его снова, чтобы заставить SQL Server освободить неиспользуемые, но выделенная память. Однако проблема с этим подходом заключается в том, что мы не можем быть уверены, насколько уменьшить максимальная память сервера, следовательно, рискуют убить SQL Server. Вот почему важно понять, сколько SQL Server "фактически" использует, прежде чем уменьшать значение для максимальная память сервера.
SQL Server всегда предполагает, что это основное запущенное приложение. Он не предназначен для совместного использования ресурсов. Он всегда будет занимать всю доступную память, и он будет выпускать ее только для операционной системы, если вы не дросселируете "max server memory".
по дизайну Sql Server не играет хорошо с другими.
в этой статье sqlskills рекомендует базовый уровень для дросселирования с последующим мониторингом и повышением дроссельной заслонки как нужны:
У меня нет решения для того, чтобы освободить выделенную память. Однако для наших целей мы смогли выяснить, как позволить активно-активным кластерам безопасно работать. Мы решили установить минимальная память сервера до
2 ГБ. Это полезно, потому что независимо от того, сколько max памяти экземпляр решает использовать, он никогда не будет запускать другие экземпляры из памяти. Опять же, это решает нашу цель, но все равно не отвечает на вопрос о том, сколько памяти фактически используется, как низко мы можем отбросить максимальную память сервера и т. д.
вы должны установить "Max Server memory" на некоторое значение между 1-2 ГБ. Этот диапазон безопасен в большинстве случаев. Это может занять некоторое время, чтобы освободить память после выполнения ниже:
Эта настройка позволяет очистить пул, скомпилировать память, все кэши, память среды clr и т. д.
минимальное значение для "max Server memory" - 128 МБ, но это не рекомендуется, так как SQL Server может не запускаться в определенных конфигурациях. Если это произойдет, используйте переключатель"- f", чтобы заставить SQL начать с минимальная конфигурация, затем измените значение на исходное.
этот пост решается по следующей ссылке, пожалуйста, проверьте формат:
Я не думаю, что SQL Server освобождает память, если операционная система активно не запрашивает ее. Если есть случай других процессов, требующих больше памяти, и если нет вообще, SQL Server освободит неиспользуемую память самостоятельно. А не пытаться промыть unusued памяти, я бы, наверное, пойти с ограничением по SQL позволило памяти.
SQL Server In-Memory OLTP использует больше памяти, чем SQL Server, и делает это по-другому. Возможно, что объем памяти, установленный и выделенный для Выполняющаяся в памяти OLTP , станет недостаточным для растущих потребностей. В таком случае может возникнуть нехватка памяти. В этом разделе описывается восстановление из ситуации с нехваткой памяти. В статье Наблюдение и устранение неисправностей при использовании памяти вы найдете рекомендации, которые помогут вам избежать многих ситуаций нехватки памяти.
Темы данного раздела
устранить ошибки восстановления базы данных, возникающие из-за нехватки памяти
Сервер, на который восстанавливается база данных, должен иметь достаточно памяти для оптимизированных для памяти таблиц в резервной копии базы данных, в противном случае база данных не подключится к сети и будет помечена как подозрительная.
Если сервер имеет достаточный объем физической памяти, но по-прежнему возникает данная ошибка, возможно, что другие процессы используют слишком много памяти или операция восстановления не получает достаточный объем памяти из-за проблем с конфигурацией. При подобных проблемах воспользуйтесь следующими мерами, чтобы предоставить операции восстановления дополнительную память.
Временно закройте выполняющиеся приложения.
Закрыв одно или несколько выполняющихся приложений или остановив ненужные на данный момент службы, можно освободить используемую ими память для операции восстановления. Эти приложения можно будет перезапустить после успешного завершения восстановления.
Увеличьте значение MAX_MEMORY_PERCENT.
Если база данных, как и рекомендуется, привязана к пулу ресурсов, то память, доступная для операции восстановления, регулируется параметром MAX_MEMORY_PERCENT. Если значение слишком мало, восстановление завершится со сбоем. В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
Дополнительные сведения о максимальных значениях параметра MAX_MEMORY_PERCENT см в разделе Процент памяти, доступной для оптимизированных для памяти таблиц и индексов.
Увеличьте значение max server memory.
Дополнительные сведения о настройке параметра Макс. памяти сервера см. в разделе Параметры конфигурации сервера "Память сервера".
устранить влияния нехватки свободной памяти на рабочую нагрузку
Очевидно, проще всего вообще избегать ситуаций, связанных с нехваткой памяти. Помочь в этом может хорошее планирование и отслеживание. Однако даже самое хорошее планирование не гарантирует стабильной работы, и возникновение нехватки памяти всегда возможно. Для устранения этой ситуации необходимо выполнить два действия.
Откройте выделенное административное соединение
SQL Server предоставляет выделенное административное соединение (DAC). С помощью выделенного административного соединения администратор может обращаться к запущенному экземпляру ядра СУБД SQL Server для устранения неполадок на сервере, даже если сервер не отвечает на другие клиентские соединения. DAC доступны в программе sqlcmd и в среде SQL Server Management Studio.
Рекомендации по использованию DAC в SSMS или sqlcmd см. в разделе Диагностическое подключение для администраторов баз данных.
Примените действие по исправлению
Для устранения проблемы с нехваткой памяти необходимо либо освободить имеющуюся память путем сокращения объема ее использования, либо выделить дополнительный объем памяти таблицам в памяти.
Освобождение имеющейся памяти
Удаление неважных строк оптимизированных для памяти таблиц и ожидание выполнения сборки мусора
Можно удалить неважные строки из оптимизированной для памяти таблицы. Сборщик мусора делает объем памяти, используемый этими строками, доступным. Компонент In-memory OLTP выполняет сбор ненужных строк агрессивно. Однако долго выполняющаяся транзакция может помешать сбору мусора. Например, если имеется транзакция, которая выполняется в течение 5 минут, все версии строк, созданные из-за операций обновления или удаления во время выполнения транзакции, не подпадают под сборку мусора.
Переместить одну или несколько строк в таблице на диске
В следующих статьях TechNet представлены рекомендации по перемещению строк из таблиц, оптимизированных для памяти, в таблицы на диске.
Увеличение объема доступной памяти
Увеличение значения MAX_MEMORY_PERCENT для пула ресурсов
Если именованный пул ресурсов для таблиц в памяти еще не создан, то его необходимо создать и привязать к нему базы данных Выполняющаяся в памяти OLTP . Инструкции по созданию пула ресурсов и привязки к нему баз данных см. в разделе Привязка базы данных с таблицами, оптимизированными для памяти, к пулу ресурсов Выполняющаяся в памяти OLTP .
Если база данных Выполняющаяся в памяти OLTP привязана к пулу ресурсов, то пользователь может увеличить процент памяти, доступной для пула. Инструкции по изменению значения MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для пула ресурсов см. в подразделе Изменение параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула .
Увеличьте значение MAX_MEMORY_PERCENT.
В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
Дополнительные сведения о максимальных значениях параметра MAX_MEMORY_PERCENT см в разделе Процент памяти, доступной для оптимизированных для памяти таблиц и индексов.
Установка дополнительной памяти
В конечном счете наилучшим решением является установка дополнительной памяти. Если выбран этот вариант, то необходимо учитывать, что, скорее всего, также можно будет увеличить значение MAX_MEMORY_PERCENT (см. подраздел Изменение параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула), так как SQL Server вряд ли будет нужно больше памяти, а это позволит выделить большую часть или даже всю установленную новую память пулу ресурсов.
Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.
Устранение ошибок выделения страниц, возникших из-за нехватки памяти при наличии достаточных ресурсов памяти
Для устранения этой ошибки необходимо включить регулятор ресурсов.
См. в разделе Включение регулятора ресурсов дополнительные сведения об ограничениях, а также рекомендации по включению регулятора ресурсов через обозреватель объектов, свойства регулятора ресурсов или Transact-SQL.
Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин
Виртуализация серверов позволяет не только снизить расходы на приобретение и эксплуатацию, но и добиться большей эффективности ИТ-процессов благодаря оптимизации подготовки, обслуживания, доступности и операций резервного копирования или восстановления приложений. В результате недавних успехов в развитии технологий стало проще консолидировать сложные рабочие нагрузки базы данных с применением виртуализации. В этой статье приведены рекомендации по использованию выполняющейся в памяти OLTP для SQL Server в виртуализированной среде.
Предварительное выделение памяти
В виртуальной среде важными факторами для памяти являются более высокая производительность и расширенная поддержка. Необходимо иметь возможность как быстро выделять память виртуальным машинам в зависимости от их требований (пиковые и низкие нагрузки), так и исключить бесполезные траты памяти. Компонент Hyper-V Dynamic Memory делает выделение памяти между виртуальными машинами, выполняемыми на узле, и управление ею более гибким.
Некоторые рекомендации по виртуализации и управлению SQL Server необходимо скорректировать при виртуализации базы данных с таблицами, оптимизированными для памяти. При отсутствии оптимизированных для памяти таблиц есть две рекомендации.
- При использовании параметра "Мин. памяти сервера" рекомендуется назначать только необходимое количество памяти, чтобы осталось достаточно памяти для других процессов (во избежание вытеснения).
- Не назначайте слишком высокого значения предварительного выделения памяти. В противном случае другие процессы могут не получить достаточной памяти к тому времени, когда она им потребуется, а это приведет к подкачке памяти.
Если следовать указанным выше рекомендациям, когда в базе данных есть оптимизированные для памяти таблицы, может выясниться, что попытка восстановить базу данных приводит к остановке базы данных в состоянии "Ожидание восстановления", даже если доступно достаточно памяти для восстановления базы данных. Причина в том, что при запуске выполняющаяся в памяти OLTP передает данные в память активнее, чем механизм выделения динамической памяти выделяет память базе данных.
Решение
Чтобы смягчить этот эффект, заранее выделите достаточную память, чтобы восстановить или перезапустить базу данных, а не минимальное значение, в расчете на то, что динамическая память выделит дополнительную память при необходимости.
Какой хороший способ проверить, сколько (фактической) памяти в настоящее время используется, и сколько SQL Server выделил себе?
Я прибегал к процентному соотношению memory_utilization_ , но, похоже, это не изменилось после выполнения следующего для освобождения памяти.
Решение состоит в том, чтобы уменьшить максимальный объем памяти сервера для SQL Server и снова увеличить его, чтобы заставить SQL Server освободить неиспользуемую, но выделенную память. Однако проблема с этим подходом заключается в том, что мы не можем быть уверены, насколько уменьшить максимальную память сервера , поэтому есть риск убить SQL Server. Вот почему важно понять, сколько SQL Server «фактически» использует, прежде чем уменьшать значение максимальной памяти сервера .
У меня нет решения, как освободить выделенную память. Однако для наших целей мы смогли выяснить, как обеспечить безопасную работу активно-активных кластеров. Мы решили установить минимальную память сервера на
2 ГБ. Это полезно, потому что независимо от того, какой максимальный объем памяти решит использовать экземпляр, он никогда не будет запускать другие экземпляры из памяти. Итак, опять же, это решает нашу задачу, но все еще не дает ответа на вопрос о том, сколько памяти фактически используется, насколько мало мы можем сбросить максимальную память сервера и т. Д.
Я не думаю, что SQL Server освобождает память, если операционная система не запрашивает ее. Если есть случай, когда другие процессы запрашивают дополнительную память, и если ее нет вообще, SQL Server освободит неиспользуемую память самостоятельно. Вместо того, чтобы пытаться очистить неиспользуемую память, я бы, вероятно, ограничил максимально допустимую память SQL.
SQL Server всегда предполагает, что это основное запущенное приложение. Он не предназначен для совместного использования ресурсов. Он всегда будет занимать всю доступную память и будет освобождать ее только для операционной системы, если вы не ограничите "максимальную память сервера".
По замыслу, Sql Server плохо взаимодействует с другими.
В этой статье sqlskills рекомендуется использовать базовый уровень для регулирования с последующим мониторингом и повышением скорости по мере необходимости:
Измененный сценарий ниже работал у меня. Мне нужно было временно освободить кучу ОЗУ, удерживаемую SQLServer, чтобы мы могли запускать некоторые другие разовые процессы на том же сервере. Он временно освобождает зарезервированное пространство памяти SQL, но при этом позволяет ему использовать резервную копию памяти по мере необходимости.
Я добавил встроенное ожидание, чтобы SQLServer действительно освободил память, прежде чем вернуть ее к исходному уровню. Очевидно, отрегулируйте значения по мере необходимости в соответствии с вашими потребностями.
На всякий случай, если у вас возникла чрезвычайная ситуация и у вас может быть небольшой простой, просто перезапустите службу SQL. Всего несколько секунд, чтобы перезапустить и сделать работу очень хорошо. Щелкните правой кнопкой мыши имя своего сервера и выберите «Перезагрузить».
Вы должны установить для параметра «Максимальный объем памяти сервера» некоторое значение от 1-2 ГБ. Этот диапазон безопасен в большинстве случаев. Для освобождения памяти может потребоваться время после выполнения нижеприведенного:
Этот параметр позволяет очистить пул, скомпилировать память, все кеши, память clr и т. Д.
Минимальное значение для «максимальной памяти сервера» составляет 128 МБ, но это не рекомендуется, так как SQL Server может не запускаться в определенных конфигурациях. Если это произойдет, используйте переключатель «-f» для принудительного запуска SQL с минимальной конфигурацией, а затем измените значение на исходное.
Читайте также: