Проверка распределений памяти за 45 секунд: от симптомов к первопричине

Автор: Luca Biondi, Check Memory Grants in 45 Seconds. From Symptoms to Root Clause. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 6

Распределения памяти (memory grants) — это молчаливые убийцы конкурентности в SQL Server. В этой статье я покажу вам, как выявить запросы, пожирающие память, и устранить катастрофические ожидания типа RESOURCE_SEMAPHORE всего за 45 секунд — до того, как ваш сервер полностью остановится!

В двух словах

• ✔️ Распределения памяти предназначены для сортировки и хэширования: Это рабочая область оперативной памяти (workspace RAM), полностью отделённая от буферного пула, который кэширует страницы данных. 🛠️
• ✔️ Ожидания RESOURCE_SEMAPHORE: Главный красный флаг, указывающий на то, что запросы выстраиваются в очередь, испытывая голод по исполнительной памяти. 💣
• ✔️ Выявление виновников: Используйте sys.dm_exec_query_memory_grants, чтобы мгновенно обнаружить запросы, требующие непомерно больших, неоправданных объёмов оперативной памяти. 🧪
• ✔️ Устранение первопричины: В 99% случаев раздувание памяти вызвано устаревшей статистикой или плохим индексированием, а не недостатком физической оперативной памяти на сервере. ✔️

Сталкивались ли вы когда-нибудь со сценарием, когда ваш процессор практически простаивает, дисковый ввод-вывод находится в норме, но пользовательские приложения повсеместно завершаются по тайм-ауту? Добро пожаловать в ужасное узкое место RESOURCE_SEMAPHORE. Это происходит, когда ваш экземпляр исчерпывает рабочую область памяти для выполнения запросов. Давайте разберём, как диагностировать и исправлять проблемы с распределением памяти — от симптомов до первопричин — менее чем за минуту!

Что это на самом деле: рабочая область памяти (Workspace Memory)

Когда план выполнения запроса включает такие операции, как ORDER BY (сортировка) или HASH JOIN, SQL Server не может обработать их «на лету». Он должен зарезервировать определённый блок оперативной памяти — называемый распределением памяти (Memory Grant) — прежде чем запросу будет разрешено начать выполнение.

Если плохо оптимизированный запрос требует 20 ГБ оперативной памяти, а на вашем сервере есть только 10 ГБ доступной рабочей памяти... этот запрос ждёт. Но не только он: ситуация ещё хуже, потому что ждёт и каждый запрос, стоящий за ним.

• 💣 Симптом: Внезапный всплеск ожиданий RESOURCE_SEMAPHORE в вашей статистике ожиданий или в активных сессиях.
• ✔️ Заблуждение: «Нам нужно больше оперативной памяти». Нет, вам нужны более качественные оценки кардинальности.

🧪 Диагностический скрипт за 45 секунд

Выполните этот T-SQL немедленно, когда заподозрите нагрузку на память. Он покажет, кто именно запрашивает оперативную память, у кого она есть и кто ждёт:

SELECT 
    session_id,
    request_id,
    request_time,
    grant_time,
    requested_memory_kb / 1024 AS Requested_MB,
    granted_memory_kb / 1024 AS Granted_MB,
    used_memory_kb / 1024 AS Used_MB,
    ideal_memory_kb / 1024 AS Ideal_MB,
    query_cost,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

Как интерпретировать результаты: Обратите внимание на разрыв между Granted_MB (выделено) и Used_MB (использовано). Если запросу выделено 5000 МБ, а использует он всего 10 МБ – он агрессивно удерживает память, которая ему не нужна, лишая её остальную часть сервера.

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

По моему опыту, добавление физической оперативной памяти для решения проблемы RESOURCE_SEMAPHORE подобно переливанию воды в ведро с дырой. Это просто купит вам несколько часов. Первопричина почти всегда кроется в плохих оценках кардинальности (Bad Cardinality Estimates).

Если статистика SQL Server говорит оптимизатору, что ожидается 10 миллионов строк, он запросит огромное распределение памяти для их сортировки. Если же запрос в действительности возвращает всего 50 строк, вся запрошенная память оказывается захваченной и полностью потраченной впустую.

1. Исправьте статистику: Первый шаг — всегда UPDATE STATISTICS YourTable WITH FULLSCAN; для таблиц, участвующих в самых проблемных запросах.

2. Устраните сортировки: Если запрос сортирует огромные наборы данных, создайте покрывающий индекс (covering index) с правильным порядком ключей. Индекс естественным образом предварительно упорядочивает данные, полностью устраняя необходимость в распределении памяти.

3. Экстренный жгут (Emergency Tourniquet): Если вы не можете немедленно изменить код или индексы, используйте подсказку запроса MAX_GRANT_PERCENT, чтобы ограничить «взбесившийся» запрос:

   OPTION (MAX_GRANT_PERCENT = 5)