Как ограничить sql по оперативной памяти

Ограничение памяти SQL

1. На сервере только на Windows 10 в поиске набрать SQL Server и запустить от администратора.

2. Проверка подлинности указать «Проверка подлинности Windows» и указать имя сервера.

3. Правой кнопкой мыши нажать по имени сервера и выбрать «Свойства».

4. В меню «Память» максимальный размер должен быть не ниже 4096МБ, если оперативной памяти больше 4 ГБ.

• Оперативной памяти компьютера меньше 4ГБ — максимальный размер в SQL = 2048МБ.
• Оперативной памяти компьютера больше 4ГБ — максимальный размер в SQL можно выставить = 4096 или же 2147483647

Настройка памяти в MS SQL для 1С Предприятия

Несколько постов в нашей группе телеграмм послужили причиной для написания данной статьи.

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

«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).

Вот мы и подошли к еще одному Важному вопросу:

«Так сколько ОЗУ надо для счастливой» жизни «сиквелу» ) ?

В рамках данной статьи, попытаюсь дать ответ и на этот не простой вопрос, или как минимум указать верное направление)

Дело в том, что определяя потребление ОЗУ «сиквелом», мы в основном работаем только с «симптомами» и лишь сопоставив показания нескольких, можно предполагать, что проблема в нехватке ОЗУ или наоборот ОЗУ в избытке.

Помните в начале статьи мы говорили о «почти универсальном» способе навести порядок с чрезмерным потреблением ОЗУ» ?

Да, в большинстве случаев ограничение поможет, но не в 100% случаев, так как действует оно только на «буферный пул»!

Вот что по этому поводу пишет MS:

Сам SQL Server как процесс занимает больше памяти, чем указано в параметре max server memory. И внутренние, и внешние компоненты могут занимать память за пределами буферного пула, что также входит в ее общий расход, однако буферный пул обычно составляет наибольшую часть общего объема памяти, потребляемого SQL Server.

Известны случаи, когда MS SQL употребил ОЗУ, что значительно превышает объем всех баз 1С, и даже если указали max server memory ограничили потребление.

К сожалению, и такое бывает, особенно когда в конфигурации 1С есть «кривые запросы».

Что еще раз подтверждает факт отсутствия «универсальной таблетки».

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

К сожалению, другого более точного определения «мало или много ОЗУ» в MS SQL как по «симптомам» нет, так как не все зависит от него самого.

И так «симптомы»:

Симптом №1

«Buffer cache hit ratio» – какой процент страниц MS SQL прочитали из буферного пула.(кэша)

Расшифруем подробно, что это за показатель «Коэффициент обращений к буферному кэшу», как его посмотреть, конечно, разберем и его нюансы. (Они есть у всех «симптомов»).

И так идем на сервер, где у Вас установлен MS SQL .При его установке будет добавлен счётчик в Performance Monitor, собственно там и можно смотреть показатель.

Создадим новую группу сборщика (Можете использовать и существующей, если у Вас есть).

Запускаем команду «Perfmon» нажав комбинацию клавиш «WIN+R»

Далее создадим новую группу сборщиков данных. (Можно только добавить показатель если у Вас уже создан свой сборщик).

Имя указываем на выбор (Для теста пишу “MS SQL ОЗУ 1С”)

Выберем “Создать вручную” (для опытных) и клик по кнопке “Далее”

На следующем этапе укажем “Счетчик производительности” и “Далее”.

И добавим сам “счетчик”

Нам нужно найти: SQLServer:BufferManager (Развернем его).

И сам счетчик: Buffer cache hit ratio он же на русском “Коэффициент обращений к буферному кэшу”

Клик “Добавить >>” и “OK”

После еще раз “Далее”

“Сохранить и закрыть” затем “Готово”

После создания счетчика включаем его, чтоб он собрал данные. (Пары минут его работы будет достаточно, так как мы установили по умолчанию интервал сбора каждые 15 сек.).

ВАЖНО!

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

И не перезапускаем на протяжении дня MS SQL, иначе данные показателя будут обнулены!

Включить сбор достаточно просто, надо лишь выделить в списке наш “сборщик” и кликнув правой клавишей мышки – “Пуск”, также можно нажать на зеленый треугольник вверху (как на картинке ниже).

После пары минут работы “Сборщика” остановим его и будем смотреть результаты.

Для остановки, также выполняем простые действия как и выше, только на этот раз с кнопкой “Стоп”.

И идем смотреть отчеты…

Как видим значение нашего Buffer cache hit ratio равно 100%

Теперь подробно что это значит:

