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