Руководство: Примеры настройки управления ресурсами tempdb

Применимо к: SQL Server 2025 (17.x) и более поздних версий

В примерах этой статьи показано, как задать ограничения на tempdb потребление пространства и просмотреть потребление пространства для каждой tempdb группы рабочей нагрузки.

Общие сведения об tempdb управлении ресурсами пространства см. в статье tempdb space resource governance.

Эти примеры помогут вам ознакомиться с tempdb управлением ресурсами пространства в тестовой непроизводной среде.

В примерах предполагается, что изначально регулятор ресурсов не включен и его конфигурация не изменяется от значения по умолчанию. Они также предполагают, что любые другие рабочие нагрузки на экземпляре SQL Server не существенно способствуют tempdb потреблению места при выполнении скриптов.

Установка фиксированного ограничения для группы рабочей нагрузки default

В этом примере общее tempdb потребление пространства запросами (запросами) в default группе рабочей нагрузки ограничивается фиксированным ограничением.

1. Измените группу рабочей нагрузки default , чтобы настроить фиксированное ограничение на 20 ГБ на tempdb потребление места.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
   

2. Включите регулятор ресурсов, чтобы сделать текущую конфигурацию эффективной.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. Посмотрите ограничения на потребление пространства tempdb.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

4. Проверьте текущее tempdb потребление пространства группой default рабочей нагрузки, добавьте данные tempdb , создав временную таблицу и вставив одну строку, а затем снова проверьте потребление места, чтобы увидеть увеличение.

   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   
   SELECT REPLICATE('A', 1000) AS c
   INTO #t;
   
   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   

5. При необходимости удалите ограничения для группы default и отключите диспетчер ресурсов, чтобы вернуться к неуправляемому потреблению пространства в tempdb.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

Установка процентного ограничения для группы рабочей нагрузки default

В этом примере файлы данных настраиваются tempdb таким образом, чтобы можно было использовать процентное ограничение, а затем ограничивает общее tempdb потребление пространства запросами (запросами) в default группе рабочих нагрузок до процентного ограничения.

1. Установите FILEGROWTH и MAXSIZE для всех tempdb файлов данных в соответствии с требованиями, ограничив максимальный размер tempdb до 1 ГБ.

   В этом примере предполагается, что tempdb есть четыре файла данных. Может потребоваться настроить скрипт, если конфигурация tempdb использует другое количество файлов или если логические имена файлов отличаются. Возможно, вам также потребуется перезапустить экземпляр SQL Server или уменьшить использование tempdb, если при выполнении этого скрипта появится ошибка 5040, ИЗМЕНЕНИЕ ФАЙЛА не удалось для базы данных 'tempdb'... Размер файла... больше MAXSIZE....

   ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp2', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp3', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp4', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   

2. Измените группу рабочей нагрузки default, чтобы установить лимит в пять процентов на tempdb потребление пространства. Максимальный размер в 1 ГБ tempdb ограничивает default группу до приблизительно 51 МБ tempdb пространства.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 5);
   

3. Если задано фиксированное ограничение, удалите его, чтобы оно не переопределило процентное ограничение.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
   

4. Включите регулятор ресурсов для эффективной настройки.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

5. Посмотрите ограничения на потребление пространства tempdb.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

6. Добавьте данные в tempdb, чтобы достичь предела.

   SELECT *
   INTO #m
   FROM sys.messages;
   

   Инструкция прерывается ошибкой 1138.

7. Проверьте статистику группы рабочей нагрузки для 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
   WHERE name = 'default';
   

   Значение в столбце total_tempdb_data_limit_violation_count увеличивается на 1, показывая, что один запрос в группе рабочей нагрузки default был прерван, поскольку его потребление пространства tempdb было ограничено регулятором ресурсов.

8. При необходимости удалите ограничения для группы default и отключите диспетчер ресурсов, чтобы вернуться к неуправляемому потреблению пространства в tempdb.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

9. При необходимости отмените изменения конфигурации файла данных tempdb, внесенные ранее в этом примере.

Установка фиксированного ограничения для определяемой пользователем группы рабочей нагрузки

В этом примере создается новая группа рабочей нагрузки, а затем создается функция классификатора для назначения сеансов с определенным именем приложения этой группе рабочей нагрузки.

В этом примере фиксированное ограничение tempdb потребления пространства для группы рабочей нагрузки имеет небольшое значение 1 МБ. Затем в примере показано, что попытка выделить пространство в tempdb, которое превышает ограничение, была прервана.

1. Создайте группу рабочей нагрузки и ограничьте потребление tempdb пространства до 1 МБ.

   CREATE WORKLOAD GROUP limited_tempdb_space_group
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
   

