TempDB — одна из моих неизменных головных болей.
Любой, любой, кто может выполнять запросы на вашем сервере, способен за считанные секунды устроить отказ в обслуживании, просто заполнив TempDB простейшим запросом:
DROP TABLE IF EXISTS #big_problem;
CREATE TABLE #big_problem
(filler VARCHAR(8000));
WHILE 1 = 1
INSERT INTO #big_problem
SELECT REPLICATE('X', 8000)
FROM GENERATE_SERIES(1, 100000);
Этот цикл постепенно заполнит TempDB, и когда одна из попыток в итоге завершится ошибкой, это не беда: сессия останется открытой. Она продолжит занимать всё остальное пространство, мешая другим пользователям (и системным задачам) пользоваться используемыми ресурсами.
Вам уж точно не стоит запускать такое в свой последний рабочий день, выходя за дверь: даже если ваш логин отключат, уже запущенные запросы продолжат выполняться, пока не завершатся, или, как в этом случае, пока не нанесут «добивающий удар» по вашему хранилищу, и файлы TempDB не начнут раздуваться, как брюки Sansabelt, пытающиеся справиться с шведским столом «ешь сколько сможешь». И уж точно не стоит гонять это в цикле. (Если всё же решитесь, убедитесь, что в начале скрипта удаляете таблицу, если она существует).
Если вы ещё не на SQL Server 2025, ваша основная линия обороны — заранее задать файлам данных TempDB нужный максимальный размер и отключить авторасширение. Это... слабая защита. Плохо ведущие себя запросы всё равно могут выгрызть TempDB до нуля, создавая проблемы для остальных.
На SQL Server 2025 Enterprise применяйте Resource Governor
Наконец у нас появился способ защититься. Можно настроить Resource Governor, чтобы делить пользователей на группы (похоже, это нынче модно) и ограничивать, сколько пространства TempDB каждая группа может потреблять. И вовсе не обязательно делить на группы (обратите внимание, политики): можно просто ограничить ресурсы, которыми пользуются все, в целом.
Чтобы упростить пример, допустим, мы ограничиваем всех одновременно. Можно задать фиксированный предел:
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = 10240);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
Или долю от общего объёма TempDB:
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 75);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
Странно, но можно настроить оба ограничения одновременно — об этом через минуту. Выполните этот запрос, чтобы посмотреть, какие лимиты заданы, сколько пространства используется сейчас, каков был пиковый расход и сколько раз запросы были убиты из‑за превышения лимитов TempDB:
SELECT r.group_id, r.name,
r.group_max_tempdb_data_mb,
r.group_max_tempdb_data_percent,
dm.tempdb_data_space_kb,
dm.peak_tempdb_data_space_kb,
dm.total_tempdb_data_limit_violation_count
FROM sys.resource_governor_workload_groups r
INNER JOIN sys.dm_resource_governor_workload_groups dm
ON r.group_id = dm.group_id;
Результаты:
И как это выглядит для ваших пользователей, когда они выбирают свой лимит:
Вместо того чтобы провоцировать системную проблему — полное отсутствие места в TempDB, теперь это просто... хотел написать «локальная проблема уровня запроса», но это не совсем так. Запрос по‑прежнему удерживает весь объём TempDB, доступный всему пулу нагрузок по умолчанию, а это не годится. Чтобы сделать правильно, придётся учесть куда больше «подводных камней», чем поместится в один пост.
Подводные камни (и их много)
Самый хитрый: лимиты начинают работать только если конфигурация авторасширения файлов TempDB согласована с ограничениями Resource Governor. Документация об этом многословна, но если коротко: если вы ограничиваете только процент (а не точный объём в МБ), то процентное ограничение действует, только если:
- Авторасширение выключено у всех файлов данных TempDB, а максимальный размер — «без ограничений», или
- Авторасширение включено у всех файлов данных TempDB, и задан максимальный размер файла.
То есть либо всё, либо ничего. По крайней мере, Resource Governor предупреждает, если вы пытаетесь включить его при несовместимой конфигурации файлов TempDB, но если вы потом поменяете настройки TempDB, предупреждения уже не будет — вы можете и не заметить, что ваши правки «сломали» ограничения Resource Governor.
Честно говоря, не до конца понимаю, почему так. Если авторасширение отключено, то максимальный размер уже неважен: рост завершён, финита, конец. В SSMS вы даже не сможете задать максимальный размер при отключённом авторасширении: он не имеет смысла.
А если авторасширение включено, мне не хочется задавать максимальный размер: я хочу, чтобы ОС увеличивала файлы до доступного в тот момент пространства. Понимаю, почему это сложно для расчёта: процессор запросов должен вычислить ограничение и применить его до того, как движок попытается (и не сможет) увеличить файл данных.
Эти правила выглядят так, будто кто‑то сделал максимум возможного, имея ограниченные ресурсы, стараясь не сломать другие части движка, чтобы выпустить функцию в срок. Окей, я с этим могу смириться. Это выглядит, как разумный компромисс, но он требует, чтобы вы знали об этих ограничениях, иначе вы будете думать, что функция включена, хотя это не так (именно так у меня и случалось на тестах раз за разом: я не понимал, почему процентные ограничения не срабатывают, отсюда и новая проверка в sp_Blitz).
Ещё один камень преткновения: чтобы это действительно помогало, нужно настроить Resource Governor так, чтобы запросы распределялись по разным группам нагрузок. Если всех оставить в группе по умолчанию, любой единичный запрос всё равно может оставить остальных без места.
И, наконец, чтобы лимиты не уронили сам SQL Server, нужно учитывать потребление ресурсов другими потребителями TempDB, такими как хранилище версий и триггеры. Это далеко выходит за рамки этой статьи, но если хотите разобраться, загляните в мой курс «Фундаментальные основы TempDB» — в нём недавно появился новый модуль ровно по этой теме.
Комментариев нет:
Отправить комментарий