Оптимизация производительности обычно выполняется по следующим характеристикам
- Загрузка процессора
- Использование памяти
- Попадание в кэш
- Дисковая подсистема
- Оптимизация запросов
Для памяти действует правило чем больше тем лучше,. Когда ее начинает не хватать повод
проверить чем же она забита. Об этом подробнее ниже.
Управление памятью SQL Server.
Memory manager (ММ) является основным элементом, который управляет распределением памяти в SQL сервере. Данный компонент автоматически распределяет доступную SQL серверу память, снижая необходимость ручной настройки. После загрузки SQL ММ определяет первоначальный объем распределенной памяти и далее по мере изменения нагрузки динамически резервирует или освобождает оперативную память. Таким образом, ММ управляет взаимодействием SQL сервера с операционной системы в контексте управления памятью.
Основные комноненты:
1. Memory Clerks (Интерфейс, используемый потребителями для управления памятью, каждый потребитель имеет один или несколько клерков памяти ,используется для распределения, освобождения и мониторинга использования памяти, каждый клерк связан с брокером , sys.dm_os_memory_clerks)
2. Memory Objects (Кучи памяти, которые используют интерфейс клерков памяти чтобы получить доступ к page allocator для выделения страниц. Memory Objects не используют интерфейсы виртуальной или общей памяти, этот элемент использует только механизм распределения страниц. Многие компоненты SQL Server обращаются напрямую к MO, минуя клерки памяти. МО предоставляют возможность распределить диапазоны памяти произвольного размера., sys.dm_os_memory_objects)
3. Memory Nodes (Главная задача этого компонента состоит в определении области выделения памяти на узле NUMA,если памяти не хватает может быть выделена с соседнего узла , sys.dm_os_memory_nodes)
4. Memory Brokers (отслеживает запросы памяти от компонентов SQL и сопоставляет с текущими показатели её использования. Основываясь на полученной информации, брокер вычисляет «оптимальный» размер памяти, которая может быть распределена между компонентами. Брокер уведомляет компоненты о своих вычислениях, после этого каждый компонент использует эти сведения для дальнейшего использования памяти.)
5. Memory Pools (используется чтобы установить лимиты памяти, internal - механизмы SQLOS, Default –пул по умолчанию для всего остального).
Более подробно все давно описано на хабре
Max server memory и min server memory
Хотя управление буферным кэшем происходит автоматически внутри SQL Server, однако администраторы могут регулировать максимальный и минимальный размер распределяемой памяти для этого буфера.
Параметр min server memory обозначает границу, ниже которой Buffer Pool не будет по требованию освобождать занятую память. При первоначальной загрузке пул не занимает память, указанную в min server memory. Используется минимально необходимый объем, который вычисляется автоматически. Размер пула при необходимости в дальнейшем увеличивается.
EXEC sp_configure 'show advanced option', '1';
reconfigure;
EXEC sp_configure;
--Set the maximum amount of memory to 4096 MB:
USE master
EXEC sp_configure 'max server memory (MB)', 4096
RECONFIGURE WITH OVERRIDE
--Display the newly set configuration:
USE master
EXEC sp_configure 'max server memory (MB)'
--Set 'show advanced options' back to default:
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
DBCC MEMORYSTATUS
--Memory broker (брокер памяти) является компонентом SQLOS. Брокер памяти отвечает за распределение памяти между различными компонентами SQL Server в соответствии с их запросами.
SELECT p.name AS resource_governor_pool_name, b.memory_broker_type
, b.allocations_kb AS current_memory_allocated_kb
, b.allocations_kb_per_sec AS allocation_rate_in_kb_per_sec
, b.future_allocations_kb AS near_future_allocations_kb
, b.target_allocations_kb
, b.last_notification AS last_memory_notification
FROM sys.dm_os_memory_brokers b
INNER JOIN sys.resource_governor_resource_pools p ON p.pool_id = b.pool_id
--Выделение памяти windows просиходит через клерки
SELECT type, name, memory_node_id
, sum(pages_kb
+ virtual_memory_reserved_kb
+ virtual_memory_committed_kb
+ awe_allocated_kb
+ shared_memory_reserved_kb
+ shared_memory_committed_kb) AS TotalKB
FROM sys.dm_os_memory_clerks
GROUP BY type, name, memory_node_id
ORDER BY TotalKB DESC
Buffer pool – это область в памяти, которая используется для кэширования страниц, данных таблиц и их индексов, размер страниц 8Кб. Использования Buffer pool уменьшает ввод/вывод в файл базы данных и таким образом увеличивает производительность сервера и является основным потребителем памяти в SQL Server.
Checkpoint – буферный пул не очищает
Lazywriter активизируется когда свободно меньше 25% буферного пула
-- Содержимое буферного пула
SELECT obj.name AS TableName, ind.name AS IndexName, part.object_id AS ObjectID, part.index_id AS IndexID
, part.partition_number AS PartitionNumber, buf.page_level AS IndexLevel
, alloc.type_desc AS AllocationType, buf.page_type AS PageType, buf.page_id AS PageNumber
FROM sys.dm_os_buffer_descriptors buf
INNER JOIN sys.allocation_units alloc ON alloc.allocation_unit_id = buf.allocation_unit_id
INNER JOIN sys.partitions part ON part.hobt_id = alloc.container_id
INNER JOIN sys.indexes ind ON ind.object_id = part.object_id AND ind.index_id = part.index_id
INNER JOIN sys.objects obj ON obj.object_id = part.object_id
WHERE buf.database_id = db_id() AND alloc.type IN (1,3) AND obj.is_ms_shipped = 0
UNION ALL
SELECT obj.name AS TableName, ind.name AS IndexName, part.object_id AS ObjectID, part.index_id AS IndexID
, part.partition_number AS PartitionNumber, buf.page_level AS IndexLevel
, alloc.type_desc AS AllocationType, buf.page_type AS PageType, buf.page_id AS PageNumber
FROM sys.dm_os_buffer_descriptors buf
INNER JOIN sys.allocation_units alloc ON alloc.allocation_unit_id = buf.allocation_unit_id
INNER JOIN sys.partitions part ON part.partition_id = alloc.container_id
INNER JOIN sys.indexes ind ON ind.object_id = part.object_id AND ind.index_id = part.index_id
INNER JOIN sys.objects obj ON obj.object_id = part.object_id
WHERE buf.database_id = db_id() AND alloc.type = 2 AND obj.is_ms_shipped = 0
ORDER BY TableName, IndexID, PageNumber
Счетчики в performance monitor
SELECT *
FROM sys.[dm_os_performance_counters]
where (counter_name like '%Buffer cache hit ratio%'
OR counter_name LIKE 'Lazy Write%')
В SQL Server 2014 buffer pool может быть расширен в энергонезависимую память, например, на диск SSD. Такое расширение называется Buffer Pool Extension. Подробнее можно прочитать здесь.
