Мониторинг и выявление проблем с памятью MS SQL SERVER

Мониторинг и выявление проблем с памятью MS SQL Server

Пост находится в стадии разработки, т.е. информация будет корректироваться, будет добавляться новая инфа и исправляться ошибки.

Главный счетчик для анализа загрузки оперативной памяти с точки зрения операционной системы — это счетчик Обмен страниц в сек (Pages/sec) объекта Память (Memory). Физически этот счетчик показывает количество обращений в секунду к файлу подкачки (неважно, на чтение или запись)!!!! 

Полная информация по текущему положению дел на сервере с памятью – состояние всех буферов, пулов и общий объем физической и виртуальной памяти, использование 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')
 

Также информацию по этой теме можно почитать в статье