Обзор компонентов управления памятью в SQL Server

Содержание

1 Общие настройки
2 Настройки базы данных
3 Результат

Материал посвящен описанию использования подсистемы памяти в MS SQL server. Данный обзор дает только общее представление о структуре управления. Следует помнить, что продукты компании Microsoft поставляются с закрытыми кодами и детальные сведения отсутствуют в общедоступных источниках (насколько удалось выяснить нам, если Вам удалось большее – сообщите, пожалуйста). Общий обзор необходим для понимания описываемых далее возможных проблем SQL server и используемых средств тестирования и измерения производительности.

Memory manager

Memory manager (ММ) является основным элементом, который управляет распределением памяти в SQL сервере. Данный компонент автоматически распределяет доступную SQL серверу память, снижая необходимость ручной настройки. После загрузки SQL ММ определяет первоначальный объем распределенной памяти и далее по мере изменения нагрузки динамически резервирует или освобождает оперативную память. Таким образом, ММ управляет взаимодействием SQL сервера с операционной системы в контексте управления памятью. Memory manager является частью SQLOS. Подробнее можно посмотреть здесь.

Состав Memory Manager

Сведения о составе этого компонента весьма ограничены, однако можно выделить следующие составные части ММ: memory nodes, memory clerks (клерки памяти), memory caches (кэши) и memory objects (объекты). Подробнее можно прочитать здесь и здесь. Также ММ предоставляет несколько счетчиков, которые позволяют оценить использование оперативной памяти в SQL. Подробнее можно прочитать здесь и здесь. Обобщенно состав ММ представлен на Рисунке 1.1 Рисунок 1.1 Компоненты Memory Manager Объекты ММ используются для распределения памяти внутри экземпляра SQL Server. Memory nodes обеспечивают интерфейс к ОС и реализацию выделения памяти на нижнем уровне. Внутри SQL Server только Memory clerks имеют доступ к интерфейсу. Memory nodes для распределения памяти. Каждый компонент SQL Sever, который потребляет существенный объем памяти должен создать собственный клерк и распределять память именно через его интерфейс. Реализация управлению памятью меняется от версии к версии SQL, однако основные функциональные компоненты сохраняются. На рисунке 2.2 приведены отличия в реализации ММ в SQL2008 и SQL2012 дополнительные сведения можно найти здесь. Рисунок 1.2 Изменения в структуре Memory Manager для SQL2008 и SQL 2012 Из рисунков видно, что полностью исчезло разделение page allocator. Эти компоненты были заменены одним Any-size page allocator.

Memory Nodes

Memory Nodes является внутренним объектом SQLOS. Представляет собой логический объект памяти, которая соответствует процессору в случае SMP-реализации или группе процессоров в случае NUMA-реализации. Подробнее можно посмотреть здесь. Рисунок 1.3 Иерархия SQLOS в случае реализации SMP (А — рисунок) и NUMA (Б- рисунок) Memory node абсолютно прозрачна для потребителей памяти. Главная задача этого компонента состоит в определении области выделения памяти. Memory node состоит из нескольких распределителей памяти (memory allocators). На рисунке 2.4 представлены потребители памяти, использующие memory node. Подробнее можно посмотреть здесь и здесь. Рисунок 1.4 Memory nodes Memory allocators являются процедурами, которые определяют тип Windows API используемой для выделения памяти. Аллокаторы содержат программный код используемый для выделения памяти, например, для страниц или использования shared memory.

Memory clerks

Memory nodes обеспечивают интерфейс к ОС и реализацию выделения памяти на уровне Windows. Внутри SQL Server только Memory clerks имеют доступ к интерфейсу Memory nodes для распределения памяти. Каждый компонент SQL Sever, который потребляет существенный объем памяти, должен создать собственный клерк и далее распределять ресурсы именно через его интерфейс. Таким образом, клерки выполняют следующие функции в рамках Memory manager: • Отражают использование памяти конкретными компонентами сервера • Получают уведомления о смене состояний памяти и изменяют её размер согласно обстоятельствам. • Используют Memory nodes для выделения памяти компонентам сервера. Выделяют четыре категории клерков. Список категорий приведен в таблице 1. Подробнее можно посмотреть здесь.

Memory Caches

Под понятием «кэш» понимается механизм кэширования различных типов данных с учетом стоимости хранения объектов. Кэш обеспечивает: контроль времени хранения, видимость и анализ статистики обращения к кэшированным данным. Кэшированные данные могут быть использованы одновременно несколькими потребителями. Кроме кэшей SQL Server использует пулы памяти. Пулы в отличии от кэшей используются для хранения однородных данных без дополнительного контроля. Используется несколько механизмов кэширования и них основные: • Cache Store • User Store • Object Store Только Object Store является пулом, Cache и User Store являются кэшами. Механизмы Cache и User Store весьма похожи, однако если параметры Cache Store контролируются целиком SQLOS, то для User Store разработчики могут использовать собственные алгоритмы управления. В документации также используются понятия Cache Store и User Store со значением “обособленные области памяти”. Каждому Cache Store сопоставлено хранилище hash table. Возможно использование не одной, а нескольких таблиц hash tables. Hash table – это структура в памяти, которая содержит массив указателей на страницы буфера. Хэширование – это методика, которая единообразно отображает значение ключа в соответствующий hash bucket. Подробнее можно прочитать здесь (про хэш) и здесь и здесь и здесь. Cache Store используются, например, для хранения кэша планов выполнения, кэша xml, кэша полнотекстового поиска, Procedure Cache, System Rowset Cache. В User Store хранятся в частности кэш метаданных пользовательских и системных баз, токены безопасности, данные схем. Полный список можно найти в динамическом представлении dm_os_memory_cache_counters.