Buffer cache hit ratio – показывает процент страниц которые MS SQL считал из кэша (буферного пула).

Это значит, что обращений к диску в нашем случаи не было и все поместилось в кэш.

Если бы “сиквелу” не хватило ОЗУ, он бы сбрасывал страницы на диски и оттуда бы читал эти данные (процент BCHR был бы значительно ниже 100%) , но раз это не происходит, значит, у нас есть основания полагать, что ОЗУ MS SQL достаточно.

Принято считать “хорошим” показателем 90% и выше.

А теперь собственно, почему не стоит полагаться на один “симптом” Buffer cache hit ratio:

Проблема в том, что счетчик Buffer cache hit ratio легко “накрутить”, если скажем пользователь будет много раз формировать один и тот же отчет в 1С (с темы же данными), то мы получим высокий процент чтения из кэша, даже если ОЗУ уже не хватает MS SQL.

При этом если сформировать в таких условиях другой “большой” отчет, он уже может выполнятся долго, сколько бы раз мы его не формировали (Так как ОЗУ MS не хватило, данные берутся с диска, а Buffer cache hit ratio все еще покажет нам высокий процент).

Очень рекомендую почитать статью Джонатана Кехайяса.

И всегда считать Buffer cache hit ratio только одним из симптомов возможной проблемы с ОЗУ!

Симптом №2

Page Life Expectancy:

Читать далее.

Зарегистрируйтесь, чтоб продолжить чтение статьи
Зарегистрироваться / Войти

Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>

Оптимизация работы 1C. Настройка сервера MS SQL #16

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

Для включения настройки:

1. В меню Пуск выберите команду Выполнить. В поле Открыть введите gpedit.msc.
2. В консоли Редактор локальных групповых политик разверните узел Конфигурация компьютера, затем узел Конфигурация Windows.
3. Разверните узлы Настройки безопасности и Локальные политики.
4. Выберите папку Назначение прав пользователя.
5. Политики будут показаны на панели подробностей.
6. На этой панели дважды кликните параметр Блокировка страниц в памяти.
7. В диалоговом окне Параметр локальной безопасности — блокировка страниц в памяти выберите «Добавить» пользователя или группу.
8. В диалоговом окне Выбор: пользователи, учетные записи служб или группы добавьте ту учетную запись, под которой у вас запускается служба MS SQL Server.
9. Чтобы изменения вступили в силу, перезагрузите сервер или зайдите под тем пользователем, под которым у вас запускается MS SQL Server.

1. Отключите механизм DFSS для дисков.

Механизм Dynamic Fair Share Scheduling отвечает за балансировку и распределение аппаратных ресурсов между пользователями. Иногда его работа может негативно сказываться на производительности 1С. Чтобы отключить его только для дисков, нужно:

1. Найти в реестре ветку HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TSFairShare\Disk
2. Установить значение параметра EnableFairShare в 0

1. Отключите сжатие данных для каталогов, в которых лежат файлы базы.

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

Чтобы отключить сжатие файлов в каталоге, необходимо:

1. Открыть свойства каталога
2. На закладке Общие нажать кнопку Другие
3. Снять флаг «Сжимать» содержимое для экономии места на диске.

1. Установите параметр «Максимальная степень параллелизма» (Max degree of parallelism) в значение 1.

Данный параметр определяет, во сколько потоков может выполняться один запрос. По умолчанию параметр равен 0, это означает, что сервер сам подбирает число потоков. Для баз с характерной для 1С нагрузкой рекомендуется поставить данный параметр в значение 1, т.к. в большинстве случаев это положительно скажется на работе запросов.

Для настройки параметра необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Открыть свойства сервера и выбрать закладку Дополнительно
3. Установить значение параметра равное единице.

1. Ограничьте максимальный объем памяти сервера MS SQL Server.

Необходимо ограничить максимальный объем памяти, потребляемый MS SQL Server, особенно это критично, если роли сервера 1С и сервера СУБД совмещены. Максимальный объем памяти, рекомендуемый для MS SQL Server, можно рассчитать по следующей формуле:

Память для MS SQL Server = Память всего – Память для ОС – Память для сервера 1С

Например, на сервере установлено 64 ГБ оперативной памяти, необходимо понять, сколько памяти выделить серверу СУБД, чтобы хватило серверу 1С.

Для нормальной работы ОС в большинстве случаев более чем достаточно 4 ГБ, обычно – 2-3 ГБ.