2. Создайте функцию классификатора в master базе данных. Классификатор использует встроенную функцию APP_NAME для определения имени приложения, указанного в строке подключения клиента. Если задано limited_tempdb_applicationимя приложения, функция возвращается limited_tempdb_space_group в качестве имени группы рабочей нагрузки, используемой. В противном случае функция возвращает default в качестве имени группы рабочей нагрузки.

   USE master;
   GO
   
   CREATE FUNCTION dbo.rg_classifier()
   RETURNS sysname
   WITH SCHEMABINDING
   AS
   BEGIN
   
   DECLARE @WorkloadGroupName sysname = N'default';
   
   IF APP_NAME() = N'limited_tempdb_application'
       SELECT @WorkloadGroupName = N'limited_tempdb_space_group';
   
   RETURN @WorkloadGroupName;
   
   END;
   GO
   

3. Измените регулятор ресурсов, чтобы использовать функцию классификатора и перенастроить регулятор ресурсов для использования новой конфигурации.

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

4. Откройте новый сеанс, классифицируемый в группу рабочей нагрузки limited_tempdb_space_group .

   1. В SQL Server Management Studio (SSMS) выберите Файл в главном меню, Создать, Запрос Движка Базы Данных.

   2. В диалоговом окне "Подключение к ядру СУБД" укажите тот же экземпляр ядра СУБД, где вы создали группу рабочей нагрузки и функцию классификатора на предыдущих шагах.

      Перейдите на вкладку Дополнительные параметры подключения и введите App=limited_tempdb_application. Это заставляет SSMS использовать limited_tempdb_application в качестве имени приложения при подключении к экземпляру. Функция APP_NAME() в классификаторе также возвращает это значение.

   3. Выберите "Подключиться", чтобы открыть новый сеанс.

5. Выполните следующую инструкцию в окне запроса, открывшемся на предыдущем шаге. Выходные данные должны показать, что сеанс классифицируется в группу рабочей нагрузки limited_tempdb_space_group .

   SELECT wg.name AS workload_group_name
   FROM sys.dm_exec_sessions AS s
   INNER JOIN sys.dm_resource_governor_workload_groups AS wg
   ON s.group_id = wg.group_id
   WHERE s.session_id = @@SPID;
   

6. Выполните следующую инструкцию в том же окне запроса.

   SELECT REPLICATE('S', 100) AS c
   INTO #t1;
   

   Утверждение успешно завершилось. Выполните следующую инструкцию в том же окне запроса:

   SELECT REPLICATE(CAST ('F' AS NVARCHAR (MAX)), 1000000) AS c
   INTO #t2;
   

   Запрос прерывается ошибкой 1138, так как он пытается превысить ограничение потребления пространства в 1 МБ tempdb для группы нагрузки.

7. Просмотрите текущее и пиковое tempdb потребление пространства группой рабочей нагрузки limited_tempdb_space_group.

   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
   WHERE name = 'limited_tempdb_space_group';
   

   Значение в столбце total_tempdb_data_limit_violation_count равно 1, показывающее, что один запрос в этой группе рабочей нагрузки был прерван, так как его tempdb потребление пространства было ограничено регулятором ресурсов.

8. При необходимости, чтобы вернуться к начальной конфигурации этого примера, отключить все сеансы с помощью limited_tempdb_space_group группы рабочей нагрузки и выполнить следующий скрипт T-SQL:

   /* Disable resource governor so that the classifier function can be dropped. */
   ALTER RESOURCE GOVERNOR DISABLE;
   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
   DROP FUNCTION IF EXISTS dbo.rg_classifier;
   
   /* Drop the workload group. This requires that no sessions are using this workload group. */
   DROP WORKLOAD GROUP limited_tempdb_space_group;
   
   /* Reconfigure resource governor to reload the effective configuration without the classifier function and the workload group. This enables resource governor. */
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   
   /* Disable resource governor to revert to the initial configuration. */
   ALTER RESOURCE GOVERNOR DISABLE;
   

   Так как SSMS сохраняет параметры подключения на вкладке "Дополнительные параметры подключения ", обязательно удалите App параметр при следующем подключении к тому же экземпляру ядра СУБД. Это позволяет избежать классификации подключений в группу limited_tempdb_space_group рабочих нагрузок, если она существует.

Связанный контент

• Управление ресурсами пространства tempdb
• Диспетчер ресурсов
• руководство по . Примеры конфигурации регулятора ресурсов и рекомендации
• ALTER RESOURCE GOVERNOR (Transact-SQL)
• CREATE WORKLOAD GROUP (Transact-SQL)
• ИЗМЕНИТЬ ГРУППУ НАГРУЗКИ (Transact-SQL)
• УДАЛИТЬ ГРУППУ НАГРУЗКИ (Transact-SQL)
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups