Tutorial: Exemplos para configurar a governança de recursos de espaço tempdb

Aplica-se a: SQL Server 2025 (17.x) Preview e versões posteriores

Exemplos neste artigo mostram como definir limites de consumo de espaço de tempdb e exibir o consumo de espaço por cada grupo de trabalho de tempdb.

Para obter uma introdução à governança de tempdb recursos espaciais, consulte Tempdb space resource governance.

Estes exemplos destinam-se a ajudá-lo a familiarizar-se com tempdb a governação de recursos espaciais num ambiente de teste não produtivo.

Os exemplos pressupõem que o administrador de recursos não está habilitado inicialmente e que sua configuração não foi alterada em relação ao padrão. Eles também assumem que quaisquer outras cargas de trabalho em sua instância do SQL Server não estão contribuindo materialmente para o tempdb consumo de espaço enquanto você executa os scripts.

Definir um limite fixo para o default grupo de carga de trabalho

Este exemplo limita o consumo total de espaço pelas solicitações (consultas) no grupo de carga de trabalho tempdb a um limite fixo default.

1. Modifique o grupo de carga de trabalho para configurar um limite fixo default de 20 GB no tempdb consumo de espaço.

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

2. Habilite o administrador de recursos para tornar a configuração atual efetiva.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. Veja os limites de consumo de tempdb espaço.

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

4. Verifique o consumo de espaço atual tempdb pelo default grupo de trabalho, adicione dados em tempdb criando uma tabela temporária e inserindo uma linha, e depois verifique novamente o consumo de espaço para ver o 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, remova os limites para o default grupo e desative o administrador de recursos para reverter ao consumo de espaço não governado em tempdb:

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

Definir um limite percentual para o default grupo de carga de trabalho

Este exemplo configura arquivos de tempdb dados para que o limite percentual possa ser usado e, em seguida, limita o consumo total de espaço pelas solicitações (consultas) no grupo de carga de trabalho tempdb a um limite percentual.

1. Defina FILEGROWTH e MAXSIZE para que todos os tempdb arquivos de dados atendam aos requisitos, limitando o tamanho máximo de tempdb 1 GB.

   Este exemplo pressupõe que tempdb tenha quatro arquivos de dados. Talvez seja necessário ajustar o script se sua tempdb configuração usar um número diferente de arquivos ou se os nomes lógicos dos arquivos forem diferentes. Você também pode precisar reiniciar sua instância do SQL Server ou reduzir tempdb o uso se você receber o erro 5040, MODIFY FILE falhou para o banco de dados 'tempdb' ... Tamanho do ficheiro ... é maior que MAXSIZE ... ao executar 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 o default grupo de carga de trabalho para configurar um limite de cinco por cento no tempdb consumo de espaço. Com o tamanho máximo tempdb de 1 GB, isso limita o default grupo a cerca de 51 MB de tempdb espaço.

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

3. Se um limite fixo estiver definido, remova-o para que não substitua o limite percentual.

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

4. Habilite o administrador de recursos para tornar a configuração eficaz.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

5. Veja os limites de consumo de tempdb espaço.

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

6. Adicione dados em tempdb para atingir o limite.

   SELECT *
   INTO #m
   FROM sys.messages;
   

   A instrução é abortada devido ao erro 1138.

7. Verifique as estatísticas do grupo de carga de trabalho 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';
   

   O valor na coluna total_tempdb_data_limit_violation_count é incrementado em 1, mostrando que uma solicitação no grupo de trabalho default foi abortada porque o seu consumo de espaço tempdb foi limitado pelo governador de recursos.

8. Opcionalmente, remova os limites para o default grupo e desative o administrador de recursos para reverter ao consumo de espaço não governado em tempdb:

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

9. Opcionalmente, reverta as alterações de configuração do tempdb arquivo de dados feitas anteriormente neste exemplo.

Definir um limite fixo para um grupo de carga de trabalho definido pelo usuário

