Проверка нагрузки на память за 45 секунд

Автор: Luca Biondi, Check Memory Pressure in 45 Seconds. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 4

В этой статье я покажу вам свой диагностический план из 5 шагов для выявления губительной нагрузки на память SQL Server менее чем за 45 секунд. Перестаньте гадать и начните точно определять, что именно лишает ваш буферный пул памяти, прежде чем производительность полностью деградирует!

В двух словах

• ✔️ Уровень ОС: Проверьте доступную физическую память – внешнее давление опасно 🛠️
• ✔️ PLE (Page Life Expectancy – ожидаемая продолжительность жизни страницы): Высокое значение – это хорошо, но внезапные падения означают активное перемещение данных в памяти (churn) 📉
• ✔️ Распределения памяти (Memory Grants): Долгие ожидания и огромные объёмы выделенной памяти – чистые убийцы параллелизма ⏳
• ✔️ Менеджеры памяти (Memory Clerks): Найдите, какой именно кэш (CACHESTORE, USERSTORE) ворует вашу оперативную память 🧠

Мы все знаем, как важно использовать каждую секунду при администрировании SQL Server. Нагрузка на память – это ещё один молчаливый убийца производительности базы данных. Потому что, когда на вашем сервере заканчивается оперативная память, он начинает активно использовать диск, ЦП взлетает до максимума, и пользователи начинают жаловаться на тайм-ауты.

Вам не нужны тяжёлые и дорогие инструменты мониторинга, чтобы анализировать нагрузку на память. Вам нужны только правильные динамические административные представления (DMV). Вот точный план действий.

⚡ Проверка за 45 секунд: что это на самом деле

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

1️⃣ Состояние буферного пула и системной памяти

Сначала нам нужно узнать, испытывает ли операционная система трудности или же голодает SQL Server.

SELECT 
    total_physical_memory_kb/1024 AS TotalMemoryMB,
    available_physical_memory_kb/1024 AS AvailableMemoryMB,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

• ✔️ Мало доступной памяти (Low available memory) = Медленные запросы.
• 💣 Внешнее давление (External pressure) – операционная система забирает память обратно у SQL Server. Это крайне опасно и обычно означает, что вы неверно настроили параметр Max Server Memory (максимальная память сервера). Ваш SQL Server вынужден сжиматься из-за сигналов ОС о нехватке памяти.

2️⃣ Ожидаемая продолжительность жизни страницы (Page Life Expectancy, PLE)

Как долго данные остаются в кэше, прежде чем будут вытеснены для освобождения места под новые данные?

SELECT cntr_value AS PageLifeExpectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy';

• ✔️ Высокое значение = Хорошо. Данные спокойно находятся в памяти.
• 💣 Внезапные падения = Серьёзное перемещение данных (churn). Что-то выметает ваш буферный пул.

3️⃣ Распределения памяти (Memory Grants)

Ожидают ли ваши запросы выделения рабочей области памяти для выполнения сортировок и хэш-операций?

SELECT 
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

• 💣 Долгие ожидания (Long waits) → Запросы остановлены в ожидании освобождения памяти.
• 💣 Огромные объёмы выделенной памяти (Huge grants) → Массовый убийца параллелизма. Несколько плохих запросов пожирают всю оперативную память.
• 💣 Маленькие объёмы выделенной памяти (Small grants) → Предупреждение: операции могут выполнять сброс на диск (spilling) в TempDB!

4️⃣ Менеджеры памяти (Memory Clerks)

Если память исчезает, кто именно её потребляет?

SELECT 
    type,
    SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY MemoryMB DESC;

• ✔️ Определите потребителя, чтобы точно увидеть, куда уходит ваша оперативная память.
• 💣 Ищите аномалии – если CACHESTORE_SQLCP (планы adhoc запросов) или CACHESTORE_OBJCP (планы хранимых процедур) потребляют непропорционально много памяти, вы имеете дело с раздуванием кэша планов (plan cache bloat), а не с проблемой данных.
• 👉 В частности, если CACHESTORE_SQLCP непропорционально велик, это сильный индикатор раздувания кэша adhoc-запросов, часто вызванного непараметризованными запросами.

5️⃣ Resource_Semaphore

Ожидания выделения памяти для выполнения запросов (queries waiting for execution memory)

SELECT 
    *
FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE';

• 💣 Если wait_time_ms для RESOURCE_SEMAPHORE продолжает увеличиваться, запросы ожидают распределения памяти, и ваша рабочая нагрузка испытывает нагрузку на память.

🚀 Моя рабочая стратегия

По опыту, никогда не рассматривайте эти метрики изолированно. Если вы видите падение PLE (шаг 2), немедленно проверьте распределения памяти (шаг 3). В 9 случаях из 10 массивный неоптимизированный запрос, выполняющий большое хэш-соединение (Hash Join), запрашивает огромное распределение памяти, вытесняя все ваши хорошие страницы данных из буферного пула.

Прежде чем бежать к команде инфраструктуры с мольбами о добавлении физической оперативной памяти, исправьте запрос! Добавление оперативной памяти на сервер с плохим индексированием подобно установке большего бензобака на машину с утечкой топлива. Оптимизируйте рабочие нагрузки, которые воруют вашу рабочую область памяти, и ваш PLE естественным образом восстановится.