Анализ использования оперативной памяти SQL Server

Практическое занятие № 17

Мониторинг работы сервера

 

Рассмотрим, как в SQL Server быстро проанализировать использование памяти, CPU, дисков и наличие блокировок.

Анализ использования оперативной памяти SQL Server

1. Для начала нужно определить сколько памяти доступно SQL Server. Для этого запустите SSMS (SQL Server Management Studio), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).

Сам по себе доступный объём RAM вам ничего не скажет. Нужно сравнить это число с используемой памятью в Диспетчере Задач и самим движком SQL Server с помощью DMV.

2. В Диспетчере задач, во вкладке Подробности, найдите sqlservr.exe и посмотрите сколько оперативной памяти использует этот процесс.

• Если на сервере, например, 128 GB оперативной памяти, а процесс sqlservr.exe использует 60 GB и ограничений по RAM у SQL Server нет, то оперативной памяти вам хватает.
• Если SQL Server использует 80-90% RAM от заданной или максимальной, то в таком случае нужно смотреть DMV. Имейте в виду, что sqlservr.exe не сможет использовать всю оперативную память. Если на сервере 128 GB, то sqlservr.exe может использовать только 80-90% (100-110 GB), так как остальная память резервируется для операционной системы.

· Имейте в виду, что процесс SQL Server’a не отдаёт оперативную память обратно в систему. Например, ваш SQL Server обычно использует 20 GB памяти, но при месячном отчете он увеличивает потребление до 100 GB, и даже когда вычисление отчета закончится и сервер будет работать в прежнем режиме, процесс SQL Server’a всё равно будет использовать 100 GB до перезагрузки службы.

· Даже если вы уверены, что оперативной памяти серверу хватает, не будет лишним точно знать объём потребляемой RAM.

· Узнать реальное использование RAM можно с помощью Dynamic Management Views. DMV это административные вьюверы (представления). С помощью DMV можно диагностировать практически любую проблему в SQL Server.

· Посмотрим sys.dm_os_sys_memory, для удобства используем запрос:

SELECT total_physical_memory_kb / 1024 AS

 [Total Physical Memory],

available_physical_memory_kb / 1024 AS

[Available Physical Memory],

total_page_file_kb / 1024 AS

[Total Page File (MB)],

available_page_file_kb / 1024 AS

[Available Page File (MB)],

100 - (100 * Cast(available_physical_memory_kb AS DECIMAL(18, 3)) / Cast

(

total_physical_memory_kb AS DECIMAL(18, 3))) AS

'Percentage Used',

system_memory_state_desc AS

[Memory State]

FROM sys.dm_os_sys_memory;

Рассмотрим каждый выводимый параметр:

1. [ Total Physical Memory ] – объём оперативной памяти доступный в операционной системе. На некоторых серверах может показывать немного больше реально установленной.

2. [ Available Physical Memory ] – объём оперативной памяти доступный для SQL Server, без учета уже захваченной SQL Server.

3. [ Total Page File (MB)] – Объём “Сommit limit”. Commit Limit = Оперативная память + все файлы подкачки. То есть, если у вас на сервере 32 GB оперативной памяти и 16 GB файл подкачки, commit limit будет 48 GB.

4. [ Available Page File (MB) ] – Объём файла подкачки.

5. Percentage Used – процент занятой оперативной памяти. Такого параметра нет в самом sys.dm_os_sys_memory, но он считается по формуле available_physical_memory_kb / total_physical_memory_kb

6. [ Memory State ] – Состояние RAM. Поле system_memory_state_desc содержит в себе состояние потребления оперативной памяти в виде текста. Значение этого поля считается исходя из других двух: system_low_memory_signal_state и system_high_memory_signal_state. Вы можете выбирать их напрямую, если вам нужен Boolean/bit формат данных. Для ознакомления со всеми полями sys.dm_os_sys_memory ознакомьтесь с документацией

Все эти данные полезны, если вы хотите точно определить сколько ваш SQL Server потребляет RAM. Чаще всего это используют, если есть подозрения что для экземпляра выделено слишком много оперативной памяти.

Если Вам нужно убедиться, что серверу хватает RAM, вы можете смотреть только на поля

system_low_memory_signal_state,

system_high_memory_signal_state и

system_memory_state_desc.

Если system_low_memory_signal_state = 1, то серверу явно не хватает оперативной памяти.