Buffer pool (buffer cache) а также buffer pool extension

Buffer pool (второе название buffer cache) – это область в памяти, которая используется для кэширования страниц, данных таблиц и их индексов, размер страниц 8Кб. Использования Buffer pool уменьшает ввод/вывод в файл базы данных и таким образом увеличивает производительность сервера. При этом Buffer Cach является основным потребителем памяти в SQL Server. Рисунок 1.5 Компоненты системы управления буфером В SQL Server 2014 buffer pool может быть расширен в энергонезависимую память, например, на диск SSD. Такое расширение называется Buffer Pool Extension. Подробнее можно прочитать здесь здесь. Подробнее об управлении буферным кэшем можно прочитать здесь, здесь и здесь. Буферный кэш имеет собственный клерк памяти и распределят память через page allocator. Для буферного кэша используется клерк памяти типа Generic и называется memoryclerk_sqlbufferpool. До SQL 2012 Buffer Cache использовал только Single Page Allocator (распределяющий отдельные страницы 8Кб). Если компоненту сервера было необходимо выделить буфер большего, чем 8Кб размера использовался Multi Page Allocator (см. рис. 2.4) и соответственно эта память располагалась за пределами Buffer Pool. C SQL2012 Single и Multi Page allocators были объединены в Any-size page allocator. На рис. 2.2 можно увидеть эти изменения.

Max server memory и min server memory

Хотя управление буферным кэшем происходит автоматически внутри SQL Server, однако администраторы могут регулировать максимальный и минимальный размер распределяемой памяти для этого буфера. Рисунок 1.6 Изменения в диапазоне памяти резервируемой параметром Max server memory Как уже упоминалось в SQL 2012 произошли изменения memory manager. В результате таких изменений параметр max server memory регулирует не только память buffer pool, но вообще всё распределение памяти (кроме Direct Memory Allocations производимых с помощью VirtualAlloc). Параметр min server memory обозначает границу, ниже которой Buffer Pool не будет по требованию освобождать занятую память. При первоначальной загрузке пул не занимает память, указанную в min server memory. Используется минимально необходимый объем, который вычисляется автоматически. Размер пула при необходимости в дальнейшем увеличивается. Подробнее можно прочитать здесь и здесь.

Stolen pages

Stolen pages — это страницы буферного кэша, которые используются для различных целей в сервере. Например, для процедурного кэша, операций сортировки (т.е. рабочей памяти запроса — workplace memory). Также эти страницы необходимы для хранения таких структур данных, которые требуют распределение памяти менее 8Кб, например, блокировки, контекст транзакций и информации о соединении. Подробнее можно посмотреть в следующих источниках:simple-talk.com;technet.microsoft.com;sqlserver-dba.com.

Object Store

Object Store представляет собой пул памяти. Он используется для хранения однородных типов данных без дополнительного контроля стоимости хранения. Эти данные могут быть легко очищены в случае нехватки памяти. По своей структуре пулы являются клерками памяти (т.е. являются одним из его видов). Дополнительно можно посмотреть здесь и здесь.

Настройка регулярного резервного копирования БД MS SQL Server

Memory Objects (MO)

Memory Objects представляют собой кучу памяти, которая использует интерфейс клерков памяти чтобы получить доступ к page allocator для выделения страниц. Memory Objects не используют интерфейсы виртуальной или общей памяти, этот элемент использует только механизм распределения страниц. Многие компоненты SQL Server обращаются напрямую к MO, минуя клерки памяти. МО предоставляют возможность распределить диапазоны памяти произвольного размера. Более подробно:http://blogs.msdn.com/b/slavao/archive/2005/02/11/371063.aspx;http://support.microsoft.com/kb/907877/en-us;http://msdn.microsoft.com/ru-ru/library/ms179875.aspx.

Memory Broker (МВ)

Memory broker (брокер памяти) является компонентом SQLOS. Брокер памяти отвечает за распределение памяти между различными компонентами SQL Server в соответствии с их запросами. Более подробно можно прочитать на сайте производителя. Рисунок 1.7 Распределение памяти Memory Broker Описание механизма: МВ отслеживает запросы памяти от компонентов SQL и сопоставляет с текущими показатели её использования. Основываясь на полученной информации, брокер вычисляет «оптимальный» размер памяти, которая может быть распределена между компонентами. Брокер уведомляет компоненты о своих вычислениях, после этого каждый компонент использует эти сведения для дальнейшего использования памяти. МВ можно увидеть в следующих динамических представлениях “sys.dm_os_ring_buffers”, где Ring_buffer_type = “RING_BUFFER_MEMORY_BROKER”. Подробнее можно прочитать здесь, здесь и здесь.