Este exemplo cria um novo grupo de carga de trabalho e, em seguida, cria uma função de classificador para atribuir sessões com um nome de aplicativo específico a esse grupo de carga de trabalho.

Para os fins deste exemplo, o limite fixo tempdb no consumo de espaço para o grupo de carga de trabalho é definido como um pequeno valor de 1 MB. O exemplo mostra que uma tentativa de alocar espaço em `tempdb` que exceda o limite é abortada.

1. Crie um grupo de carga de trabalho e limite seu tempdb consumo de espaço a 1 MB.

   CREATE WORKLOAD GROUP limited_tempdb_space_group
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
   

2. Crie a função de classificador no master banco de dados. O classificador usa a função APP_NAME interna para determinar o nome do aplicativo especificado na cadeia de conexão do cliente. Se o nome do aplicativo estiver definido como limited_tempdb_application, a função retornará limited_tempdb_space_group como o nome do grupo de carga de trabalho a ser usado. Caso contrário, a função retornará default como o nome do grupo de carga de trabalho.

   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 o administrador de recursos para usar a função de classificador e reconfigure o administrador de recursos para usar a nova configuração.

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

4. Abra uma nova sessão que esteja classificada neste grupo de carga de trabalho limited_tempdb_space_group.

   1. No SQL Server Management Studio (SSMS), selecione Arquivo no menu principal, Novo, Consulta do Motor de Base de Dados.

   2. Na caixa de diálogo Conectar ao Mecanismo de Banco de Dados , especifique a mesma instância do Mecanismo de Banco de Dados em que você criou o grupo de carga de trabalho e a função de classificador nas etapas anteriores.

      Selecione a guia Parâmetros de conexão adicionais e digite App=limited_tempdb_application. Isso faz com que o SSMS use limited_tempdb_application como o nome do aplicativo ao se conectar à instância. A APP_NAME() função no classificador também retorna esse valor.

   3. Selecione Conectar para abrir uma nova sessão.

5. Execute a seguinte instrução na janela de consulta aberta na etapa anterior. A saída deve mostrar que sua sessão está classificada limited_tempdb_space_group no grupo de carga de trabalho.

   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. Execute a instrução a seguir na mesma janela de consulta.

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

   A instrução é concluída com êxito. Execute a seguinte instrução na mesma janela de consulta:

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

   A instrução é anulada com o erro 1138 porque tenta exceder o limite de consumo de espaço de 1 MB tempdb para o grupo de trabalho.

7. Veja o consumo de espaço atual e de pico do grupo de cargas de trabalho 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 = 'limited_tempdb_space_group';
   

   O valor na total_tempdb_data_limit_violation_count coluna é 1, mostrando que uma solicitação nesse grupo de carga de trabalho foi abortada porque seu tempdb consumo de espaço foi limitado pelo administrador de recursos.

8. Opcionalmente, para reverter para a configuração inicial deste exemplo, desconecte todas as sessões usando o limited_tempdb_space_group grupo de carga de trabalho e execute o seguinte script 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;
   

   Como o SSMS retém parâmetros de conexão na guia Parâmetros de Conexão Adicionais , certifique-se de remover o App parâmetro na próxima vez que você se conectar à mesma instância do Mecanismo de Banco de Dados. Isso evita que suas conexões sejam classificadas no limited_tempdb_space_group grupo de carga de trabalho, se existir.

Conteúdo relacionado

• Governança de recursos espaciais Tempdb
• Administrador de recursos
• Tutorial: Exemplos de configuração do administrador de recursos e práticas recomendadas
• ALTER ADMINISTRADOR DE RECURSOS (Transact-SQL)
• CRIAR GRUPO DE CARGA DE TRABALHO (Transact-SQL)
• GRUPO DE CARGA DE TRABALHO ALTER (Transact-SQL)
• GRUPO DE CARGA DE TRABALHO DROP (Transact-SQL)
• grupos_de_trabalho_do_governador_de_recursos_sys
• sys.dm_resource_governor_workload_groups