Tutorial: Ejemplos para configurar la gobernanza de recursos de espacio tempdb

Se aplica a: SQL Server 2025 (17.x) y versiones posteriores

En los ejemplos de este artículo se muestra cómo establecer límites en el consumo de espacio de tempdb y ver el consumo de espacio de tempdb por cada grupo de carga de trabajo.

Para obtener una introducción a la tempdb gobernanza de recursos espaciales, consulte Gobernanza de recursos espaciales de Tempdb.

Estos ejemplos están diseñados para ayudarle a familiarizarse con tempdb la gobernanza de recursos espaciales en un entorno de prueba y no producción.

En los ejemplos se supone que el regulador de recursos no está habilitado inicialmente y que su configuración no cambia de la predeterminada. Se asume también que ninguna otra carga de trabajo de la instancia de SQL Server contribuye materialmente al tempdb consumo de espacio mientras se ejecutan los scripts.

Establezca un límite fijo para el grupo de trabajo default

En este ejemplo se limita el consumo total tempdb de espacio por parte de las solicitudes (consultas) del default grupo de cargas de trabajo a un límite fijo.

1. Modifique el default grupo de cargas de trabajo para configurar un límite fijo de 20 GB en el tempdb consumo de espacio.

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

2. Active el regulador de recursos para aplicar la configuración actual.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. Vea los límites del tempdb consumo de espacio.

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

4. Compruebe el consumo de espacio actual tempdb del grupo de cargas de trabajo default, agregue datos en tempdb creando una tabla temporal e insertando una fila y, a continuación, compruebe nuevamente el consumo de espacio para ver el aumento.

   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. Opcionalmente, elimine los límites para el grupo default y deshabilite el regulador de recursos para volver al consumo de espacio no gobernado en tempdb.

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

Establecer un límite de porcentaje para el grupo de cargas de trabajo default

En este ejemplo se configuran tempdb los archivos de datos para que se pueda usar el límite de porcentaje y, a continuación, se limita el consumo total tempdb de espacio por parte de las solicitudes (consultas) del default grupo de cargas de trabajo a un límite de porcentaje.

1. Establezca FILEGROWTH y MAXSIZE para que todos los tempdb archivos de datos cumplan los requisitos, limitando el tamaño máximo de tempdb a 1 GB.

   En este ejemplo se supone que tempdb tiene cuatro archivos de datos. Es posible que tenga que ajustar el script si la tempdb configuración usa un número diferente de archivos o si los nombres lógicos de archivo son diferentes. Es posible que también tenga que reiniciar la instancia de SQL Server o reducir el uso de tempdb si llega a recibir el error 5040, MODIFY FILE falló para la base de datos 'tempdb' ... El tamaño del archivo ... es mayor que MAXSIZE ... al ejecutar este script.

   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. Modifique el default grupo de cargas de trabajo para configurar un límite del cinco por ciento en el consumo de espacio de tempdb. Con el tamaño máximo tempdb de 1 GB, esto limita el default grupo a aproximadamente 51 MB de tempdb espacio.

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

3. Si se establece un límite fijo, quítelo para que no invalide el límite de porcentaje.

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

4. Active el regulador de recursos para que la configuración surta efecto.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

5. Vea los límites del tempdb consumo de espacio.

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

6. Agregue datos en tempdb para alcanzar el límite.

   SELECT *
   INTO #m
   FROM sys.messages;
   

   La instrucción se anula con el error 1138.

7. Compruebe las estadísticas del grupo de cargas de trabajo para 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';
   

   El valor en la columna total_tempdb_data_limit_violation_count se incrementa en 1, lo que muestra que se anuló una solicitud en el grupo de carga de trabajo default porque el consumo de espacio tempdb estaba limitado por el regulador de recursos.

8. Opcionalmente, elimine los límites para el grupo default y deshabilite el regulador de recursos para volver al consumo de espacio no gobernado en tempdb.

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

9. Opcionalmente, revierta los tempdb cambios de configuración del archivo de datos realizados anteriormente en este ejemplo.

