Диагностика выделения памяти запросам SQL Server

Отчет может быстро выполняться отдельно и создавать очередь при одновременном запуске несколькими пользователями. Одна из возможных причин связана с рабочей памятью для сортировки и хеширования. Оптимизация времени одной изолированной попытки может скрыть объем удерживаемого резерва и ожидание остальных запросов.

Снять данные во время проблемы

Наблюдайте систему до завершения всех отчетов. Следующий запрос показывает активные заявки на память вместе с текущими запросами. Он ничего не изменяет, но требует соответствующих диагностических прав. Для этой DMV в SQL Server 2022 и новее используется VIEW SERVER PERFORMANCE STATE, в более ранних версиях обычно требуется VIEW SERVER STATE.

SELECT
    mg.session_id,
    mg.request_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

Текст относится ко всему пакету. В процедуре могут присутствовать другие инструкции, не связанные с исследуемым выделением. Для уточнения используйте смещения текущей инструкции или план. Сохраняйте идентификаторы сессии и запроса вместе: позднее та же сессия может выполнять уже другую работу.

Отсутствующий grant_time означает, что заявка еще ожидает выделения. Сопоставьте запрошенные и выделенные килобайты с текущим и максимальным наблюдаемым использованием. Это живые показатели, а не завершенная история. Сразу после старта запрос закономерно может использовать небольшую часть резерва. Сохраните несколько снимков с временем и свяжите их с периодом инцидента.

RESOURCE_SEMAPHORE относится к ожиданию рабочей памяти выполнения. RESOURCE_SEMAPHORE_QUERY_COMPILE связан с компиляцией. Исследуйте и запросы, уже получившие большие резервы. Ожидающий запрос может быть пострадавшим, тогда как место удерживает другой отчет.

Разделить разные механизмы

Избыточное выделение резервирует значительно больше необходимого. Недостаточное может заставить сортировку или хеширование сбрасывать промежуточные данные в tempdb. Очередь возможна и при разумных отдельных резервах, если слишком много крупных операций пересекаются во времени. Общий процент занятой памяти не различает эти ситуации.

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

При сбросе данных на диск сравните оценочное и реальное число входных строк конкретного оператора. Недооцененный результат join может резко увеличить вход для хеширования. Лишние столбцы могут сделать дорогим даже умеренное количество строк. Уменьшать проекцию перед сортировкой полезно лишь при сохранении смысла запроса.

Индекс с нужным порядком иногда позволяет убрать сортировку. Предварительная агрегация на правильном уровне уменьшает вход join. Но индекс увеличивает стоимость записи, а ошибочная агрегация способна выдавать правдоподобные неверные суммы. Проверка результата должна предшествовать оценке экономии памяти.

Проверить реальную одновременную нагрузку

Запустите характерную смесь отчетов параллельно с рабочими наборами параметров. Измеряйте завершенные запросы в минуту и высокие перцентили задержки, а не только отдельное время. Меньший резерв может улучшить допуск к выполнению, но дополнительная работа tempdb способна ухудшить общую пропускную способность.

Memory Grant Feedback может корректировать выделение между выполнениями в поддерживаемых версиях и режимах. Поведение и сохранение результатов зависят от версии и конфигурации. Наблюдайте эффект, не считая его гарантией для первого выполнения, нового плана или любого распределения параметров.

Не начинайте с массового применения подсказок памяти или общего увеличения серверного лимита. Сначала установите причину: оценки, ширина строк, порядок или чрезмерное пересечение операций. Разнести крупные выгрузки во времени иногда полезнее, чем уменьшать резерв каждому запросу.

Для итоговой проверки используйте сопоставимое окно нагрузки: меньше ожидающих заявок, допустимые сбросы, стабильная производительность и правильные результаты. Сохраните планы и время снимков. Отдельно зафиксируйте количество одновременных отчетов: без него сравнение легко приписывает оптимизации эффект более спокойного дня. Такая запись помогает понять, уменьшили ли вы потребность, исправили оценку или лишь перенесли ожидание на другой ресурс.

Техническая документация: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.