Есть несколько вариантов ограничения памяти, некоторые настраиваем мы, а не которые устанавливает Microsoft.
Ограничение памяти редакцией SQL Server
Поговорим сначала о том, что изменить нам не представляется возможным, а именно ограничение памяти на редакцию SQL Server. От версии к версии эти пороги меняются, сегодня я хотел бы остановиться на SQL Server 2014, остальные версии можно посмотреть тут.
Имя функции | Enterprise | Business Intelligence | Standard Edition | Express |
Максимальный объем используемой памяти | MAX | 64 ГБ | 64 ГБ | 1 ГБ |
Как мы видим, для нагруженных решений, редакции Standard будет недостаточно, а Enterprise стоит значительно больше (в среднем в 4 раза больше если сравнивать лицензии на ядра).
Ограничение памяти экземпляру SQL Server
Теперь перейдём к ограничению на которое мы можем повлиять, а именно к параметру экземпляра max server memory. Данный параметр ограничивает память выделяемую для экземпляра, но охватывает не весь её объём. Max server memory ограничивает только баффер пул (далее buffer pool), если не вдаваться в детали, то buffer pool это область памяти SQL Server отвечающая за весь кэш страниц данных и индексов. Остальная часть кэша (процедурный, backup, для поддержки dll и тд) используется вне buffer pool. Память управляется клерками памяти, посмотреть все их виды и используемые ими ресурсы можно тут. Именно по этой причине вы могли видеть что экземпляр часто съедает больше памяти чем мы выставили в max server memory.
max server memory может быть изменён 2 способами:
- С помощью T-SQL команд
-- Сначала включаем возможность расширенного изменения конфигурации SQL Server sp_configure 'show advanced option', 1 RECONFIGURE GO -- Устанавливаем максимальное количество оперативной памяти для buffer pool в 2048 sp_configure 'max server memroy', 2048 RECONFIGURE
2. С помощью SQL Server Management Studion:
Правая кнопка мышки на сервере > Свойства > раздел Память > указать в разделе max server memory нужное значение > ОК
Ограничение памяти с помощью Регулятора Ресурсов
Так же у нас есть возможность опуститься ещё на 1 уровень ниже и ограничить память для конкретных подключений с помощью Регулятора Ресурсов (Resource Governor). В данном компоненте создаются пулы ресурсов, группы рабочих нагрузок и функция распределения подключений по группам рабочих нагрузок (подробнее здесь). С помощью Resource Governor мы можем разделить ресурсы экземпляра SQL Server на группы и раздать эти группы, по определённой логике, разным пользователям. Данный функционал доступен только в Enterprise Edition.
Особенности определения реально используемой памяти SQL Server
Понять сколько использует памяти SQL Server можно с помощью диспетчера задач, но если у вас на сервере включена ‘блокировка страниц в памяти’, то данный способ не покажет реальной картины. В таком случае, чтобы посмотреть количество используемой памяти вам потребуется обратиться к счётчикам производительности или написать запрос к представлению sys.dm_os_performance_counters:
- Запрос будет выглядеть следующим образом
SELECT cntr_value/1024 as memory FROM sys.dm_os_performance_counters WHERE counter_name like '%Total Server Memory%'
2. Счётчик производительности можно найти в Performance Monitor — SQL Server:Memory Manager\Total Server Memory (KB)
Новое поведение ограничения памяти в SQL Server 2016
Память под In-Memory OLTP и колоночные индексы в памяти выделяется не из buffer pool, а через другие клерки, по этой причине ограничение max server memory на данные компоненты не подействует. В SQL Server 2016 были добавлены следующие лимиты:
Обратите внимание, что лимит для колоночных индексов в памяти указан для всего экземпляра, а для In-Memory OLTP в рамках одной БД.
Как и в предыдущих версиях SQL Server нет никаких других ограничений по памяти кроме как для buffer pool (max server memory), In-Memory OLTP и колоночных индексов в памяти. Остальные клерки памяти так же могут использовать ресурсы сверх ожидаемых.
Пример: SQL Server Standard Edition имеет ограничение buffer pool в 128 Гб оперативной памяти (ограничение редакции), то есть для страниц данных и индексов максимальный лимит 128 Гб. Начиная с SQL Server 2016 Sp1 вы можете дополнительно использовать 32 Гб памяти для колоночных индексов в памяти и 32 Гб для In-Memory OLTP (для каждой БД), плюс память, используемая другими клерками.
Один комментарий на «Особенности ограничения памяти SQL Server и что изменилось в SQL Server 2016»