Establecimiento de un límite fijo para un grupo de cargas de trabajo definido por el usuario

En este ejemplo se crea un nuevo grupo de cargas de trabajo y, a continuación, se crea una función clasificadora para asignar sesiones con un nombre de aplicación específico a este grupo de cargas de trabajo.

Para los fines de este ejemplo, el límite fijo en el tempdb consumo de espacio para el grupo de cargas de trabajo se establece en un valor pequeño de 1 MB. A continuación, en el ejemplo se muestra que se anula un intento de asignar espacio en tempdb que supera el límite.

1. Cree un grupo de cargas de trabajo y limite su tempdb consumo de espacio a 1 MB.

   CREATE WORKLOAD GROUP limited_tempdb_space_group
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
   

2. Cree la función clasificadora en la master base de datos. El clasificador usa la función APP_NAME integrada para determinar el nombre de la aplicación especificado en la cadena de conexión del cliente. Si el nombre de la aplicación se establece en limited_tempdb_application, la función devuelve limited_tempdb_space_group como nombre del grupo de cargas de trabajo que se utilizará. De lo contrario, la función devuelve default como nombre del grupo de cargas de trabajo.

   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. Modifique el regulador de recursos para usar la función clasificadora y vuelva a configurar el regulador de recursos para usar la nueva configuración.

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

4. Abra una nueva sesión que se clasifique en el grupo de trabajo de cargas de limited_tempdb_space_group.

   1. En SQL Server Management Studio (SSMS), seleccione Archivo en el menú principal, Nuevo, Consulta del motor de base de datos.

   2. En el cuadro de diálogo Conectar al motor de base de datos , especifique la misma instancia del motor de base de datos donde creó el grupo de cargas de trabajo y la función clasificadora en los pasos anteriores.

      Seleccione la pestaña Parámetros de conexión adicionales y escriba App=limited_tempdb_application. Esto hace que SSMS use limited_tempdb_application como nombre de aplicación al conectarse a la instancia. La APP_NAME() función del clasificador también devuelve este valor.

   3. Seleccione Conectar para abrir una nueva sesión.

5. Ejecute la siguiente instrucción en la ventana de consulta abierta en el paso anterior. La salida debe mostrar que tu sesión está clasificada en el grupo de cargas de trabajo 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. Ejecute la siguiente instrucción en la misma ventana de consulta.

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

   La declaración se completa correctamente. Ejecute la siguiente instrucción en la misma ventana de consulta:

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

   La instrucción se anula con el error 1138 porque intenta superar el límite de consumo de espacio de tempdb de 1 MB para el grupo de cargas de trabajo.

7. Consulte el consumo de espacio actual y máximo tempdb por el grupo de trabajo 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';
   

   El valor de la total_tempdb_data_limit_violation_count columna es 1, que muestra que se anuló una solicitud de este grupo de cargas de trabajo porque su tempdb consumo de espacio estaba limitado por el regulador de recursos.

8. Opcionalmente, para revertir a la configuración inicial de este ejemplo, desconecte todas las sesiones mediante el limited_tempdb_space_group grupo de cargas de trabajo y ejecute el siguiente script de 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;
   

   Dado que SSMS conserva los parámetros de conexión en la pestaña Parámetros de conexión adicionales , asegúrese de quitar el App parámetro la próxima vez que se conecte a la misma instancia del motor de base de datos. Esto evita que las conexiones se clasifiquen en el limited_tempdb_space_group grupo de cargas de trabajo si existe.

Contenido relacionado

• Gobernanza de recursos de espacio tempdb
• Gobernador de Recursos
• Tutorial de : ejemplos de configuración del regulador de recursos y procedimientos recomendados
• ALTER RESOURCE GOVERNOR (Transact-SQL)
• CREAR GRUPO DE CARGA DE TRABAJO (Transact-SQL)
• ALTER WORKLOAD GROUP (Transact-SQL)
• DROP WORKLOAD GROUP (Transact-SQL)
• sys.grupos_de_trabajo_del_gobernador_de_recursos
• sys.dm_resource_governor_workload_groups