Мониторинг и выявление проблем с памятью MS SQL Server
Пост находится в стадии разработки, т.е. информация будет корректироваться, будет добавляться новая инфа и исправляться ошибки.
Главный счетчик для анализа загрузки оперативной памяти с точки зрения операционной системы — это счетчик Обмен страниц в сек (Pages/sec) объекта Память (Memory). Физически этот счетчик показывает количество обращений в секунду к файлу подкачки (неважно, на чтение или запись)!!!!
Полная информация по текущему положению дел на сервере с памятью – состояние всех буферов, пулов и общий объем физической и виртуальной памяти, использование AWE памяти, расширяющей только лишь буферный пул (выделение и реальное использование) может быть доступна при выполнении всего лишь одной команды.
Полная информация по текущему положению дел на сервере с памятью – состояние всех буферов, пулов и общий объем физической и виртуальной памяти, использование AWE памяти, расширяющей только лишь буферный пул (выделение и реальное использование) может быть доступна при выполнении всего лишь одной команды.
DBCC MEMORYSTATUS
Результат выглядит примерно так:
Результат выглядит примерно так:
Здесь можно увидеть распределение памяти между различными кэшами сервера. Наиболее важный и часто встречаемый - буферный пул. В разделе показателей клерка MEMORYCLERK_SQLBUFFERPOOL можно увидеть значения зарезервированной памяти, закоммиченной и объем используемой AWE памяти.
Также эти данные можно увидеть в системном отчете SSMS - Memory consumption.
На которой:
В первой колонке и третьей колонке - используемая память для буферного пула
Вторая колонка показывает объем заразервированной виртуальной памяти для БП
и Четвертая колонка - объем использованной AWE памяти
В верхней части отчета можно увидеть диагруппу, показывающую разделение памяти среди наиболее тяжелых клерков. В таблице выше они отсортированны по первому столбцу. В диаграмме это выглядит так:
Показатель потребляемой памяти в скобочках после названия каждого клерка это сумма 1 и 3 столбцов.
Данная информация доступна при использовании динамических административных представлений (DMV)
sys.dm_os_memory_clerks
sys.dm_os_memory_objects
Пример использования первой DMV показывает состояние всех клерков памяти на данный момент на сервере (информация аналогичная с таблицей из отчета)
SELECT name, type,
SUM(single_pages_kb + multi_pages_kb) AS MemoryKB
FROM sys.dm_os_memory_clerks
GROUP BY name, type
ORDER BY MemoryKB desc
Объем оперативной памяти выделенный по механизму AWE можно получить с помощью следующего запроса
SELECT SUM(awe_allocated_kb) FROM sys.dm_os_memory_clerks
Клеркам соответствуют различные буферы памяти предназначенные SQL server-у для различных задач.
Скрипт возвращает количество страниц и объем памяти в буферном пуле выделенных для каждой из БД на сервере. Здесь можно просмотреть какая из баз наиболее прожорливая. Как правило это зависит от размера базы, но не всегда.
use master
go
SELECT count(*)AS cached_pages_count, count(*)/128 'Size (Mb)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC
Go
Следующий пример возвращает количество страниц в кэше, загруженных для каждого объекта в текущей базе данных. Подобная детализация дает возможность вычислить проблемные объекты БД.
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;
или этот скрипт, показывающий сожержание буфферного пула
select
count(*)as cached_pages_count,
obj.name as objectname,
ind.name as indexname,
obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
inner join
(
select object_id as objectid,
object_name(object_id) as name,
index_id,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.hobt_id
and (au.type = 1 or au.type = 3)
union all
select object_id as objectid,
object_name(object_id) as name,
index_id,allocation_unit_id
from sys.allocation_units as au
inner join sys.partitions as p
on au.container_id = p.partition_id
and au.type = 2
) as obj
on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
on obj.objectid = ind.object_id
and obj.index_id = ind.index_id
where bd.database_id = db_id()
and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc
Как бороться?
1. Перезагрузка – если это возможно
2. DBCC FREESYSTEMCACHE ('ALL','default'); - удаляет все неиспользуемые элементы из всех кэше
3. Или очищаем непосредственно конкретный буфер (как параметр передаем низвание клерка)
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
DBCC FREESYSTEMCACHE ('SQL Plans')
Также информацию по этой теме можно почитать в статье
