Это экземпляр SQL Server 2016 CU2 с 15 ГБ, настроенными как максимальное использование ОЗУ. MDOP равен 4. Базы данных в этом экземпляре очень редко запрашиваются пользователями, и это вторичная реплика только для чтения.
Проблема: еженедельно все запросы переходят в состояние RESOURCE_SEMAPHORE из-за исчерпания памяти, даже если на этом сервере одновременно не выполняется никаких других запросов. После перезапуска экземпляра запросы снова начинают работать. На изображениях ниже вы заметите, что нам не хватает Maximum Workspace Memory, и это действительно ОЧЕНЬ плохие запросы (астрономические затраты на запросы).
Перед перезапуском: доступно только 1,8 ГБ максимальной рабочей памяти и ВСЕ неверные запросы переходят в состояние RESOURCE_SEMAPHORE.
После перезапуска экземпляра: достаточно памяти рабочей области (~11 ГБ) и больше нет RESOURCE_SEMAPHORE, даже для ужасных запросов.
Как вы видите, это отвратительный запрос, который запрашивает ~2 ГБ оперативной памяти. На этом изображении SSMS показывает, что требуемая память была фактически предоставлена, поскольку экземпляр был перезапущен. Перед перезапуском предоставление памяти оставалось на NULL, а запрос оставался в состоянии ожидания RESOURCE_SEMAPHORE.
Теперь нам хотелось бы узнать: почему максимальный объем памяти рабочей области со временем уменьшается и не освобождается, когда нет запущенных запросов?
решение1
Похоже, что из общего объема выделенной памяти в 15 728 640 000 байт украденная серверная память занимает 12 828 864 000 байт, что явно недостаточно для нормальной работы.
В документации говорится, что SQL Server, объект диспетчера памяти:
Украденная память сервера (КБ)
Указывает объем памяти, который сервер использует для целей, отличных от страниц базы данных.
Анотвечатьговорит больше:
Украденная память описывает буферы, которые используются для сортировки или для операций хеширования (память рабочей области запросов), или для тех буферов, которые используются как общее хранилище памяти для выделений для хранения внутренних структур данных, таких как блокировки, контекст транзакции и информация о подключении. Процессу lazywriter не разрешено сбрасывать украденные буферы из пула буферов.
Расточительный запрос, по-видимому, выполнил огромную операцию сортировки, и SQL Server затем не освобождает выделенную память. Память берется из буферного пула и не может быть использована ни для чего другого.
У вас не так уж много вариантов:
- Улучшите этот запрос, чтобы он не сортировал огромные объемы данных.
- Увеличьте память SQL Server
- Периодически перезапускайте SQL Server, особенно после выполнения этого запроса.