Особенности ограничения памяти SQL Server и что изменилось в SQL Server 2016

Особенности ограничения памяти SQL Server и что изменилось в SQL Server 2016

Есть несколько вариантов ограничения памяти, некоторые настраиваем мы, а не которые устанавливает 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 способами:

  1. С помощью 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 нужное значение > ОК

memory

Ограничение памяти с помощью Регулятора Ресурсов

Так же у нас есть возможность опуститься ещё на 1 уровень ниже и ограничить память для конкретных подключений с помощью Регулятора Ресурсов (Resource Governor). В данном компоненте создаются пулы ресурсов, группы рабочих нагрузок  и функция распределения подключений по группам рабочих нагрузок (подробнее здесь). С помощью Resource Governor мы можем разделить ресурсы экземпляра SQL Server на группы и раздать эти группы, по определённой логике, разным пользователям. Данный функционал доступен только в Enterprise Edition.

Особенности определения реально используемой памяти SQL Server

Понять сколько использует памяти SQL Server можно с помощью диспетчера задач, но если у вас на сервере включена ‘блокировка страниц в памяти’, то данный способ не покажет реальной картины. В таком случае, чтобы посмотреть количество используемой памяти вам потребуется обратиться к счётчикам производительности или написать запрос к представлению sys.dm_os_performance_counters:

  1. Запрос будет выглядеть следующим образом
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 были добавлены следующие лимиты:

my_first_habra

Обратите внимание, что лимит для колоночных индексов в памяти указан для всего экземпляра, а для 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»

Добавить комментарий

Войти с помощью: