SQL Server 2025 представляет новую возможность Resource Governor для управления использованием tempdb, а также делает Resource Governor доступным в Standard Edition.
Мне стало интересно: может ли новая функция tempdb в Resource Governor помочь сдержать запросы, которые не используют временные таблицы, но вызывают массовое переполнение данных в tempdb? В документации говорится «да», но я всегда предпочитаю получить практический опыт, когда это возможно.
У меня есть ужасный запрос, который «переливается через край», как автомат с мягким мороженым, объявивший войну. Давайте протестируем новые функции управления tempdb в SQL Server 2025.
Знакомьтесь с нашим тестовым запросом, чья сортировка вызывает переполнение на терабайт данных
Я использую базу данных StackOverflow2013 с запросом, специально созданным для переполнения. Запрос получает распределение памяти в 15 ГБ из-за комбинации оконных функций, вычисляющих агрегаты по большим секциям, и нескольких LEFT JOIN, создающих дублирующиеся строки для оконных вычислений.
Этого распределения памяти в 15 ГБ совершенно недостаточно для объёма данных, поступающих в оператор сортировки в плане запроса, и переполнение памяти на диск легко заполняет 1 ТБ свободного места на моём ноутбуке, если его не ограничить.
Сначала этот скрипт настраивает параметры базы данных и индексы, используемые запросом:
USE StackOverflow2013;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;
GO
EXECUTE dbo.DropIndexes;
GO
CREATE NONCLUSTERED INDEX
ix_Posts_PostTypeId_ETC
ON dbo.Posts
([PostTypeId])
INCLUDE ([AcceptedAnswerId],[AnswerCount],[CommentCount],[CreationDate],[LastActivityDate],[OwnerUserId],[Score],[Tags],[Title],[ViewCount])
WITH
(
DATA_COMPRESSION = PAGE
);
GO
CREATE NONCLUSTERED INDEX
ix_Comments_PostId
ON dbo.Comments
(PostId)
INCLUDE
(
CreationDate,
Score
)
WITH
(
DATA_COMPRESSION = PAGE
);
GO
А этот скрипт представляет сам запрос, вызывающий переполнение:
/* Этот запрос ни на что особо не годен, кроме как для переполнения tempdb. */
USE StackOverflow2013;
GO
SELECT TOP (100)
QuestionId = q.Id,
QuestionTitle = q.Title,
QuestionCreationDate = q.CreationDate,
QuestionScore = q.Score,
QuestionViews = q.ViewCount,
QuestionAnswerCount = q.AnswerCount,
QuestionCommentCount = q.CommentCount,
QuestionTags = q.Tags,
QuestionLastActivityDate = q.LastActivityDate,
QuestionOwnerId = uq.Id,
QuestionOwnerName = uq.DisplayName,
QuestionOwnerReputation = uq.Reputation,
QuestionOwnerLocation = uq.Location,
QuestionOwnerCreationDate = uq.CreationDate,
AcceptedAnswerId = a.Id,
AcceptedAnswerScore = a.Score,
AcceptedAnswerCreationDate = a.CreationDate,
AcceptedAnswerOwnerId = ua.Id,
AcceptedAnswerOwnerName = ua.DisplayName,
AcceptedAnswerOwnerReputation = ua.Reputation,
TotalUpVotes =
SUM
(
CASE
WHEN v.VoteTypeId = 2
THEN 1
ELSE 0
END
) OVER
(
PARTITION BY
q.Id
),
TotalFavorites =
SUM
(
CASE
WHEN v.VoteTypeId = 5
THEN 1
ELSE 0
END
) OVER
(
PARTITION BY
q.Id
),
RecentCommentCount =
COUNT_BIG
(
CASE
WHEN c.CreationDate >= '2012-01-01'
THEN 1
END
) OVER
(
PARTITION BY
q.Id
),
QuestionOwnerBadgeCount =
COUNT_BIG(b.Id) OVER
(
PARTITION BY
uq.Id
),
QuestionOwnerGoldBadges =
SUM
(
CASE
WHEN b.Name IN ('Legendary', 'Enlightened', 'Great Answer')
THEN 1
ELSE 0
END
) OVER
(
PARTITION BY
uq.Id
)
FROM dbo.Posts AS q
JOIN dbo.Users AS uq
ON uq.Id = q.OwnerUserId
LEFT JOIN dbo.Posts AS a
ON a.Id = q.AcceptedAnswerId
LEFT JOIN dbo.Users AS ua
ON ua.Id = a.OwnerUserId
LEFT JOIN dbo.Votes AS v
ON v.PostId = q.Id
LEFT JOIN dbo.Comments AS c
ON c.PostId = q.Id
LEFT JOIN dbo.Badges AS b
ON b.UserId = uq.Id
WHERE q.PostTypeId = 1
AND CASE
WHEN uq.Reputation >= 50000
AND q.Score >= 10
THEN 1
WHEN uq.Reputation BETWEEN 5000 AND 49999
AND q.Score >= 20
AND q.ViewCount >= 5000
THEN 1
WHEN uq.Reputation < 5000
AND (
q.Score >= 40
OR (a.Score IS NOT NULL AND a.Score >= 15)
)
THEN 1
WHEN ua.Reputation >= 20000
AND v.VoteTypeId = 2
AND c.CreationDate >= '2012-01-01'
THEN 1
ELSE 0
END = 1
ORDER BY
q.Score DESC,
q.ViewCount DESC,
q.CreationDate DESC;
Управление пространством tempdb в Resource Governor SQL Server 2025
SQL Server 2025 представляет функцию управления пространством tempdb в Resource Governor, которая позволяет устанавливать ограничения одним из двух способов.
Для ограничения использования tempdb существуют две опции на уровне «группы», как описано в CREATE WORKLOAD GROUP:
- GROUP_MAX_TEMPDB_DATA_MB: «максимальный объём пространства, которое рабочая группа может использовать в файлах данных tempdb, в мегабайтах… Ограничение относится к общему пространству в tempdb, потребляемому всеми сеансами в рабочей группе.»
- GROUP_MAX_TEMPDB_DATA_PERCENT: «Указывает максимальный объём пространства, который рабочая группа может использовать в файлах данных tempdb, в процентах от максимального размера tempdb.» В документации есть таблица, подробно описывающая требования к конфигурации для применения этого параметра. Одно из требований — он не работает, если для рабочей группы также настроен
GROUP_MAX_TEMPDB_DATA_MB.
Когда запрос превышает установленное ограничение, SQL Server прерывает его с ошибкой 1138, уровень серьёзности 17: Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'имя-рабочей-группы'.
Настройка Resource Governor для нашего теста
Чтобы протестировать управление tempdb с нашим запросом, вызывающим переполнение, мы выполним следующие шаги:
- Создадим пул ресурсов
- Создадим рабочую группу с ограничениями tempdb, использующую этот пул ресурсов
- Создадим классифицирующую функцию для направления наших тестовых запросов в эту рабочую группу
- Включим Resource Governor
- Проверим конфигурацию
- Запустим тестовый запрос из классифицированного сеанса
- Понаблюдаем за использованием tempdb пулом, запросив
sys.dm_resource_governor_workload_groups
Шаг 1: Создание пула ресурсов
USE master;
GO
CREATE RESOURCE POOL tempdb_spill_test_pool;
GO
Пул ресурсов определяет физические ресурсы, доступные для группы запросов, которые вы классифицируете для использования этого пула. Пул ресурсов позволяет устанавливать такие параметры, как ограничения процента ЦП с помощью MAX_CPU_PERCENT и ограничения памяти с помощью MAX_MEMORY_PERCENT.
Функция управления tempdb не имеет параметров на самом пуле ресурсов, все параметры находятся на рабочей группе. Вы можете использовать пул по умолчанию, если хотите, но я считаю полезным создавать отдельные пулы для различных рабочих нагрузок для отслеживания, и вам может понадобиться применить ограничения пула в будущем.
Я создаю базовый пул без указания ограничений ресурсов.
Шаг 2: Создание рабочей группы с ограничениями tempdb
CREATE WORKLOAD GROUP tempdb_spill_test_group
WITH
(
GROUP_MAX_TEMPDB_DATA_MB = 10240
)
USING tempdb_spill_test_pool;
GO
Предложение USING назначает эту рабочую группу нашему пулу ресурсов. Сеансы, классифицированные в эту рабочую группу, получают ограничение GROUP_MAX_TEMPDB_DATA_MB (10 ГБ) и любые другие ограничения ресурсов, которые мы установили для пула.
Некоторые параметры рабочей группы позволяют определять политики для всей группы, например, GROUP_MAX_REQUESTS. Некоторые параметры определяют политики для отдельных запросов внутри группы. Например, REQUEST_MAX_MEMORY_GRANT_PERCENT и REQUEST_MAX_CPU_TIME_SEC позволяют ограничивать ресурсы для отдельных запросов, выполняющихся в пуле. Вы также можете установить MAX_DOP (максимальная степень параллелизма) для всех запросов в пуле.
🔥 Не делайте поспешных выводов: Я не утверждаю, что 10 ГБ — это «лучшая практика» для объёма пространства, разрешённого рабочей группе в tempdb. Фактические требования к tempdb сильно различаются в разных производственных средах. Некоторые рабочие нагрузки требуют очень мало, некоторым нужно много. Проанализируйте конкретную систему, чтобы понять, что подходит.
Шаг 3: Создание классифицирующей функции
USE master;
GO
CREATE OR ALTER FUNCTION
dbo.tempdb_test_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE
@workload_group_name sysname = N'default';
IF APP_NAME() = N'tempdb_spill_test'
BEGIN
SELECT
@workload_group_name = N'tempdb_spill_test_group';
END;
RETURN @workload_group_name;
END;
GO
Классифицирующая функция — это скалярная пользовательская функция, которая выполняется для каждого нового соединения. Она возвращает имя рабочей группы, которой следует назначить сеанс. Функция должна быть создана в базе данных master с указанием SCHEMABINDING.
Только одна классифицирующая функция может быть активна на данном экземпляре SQL Server одновременно.
Классифицирующие функции обычно используют свойства сеанса, такие как APP_NAME(), SUSER_SNAME() (имя входа), HOST_NAME() (имя клиентского компьютера) или пользовательскую логику для маршрутизации сеансов. Функция выполняется во время входа в систему, поэтому делайте её лёгкой и быстрой, чтобы не повлиять на производительность при входе и избежать таймаутов соединения.
Это также означает, что когда вы включаете Resource Governor, уже подключённые сеансы не будут использовать только что настроенные пулы или рабочие группы. Сеансы классифицируются только при следующем входе в систему.
Используемая здесь классифицирующая функция проверяет имя приложения, потому что это очень просто использовать для тестирования.
- Когда сеанс подключается с именем приложения 'tempdb_spill_test', он назначает этот сеанс группе 'tempdb_spill_test_group'.
- Все остальные сеансы попадают в рабочую группу 'default'.
Шаг 4: Настройка и включение Resource Governor
ALTER RESOURCE GOVERNOR
WITH
(
CLASSIFIER_FUNCTION = dbo.tempdb_test_classifier
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Шаг 5: Проверка конфигурации
SELECT
group_name = wg.name,
wg.group_max_tempdb_data_mb,
wg.group_max_tempdb_data_percent,
pool_name = rp.name
FROM sys.resource_governor_workload_groups AS wg
JOIN sys.dm_resource_governor_resource_pools AS rp
ON rp.pool_id = wg.pool_id
WHERE wg.name = N'tempdb_spill_test_group';
GO
Результаты запроса показывают, что наша рабочая группа настроена с ограничением в 10 ГБ для данных tempdb, и что группа назначена созданному нами пулу ресурсов.
Шаг 6: Запуск тестового запроса в классифицированном сеансе
Чтобы запустить тестовый запрос под управлением Resource Governor, откройте новое окно запроса в SSMS и установите имя приложения в свойствах соединения. Если вы используете SQL Server Management Studio 22, вот шаги в новом окне подключения к базе данных:
- Выберите Файл, Создать, Запрос к ядру СУБД
- В диалоговом окне подключения выберите Дополнительно
- В разделе Контекст установите имя приложения: tempdb_spill_test
- Нажмите OK
- Убедитесь, что ServerName и другая информация о подключении установлены правильно, и нажмите Подключить
Выполните этот запрос в вашем новом сеансе, чтобы убедиться, что он был классифицирован правильно:
SELECT
workload_group_name = wg.name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_resource_governor_workload_groups AS wg
ON wg.group_id = s.group_id
WHERE s.session_id = @@SPID;
GO
Это должно вернуть 'tempdb_spill_test_group' в качестве имени рабочей группы.
Теперь запустите тестовый запрос выше в этом сеансе.
Запрос, вызывающий переполнение, достигает предела в 10 ГБ на моём ноутбуке примерно за 17 секунд.
Когда он достигает предела, SQL Server прерывает запрос, и я вижу предсказанную ошибку:
Msg 1138, Level 17, State 1, Line 3
Could not allocate a new page for database 'tempdb' because that would exceed
the limit set for workload group 'tempdb_spill_test_group', group_id 256.
Шаг 7. Запрос на использование tempdb и нарушений из sys.dm_resource_governor_workload_groups
Поскольку запрос завершается быстро, его легко запустить несколько раз и наблюдать за использованием tempdb рабочей группой во время его выполнения:
SELECT
wg.group_id,
wg.name,
wg.tempdb_data_space_kb,
wg.peak_tempdb_data_space_kb,
wg.total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups AS wg
WHERE wg.name = N'tempdb_spill_test_group';
GO
Вот пример того, как выглядит вывод на моём ноутбуке:
Столбец total_tempdb_data_limit_violation_count не считает количество запросов, которые завершились сбоем.
Этот столбец в sys.dm_resource_governor_workload_groups определяется как:
Количество прерываний запроса с ошибкой 1138, так как это приведет к превышению предела потребления пространства данных tempdb для группы рабочей нагрузки. Может принимать значение NULL..
В ходе краткого тестирования я обычно вижу, что это число увеличивается на 8 каждый раз, когда мой запрос достигает предела и прерывается. На моём ноутбуке запрос выполняется с максимальной степенью параллелизма 8. Иногда я вижу увеличение на другое число, например, 5.
Была ли эффективная maxdop понижена для этого запуска по какой-то причине? Я не уверен, я не углублялся в это, но приращение определённо не является количеством отдельных запусков запросов, завершившихся сбоем.
Очистка: Удаление конфигурации Resource Governor
Этот скрипт отменяет настройки, сделанные в демонстрации выше. Вам нужно закрыть все активные сеансы, использующие рабочую группу/пул ресурсов, прежде чем это будет успешно выполнено.
ALTER RESOURCE GOVERNOR DISABLE;
GO
ALTER RESOURCE GOVERNOR
WITH
(
CLASSIFIER_FUNCTION = NULL
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP FUNCTION IF EXISTS dbo.tempdb_test_classifier;
GO
DROP WORKLOAD GROUP tempdb_spill_test_group;
GO
DROP RESOURCE POOL tempdb_spill_test_pool;
GO
Помогло ли управление ресурсами с переполнением tempdb?
✅ Да, эта функция Resource Governor защитила мой жёсткий диск от безумного потребления пространства этим действительно ужасным запросом. Это подтверждает то, что сказано в документации: функция управления пространством tempdb применяется не только к использованию временных таблиц, но также помогает сдерживать переполнение памяти в операторах плана запроса.
Мои выводы:
- Это отличная функция для SQL Server, особенно если у вас есть большие объёмы данных, и запросы не всегда могут быть протестированы с размерами производственных наборов данных перед выпуском в ваш SQL Server. Если у вас есть автоматически сгенерированный код или люди, которым необходимо быстро разрабатывать запросы для аналитики, вы, вероятно, очень рады получить эту функцию.
- Эта функция управления tempdb не предоставляет предупреждений или контролируемой деградации. Это жёсткий лимит: оставайтесь ниже него, или запросы будут завершаться сбоем. Это делает её полезной для защиты сервера, но означает, что вам нужно настраивать запросы или увеличивать распределение памяти, чтобы избежать достижения лимита.
- Столбец
total_tempdb_data_limit_violation_countвsys.dm_resource_governor_workload_groupsсбивает с толку. Документация читается так, как будто он считает отдельные запросы, завершившиеся сбоем, хотя на самом деле, кажется, он считает что-то вроде потоков, которые терпят неудачу при достижении лимита. Это не кажется огромной проблемой, если вы не зацикливаетесь на этом.
В целом, я считаю, что эта функция — долгожданное дополнение к Resource Governor, и прекрасно, что Resource Governor доступен для Standard Edition в SQL Server 2025.
Комментариев нет:
Отправить комментарий