Resource semaphore

Resource Semaphore отвечают за выделение памяти компоненту и сохранение общего размера используемой памяти в пределах установленного лимита. Более подробно можно посмотреть на blogs.msdn.com и mssqltips.com.

В данной статье будет рассмотрена закладка «Память» (Memory) окна настройки параметров MS SQL Server 2012 (справедливо и для MS SQL Server 2008 (R2)). На данной закладке можно указать лимиты использования памяти SQL Server 2012, а также задать размер памяти для запроса и для создания индекса.

Запускаем утилиту «SQL Server Management Studio». В Microsoft Windows server 2012 R2 ее можно найти в списке всех программ.

В Microsoft Windows Server 2008 R2 в меню «Пуск» (Start) — «Microsoft SQL Server 2012» — «Среда SQL Server Management Studio».

Вводим имя экземпляра сервера, данные для авторизации и нажимаем «Соединить» (Connect).

Резервное копирование и восстановление базы данных в MS SQL Server

В окне справа, в обозревателе объектов, кликаем правой кнопкой мыши по верхнему узлу дерева, содержащую строку соединения с сервером, и в контекстном меню выбираем «Свойства» (Properties).

В открывшемся окне «Свойства сервера» (Server Properties) переходим на вкладку «Память» (Memory), на которой доступны для изменения следующие параметры:

Минимальный размер памяти сервера (Minimum server memory) — Указывает минимальный размер оперативной памяти в мегабайтах, который будет выделен при запуске SQL Server и не будет в дальнейшем уменьшаться. Следует задавать такое значение, чтобы SQL Server не мешал работе операционной системы и программ, работающих на данном компьютере.
Максимальный размер памяти сервера (Maximum server memory) — Максимальный объем оперативной памяти в мегабайтах, который может использовать SQL Server во время работы. Устанавливать данный параметр имеет смысл если известно, что одновременно с SQL Server будет запущено несколько других приложений, которые используют весь объем памяти, доступный в момент запуска и не запрашивают ее дополнительно (т. е. не запрашивают память при необходимости). В этом случае необходимо установить такое значение максимального размера памяти сервера, при котором обеспечивается необходимый для приложения объем памяти, не занимаемый SQL Server. Минимальный объем памяти, который можно установить для данного параметра равен 64 мегабайтам для 32-разрядных систем и 128 МБ для 64-разрядных систем.
Память для создания индекса (Index creation memory) — Устанавливает объем памяти в килобайтах, который используется во время операции сортировки при создании индекса. Значение по умолчанию, равное 0, соответствует динамическому распределению, которое можно использовать в большинстве случаев без дальнейшей настройки. Значения задаются в диапазоне от 704 до 2 147 483 647.
Минимальный объем памяти для запроса (Minimum memory per query) — Устанавливает объем памяти в килобайтах, выделяемый для выполнения запроса. Возможно задать значение в диапазоне от 512 до 2 147 483 647 килобайт. Значение по умолчанию — 1024.

Введя необходимые параметры, нажимаем «ОК» для сохранения изменений.

Для того, чтобы просмотреть, вступили ли в силу внесенные изменения, заново откроем окно «Свойства сервера» (Server Properties) на вкладке «Память» (Memory) и установим переключатель, располагающийся внизу формы, в значение «Текущие значения» (Running values).

Если текущие значения остались прежними, значит требуется перезапуск экземпляра SQL server.

Бэкап файловых и SQL баз 1С (в облако и с шифрованием)

Помогла ли Вам данная статья?

Общие настройки

Запускаем SQL Server Management Studio и вводим данные для подключения к серверу баз данных.

Кликаем правой кнопкой мыши по серверу и выбираем Свойства:

В открывшемся окне переходим на вкладку «Память» и ограничиваем потребление оперативной памяти в графе «Максимальный размер памяти сервера (МБ)»:

* максимальный размер рассчитывается так: вся оперативная память сервера минус 4096 Мб (на нужды системы) минус 1536 * количество процессов rphost. Например, если в сервере установлено 32 Гб оперативной памяти и присутствует 2 процесса rphost, расчет будет таким: 32768 — 4096 — (2 * 1536) = 25600.

Теперь переходим на вкладку «Процессоры» и выставляем «Максимальное число рабочих потоков» в значение 2048 и ставим галочку Поддерживать приоритет SQL Server:

Настройки базы данных

В SQL Server Management Studio раскрываем «Базы данных», кликаем правой кнопкой мыши по рабочей базе и нажимаем Свойства:

Теперь переходим на вкладку «Файлы» и в настройках Авторасширения настраиваем расширение файла базы до 250 Мб и лога до 100. Также не лишним будет ограничить размер лога до 4096 Мб: