How do I limit MS SQL Server memory usage?
I’ve heard MS SQL Server takes up as much RAM as it can to cache results. Well, it’s not leaving enough bargaining room for our little server’s RAM.
How do I change the settings to limit the amount of RAM it can use?
MS SQL Server running on Windows Server 2008.
2 Answers 2
- In Object Explorer, right-click a server and select Properties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
You can also do it in T-SQL using the following commands (example):
To Restrict MS SQL Service memory consumption:
Set «max server memory» in SQL Server Management Studio
Now we will set the «max server memory» option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing «Properties».
In Object Explorer, right-click a server and select Properties. 
Click the Memory node as shown below:
4. Under Server Memory Options, enter the amount that you want for Maximum server memory. Below as you can see we are setting up max server memory to 4096 MB (i.e. 4 GB).
To Restrict MS SQL Service memory consumption:
We can set «max server memory» also by using a T-SQL script:
Setting a fixed amount of memory for SQL Server
There is a frequently asked question among many people who work with SQL Server: «Why does SQL Server use almost all of the memory on the server, even if there is essentially no activity on the databases?» This is a common and logical question if you are not familiar with SQL Server memory management and you have not investigated the question. This tip is intended to explain the default SQL Server memory configurations and how to configure SQL Server to use a fixed amount of RAM.
Solution
When a SQL Server instance is running on your machine, you may find that memory usage is perceived as too high. Commonly, most of the memory is used by SQL Server. Moreover, when you increase memory on the machine, but the database load remains the same, it is possible that even after the memory upgrade SQL Server will be the top consumer of memory. The reason is that by default SQL Server dynamically allocates memory during its activity and does not release it until there is a request from Windows.
It is normal for SQL Server, because it tries to keep as much memory as possible for performance purposes. It reserves memory and caches data into memory to decrease access to disk drives and therefore increasing performance. When other processes on the same server require memory, SQL Server releases the needed memory. So, SQL Server can use almost all available memory on the server.
If your server is a dedicated database server there is no problem regarding memory allocation, however sometimes there are many applications running on the database server. Moreover, some applications use memory available at their starting time and may be unable to request additional memory from Windows, if needed. In this case, to permit the other applications to work properly, we can limit SQL Server’s access to memory by setting the «max server memory» option. By default it is set to 2147483647 MB, which allows SQL Server to use approximately all of the server’s memory. We can change this setting to a lower value, according to our requirements. It could be done either by SQL Server Management Studio or a T-SQL script.
Setting «max server memory» using SQL Server Management Studio
At a very high level, let’s monitor memory usage on our machine where SQL Server is running, then set «max server memory» in SQL Server Management Studio (SSMS) and view the changes in memory usage. In our example, the test server has 1 GB of Memory (RAM) and SQL Server 2014 is installed, but not in a running state. Also, there are no other resources consuming memory on our server. By running Task Manager we can monitor memory usage in the Performance tab to get a sense of the memory usage prior to starting SQL Server:
Now we will start SQL Server and monitor memory usage again:
We can see that the amount of used memory has increased:
And the top memory «consumer» is SQL Server:
In SSMS we can run the following query and find the memory used by SQL Server in megabytes:
In our server it is 117 MB:
Now let’s do some activity in our instance. We have TestDB database on our instance and TableA in it, which contains 300,000 rows. Let’s select all rows from this table:
When the query finishes we will check memory usage again in SQL Server:
In this case we can see that 432 MB is used by SQL Server, however there are no active queries on our instance:
And 93% of our server memory is used (mostly by SQL Server):
Set «max server memory» in SQL Server Management Studio
Now we will set the «max server memory» option to limit the memory usage by SQL Server. We can do this by right-clicking on our instance and choosing «Properties»:
After than choose the «Memory» page:
We can see above that «Maximum server memory» is set to 2147483647 MB. This is the default value. We will change it to 200 MB and click «OK»:
By running this query again we can see that memory used by SQL Server decreased to 244 MB:
A question arises: why 244 MB, when we set «maximum server memory» for SQL Server to 200 MB? The reason is that our query returns currently used memory, however max server memory controls memory used by the buffer pool, compiled memory, all cache and so on, but it does not control memory for linked server providers other than SQL Server, memory allocated by a non SQL Server DLL, memory for thread stacks, memory heaps, etc. and therefore the result of our query can be a bit more than the value of «max server memory».
Set «max server memory» in SQL Server using T-SQL
We can set «max server memory» also by using a T-SQL script:
This script sets «max server memory» to its default value — 2147483647 MB:
We can check in SSMS that «max server memory» has changed:
Recommendations on setting «max server memory»
Microsoft recommends to allow SQL Server to use memory dynamically, however in some situations it is preferable to limit the memory usage by SQL Server. For example, when our server is not a dedicated database server and there are other applications running on the same server which are unable to request memory from Windows, it would be better if we estimate the maximum memory which can be used by SQL Server and set «max server memory» appropriately.
Also, when there are more than one SQL Server instances running on your server, we can set «max server memory» for each of them, considering their load, to control memory usage among these instances. Note that we should consider that the sum of «max server memory» values for all instances will be less than the total physical memory in our server. This ensures that free memory will be available immediately for instances after start up. However if one of instances is not running, the running instances will be unable to use the remaining free memory.
Conclusion
As we can see, SQL Server tends to use all available memory on a server. However in some cases, when we need to limit memory used by SQL Server or properly distribute memory among SQL Server instances we can set the «max server memory» option for each instance. One most important thing which we should consider before managing memory for SQL Server is to correctly estimate the maximum amount of memory which will be needed for each instance.
Настройка памяти в 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 он же на русском “Коэффициент обращений к буферному кэшу”
После еще раз “Далее”
“Сохранить и закрыть” затем “Готово”
После создания счетчика включаем его, чтоб он собрал данные. (Пары минут его работы будет достаточно, так как мы установили по умолчанию интервал сбора каждые 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С >>>
Немножко всего .. из жизни администратора ms sql server
Память :
Графически
Выбираем свойства сервера, вкладка “ Memory ”, в поле « Maximum server memory » устанавливаем необходимое значение памяти , которые выделяем MS SQL Server -у:
EXEC sys . sp_configure N’max server memory (MB)’ , N’9000′
GO
RECONFIGURE WITH OVERRIDE
Вкладка Processors, снимаем галочку у параметра «Automatically set processor affinity for all processors”, отмечаем необходимые процессоры :
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 3
GO
Теперь у нас есть сконфигурированный инстанс MS SQL Server , которому выделено 9 Гб памяти и отдано 4 процессора сервера.
Параметры конфигурации службы сохраняются в файле msmdsrv. ini в каталоге установки службы директории OLAP\Config. Перед изменением рекомендуется сохранить отдельно данный файл, хоть и перед рестартом службы копия файла сохраняется с расширением msmdsrv. bak
Второй параметр нас мало интересует, это параметр определяет минимальное количество памяти при запуска службы. А вот параметр Memoty \ TotalMemoryLimit определяет максимальное количество памяти, которое может использовать служба MS SQL Server Analysis services .Значение в процентах от общего количества памяти на сервере. К примеру, значение 20 на сервере с 32 Гб памяти, определяет для службы MS SQL Server Analysis 6,4 Гб памяти.
Номера процессоров 76543210 — 00110000 , где 1 процессор который будем использовать
Значение 00110000 переводим в 16-е значение= 0х30
Файл rsreportserver .config
Наиболее важные параметры :
Этот параметр не появляется в файле конфигурации RSReportServer.config, если не добавить его вручную. Чтобы сервер отчетов использовал меньше памяти, можно изменить файл RSReportServer.config, добавив элемент и значение. Диапазон допустимых значений — от 0 до максимального целого числа. Значение указывается в килобайтах.
WorkingSetMinimum — Нижний предел потребления ресурсов; сервер отчетов не будет освобождать память, если общее использование памяти ниже этого предела.
Этот параметр не появляется в файле конфигурации RSReportServer.config, если не добавить его вручную. Чтобы изменить это значение, необходимо добавить элемент WorkingSetMinimum в файл RSReportServer.config. Диапазон допустимых значений — от 0 до максимального целого числа. Значение указывается в килобайтах
< MemorySafetyMargin > 80 </ MemorySafetyMargin > — значения оп умолчанию
< MemoryThreshold > 90 </ MemoryThreshold > -значение по умолчанию
< WorkingSetMaximum > 512000 </ WorkingSetMaximum > — добавлено, определено 512 Мб максимальный объем памяти для службы MS SQL Server Reporting Services
< WorkingSetMinimum > 256000 </ WorkingSetMinimum > — добавлено, определен нижний предел памяти, установлено 256 мб.