Автор: Dimitri Furman, MICROSOFT, 19 мая 2025г. SQL Server 2025: introducing tempdb space resource governance
С начала существования SQL
Server администраторам баз данных приходилось сталкиваться с распространённой
проблемой — нехваткой места в базе данных tempdb.
Мне всегда казалось странным, что всё, что мне нужно для сбоя в работе экземпляра SQL Server, — это доступ к серверу, на котором я могу создать временную таблицу, заполняющую всю tempdb, и никто не сможет меня остановить.
- Erland Sommarskog, независимый консультант по SQL Server и Data Platform MVP
Поскольку tempdb используется
сервером для большого числа разных задач, проблема может возникнуть не только
из-за действий пользователей, таких как создание временной таблицы. Например,
выполнение запроса для отчета, который материализует данные в tempdb, может
привести к сбою всех активных на этом экземпляре SQL Server процессов.
На протяжении многих лет
администраторы баз данных разрабатывали собственные решения, которые
отслеживали использование места в tempdb и предпринимали соответствующие
ситуации действия, например, завершали сеансы, которые сильно утилизировали
место в этой системной базе. Но это всегда требовало дополнительных
знаний, опыта, усилий и было сложным делом.
За свою карьеру я потратил больше времени, чем поддаётся подсчёту, на создание решений для управления местом в tempdb. Даже несмотря на то, что я потратил много времени и сил, всё равно возникали проблемы и сложности, особенно в многопользовательских приложениях с большим количеством баз данных и с проблемой «шумных соседей».
- Edward Pollack, работает в Transfinder архитектором данных и является Data Platform MVP
Новое решение в SQL Server 2025
Начиная с выпуска SQL Server
2025 CTP 2.0, вы можете использовать регулятор ресурсов (Resource Governor -
знакомый всем функционал, доступный с SQL Server 2008) для ограничения использования
места в tempdb, который будет делать это на уровне ядра сервера баз данных.
Resource Governor уже зарекомендовал себя с лучшей стороны, позволяя изолировать разные нагрузки экземпляра SQL Server, контролируя использование процессоров и памяти. Это помогает гарантировать предсказуемую производительность и стабильность работы ядра, что бывает особенно важно, например, для торговых систем, причём, даже тогда, когда другие системы используют тот же сервер.
- Ola Hallengren, главный инженер по платформам данных в Saxo Bank и Data Platform MVP
Аналогичным образом, если на сервере выполняется несколько разных
нагрузок, каждая из них может иметь собственный лимит в tempdb, заведомо меньший,
чем максимально доступное там пространство. Таким образом, даже если одна из
нагрузок достигнет своего лимита, другие продолжат выполняться.
Ниже показан сценарий, который ограничивает запросами занимаемое
место в tempdb в группе рабочих нагрузок по умолчанию до 17 ГБ с помощью всего
двух операторов T-SQL:
ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 17408);
ALTER RESOURCE GOVERNOR RECONFIGURE;
Группа по умолчанию используется для всех запросов, которые
не относятся к другим группам рабочих нагрузок. Вы можете создавать такие группы
для отдельных приложений, пользователей и т. д. и устанавливать ограничения для
каждой группы.
Если запрос попытается занять место в базе данных tempdb
сверх установленного ограничения для его группы рабочей нагрузки, он будет прерван
с сообщением бо ошибке 1138, с уровнем серьезности 17:
Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'workload-group-name'.
Все остальные запросы на сервере продолжат выполняться.
Задание ограничений
Резонный вопрос: Как установить правильные лимиты для разных
рабочих нагрузок сервера?
Тут можно не гадать. Использование места в tempdb отслеживается во времени для
каждой группы рабочих нагрузок и отображается в sys.dm_resource_governor_workload_groups. Причём это происходит даже если никакие
ограничения не установлены. Вы можете определить репрезентативные модели
использования для каждой рабочей нагрузки в течении времени, а затем установить
правильные ограничения. Потом можно эти настройки ограничений откорректировать,
если поведение рабочих нагрузок изменится.
Например, запрос ниже позволяет увидеть текущее
использование места в tempdb, пики его использования и количество запросов,
которые были прерваны из-за превышения лимита для каждой группы рабочей
нагрузки:
SELECT group_id,
name,
tempdb_data_space_kb,
peak_tempdb_data_space_kb,
total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups;
В этом динамическом административном представлении добавлены колонки tempdb_data_space_kb, peak_tempdb_data_space_kb и total_tempdb_data_limit_violation_count. Пиковая нагрузка и количество прерываний запросов (из-за нарушений ограничений) отслеживается с момента перезапуска сервера. В любое время можно сбросить эти и другие статистические данные Resource Governor, чтобы начать отслеживание заново и обойтись без перезапуска сервера, выполнив:
ALTER RESOURCE GOVERNOR RESET STATISTICS;
А что насчет журнала транзакций?
Ограничения, которые можно установить
для каждой группы рабочих нагрузок, применяются только к используемому месту в
файлах данных tempdb. Однако,
возможна ситуация, когда крупная транзакция заполнит журнал транзакций tempdb, что может привсети к сбою в работе всего сервера.
Для предотвращения такой ситуации, в SQL Server 2025 можно включить функцию ускоренного восстановление базы данных tempdb (ADR), чтобы
воспользоваться преимуществами агрессивного усечения журнала и значительно
снизить вероятность нехватки там места. Дополнительные сведения см. в статье Улучшения ADR в SQL Server 2025.
Дополнительные сведения
Дополнительную информацию об
управлении ресурсами tempdb, включая примеры, рекомендации и подробности работы,
см. в разделе Управление ресурсами пространства TempDB в документации.
Если вы раньше не использовали Resource Governor, вот хорошая отправная точка: Учебное пособие: Примеры конфигурации регулятора ресурсов и передовые методы.
Комментариев нет:
Отправить комментарий