Чтобы определить, сколько памяти требуется серверу 1С, необходимо посмотреть, сколько памяти занимают процессы кластера серверов в разгар рабочего дня. Этими процессами являются ragent, rmngr и rphost, подробно данные процессы рассматриваются в разделе, который посвящен кластеру серверов. Снимать данные нужно именно в период пиковой рабочей активности, когда в базе работает максимальное количество пользователей. Получив эти данные, необходимо прибавить к ним 1 ГБ – на случай запуска в 1С «тяжелых» операций.

Чтобы установить максимальный объем памяти, используемый MS SQL Server, необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Открыть свойства сервера и выбрать закладку Память
3. Указать значение параметра Максимальный размер памяти сервера.

1. Включите флаг «Поддерживать» приоритет SQL Server (Boost SQL Server priority).

Данный флаг позволяет повысить приоритет процесса MS SQL Server над другими процессами.

Имеет смысл включать флаг только в том случае, если на компьютере с сервером СУБД не установлен сервер 1С.

Для установки флага необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Открыть свойства сервера и выбрать закладку Процессоры
3. Включить флаг «Поддерживать приоритет SQL Server (Boost SQL Server priority)» и нажать Ок.

1. Установите размер авто увеличения файлов базы данных.

Автоувеличение позволяет указать величину, на которую будет увеличен размер файла базы данных, когда он будет заполнен. Если поставить слишком маленький размер авторасширения, тогда файл будет слишком часто расширяться, на что будет уходить время. Рекомендуется установить значение от 512 МБ до 5 ГБ.

Для установки размера авторасширения необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Открыть свойства нужной базы и выбрать закладку Файлы
3. Напротив каждого файла в колонке Автоувеличение поставить необходимое значение

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

1. Разнесите файлы данных mdf и файлы логов ldf на разные физические диски.

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

Для переноса файлов необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Открыть свойства нужной базы и выбрать закладку Файлы
3. Запомнить имена и расположение файлов
4. Отсоединить базу, выбрав через контекстное меню Задачи – Отсоединить
5. Поставить флаг Удалить соединения и нажать Ок
6. Открыть Проводник и переместить файл данных и файл журнала на нужные носители
7. В Management Studio открыть контекстное меню сервера и выбрать пункт Присоединить базу
8. Нажать кнопку Добавить и указать файл mdf с нового диска
9. В нижнем окне сведения о базе данных в строке с файлом лога нужно указать новый путь к файлу журнала транзакций и нажать Ок.

1. Вынесите файлы базы TempDB на отдельный диск.

Служебная база данных TempDB используется всеми базами сервера для хранения, промежуточных расчетов, временных таблиц, версий строк при использовании RCSI и многих других вещей. Обычно обращений к этой базе очень много, и если она будет лежать на медленных дисках, это может замедлить работу системы.

Рекомендуется хранить базу TempDB на отдельном диске для повышения производительности работы системы.

Для переноса базы TempDB на отдельный диск необходимо:

1. Запустить Management Studio и подключиться к нужному серверу
2. Создать окно запроса и выполнить скрипт:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘Новый_Диск:\Новый_Каталог\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘Новый_Диск:\Новый_Каталог\templog.ldf’)
GO

1. Включите Shared Memory, если сервер 1С расположен на том же компьютере, что и сервер СУБД.

Протокол Shared Memory позволит общаться приложениям через оперативную память, а не через протокол TCP/IP.

Для включения Shared Memory необходимо:

1. Запустить диспетчер конфигурации SQL Server.
2. Зайти в пункт SQL Native Client – Клиентские протоколы – Общая память – Включено
3. Поставить значение Да и нажать Ок.

Ограничить память в SQL Server 2016-2019

Важным пунктом настройки арендованного сервера с установленным SQL Server 2016 (отличии в версиях 2012, 2008 или даже 2019 здесь минимальны), является ограничение объема оперативной памяти занимаемым службой MSSQLSERVER в процессах операционной системы.

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

Как ограничить потребление оперативной памяти для MS SQL Server?

И так открываем Microsoft SQL Server Management Studio, заходим в систему под учетной записью с администраторским доступом и в главном окошке Object Explorer, нажимаем правой кнопкой мыши на корневой пункт — название нашей версии копии SQL сервера, а потом выбираем его Свойства — пункт Properties.

В открывшемся диалоговом окне, нас интересует пункт Memory в левом меню. Открыв данный пункт мы видим значение Maximum server memory, которое и заполняем соответственно объему оперативной памяти на нашем сервере и его количеству, которое хотим выделить для работы SQL сервера баз данных. В нашем примере это 81 Гб из 128 Гб доступных на арендованном физическом выделенном сервере 1с.

Как видите решить вопрос ограничения оперативной памяти выделенной MS SQL Server довольно просто.