Cenários de gerenciamento de carga de trabalho do Administrador de Recursos

Seu desafio é determinar a configuração do grupo de carga de trabalho e pool de recursos que você deseja usar para seu ambiente SQL Server. Para isso, deve-se saber como recursos SQL Server são consumidos atualmente por um aplicativo.

A solução é criar um grupo de carga de trabalho que esteja no pool de recursos padrão. Execute o aplicativo durante vários dias e use as informações coletadas para responder as seguintes perguntas.

• O que é o requisito de CPU?
  Determine uma largura de CPU estimada multiplicando o uso médio total da CPU por solicitação pelo número médio de solicitações por segundo.

  O desvio máximo, médio e padrão do uso da CPU pode ser usado para determinar se um limite máximo de CPU é necessário.

• Qual o número de solicitações simultâneas?
  Use o número máximo e médio de estatísticas de solicitações simultâneas para determinar o fator de simultaneidade mínimo.

• Qual a memória total necessária?
  Use a memória máxima e média total usada para determinar a exigência de memória.

• Qual a memória necessária para uma única consulta?
  Use a memória máxima e média por estatística de consulta para determinar a memória necessária.

• Alguma consulta não está sendo executada por falta de memória?
  Use o tempo médio de espera em estatística de consulta de memória para determinar se alguma consulta está bloqueada devido à disponibilidade de memória.

P: Acabo de instalar uma versão nova do SQL Server e gostaria de usar o Administrador de Recursos. Como posso usá-lo em meu ambiente?

R: Considere o uso do Administrador de Recursos para monitorar o consumo de recursos por cargas de trabalho. Siga as etapas abaixo para definir um ambiente do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Crie grupos de carga de trabalho para suas cargas de trabalho.

2. Crie uma função de classificação.

3. Registre a função de classificação com o Administrador de Recursos.

4. Habilite o Administrador de Recursos.

5. Monitore os contadores de desempenho do Administrador de Recursos e verifique os DMVs que retornarão informações sobre o uso de recursos para um grupo de carga de trabalho.

Exemplo

Observação
A configuração a seguir não especifica um pool de recursos que deve ser usado pelo grupo de carga de trabalho. Por padrão, o grupo de carga de trabalho usa o pool padrão.

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Com base nos resultados de monitoramento do Cenário 1, gostaria de ver um evento cada vez que uma consulta no grupo ad-hoc (GroupAdhoc) fosse executada por mais de 30 segundos.

R: Siga as etapas abaixo para alterar a configuração atual do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Defina um limite sobre o uso de CPU para o grupo ad hoc.

2. Monitore eventos de Rastreamento SQL (classe de evento Resource Governor management).

3. Execute uma ação no evento. Por exemplo, ignore o evento, envie um e-mail, envie uma página ou execute o KILL comando na solicitação.

Exemplo

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Quero restringir o grupo ad-hoc para que não exceda 50 por cento do uso da CPU quando todas as solicitações estiverem acumuladas.

R: Como os cenários anteriores usaram o pool padrão, deve-se criar um novo pool de recursos. Siga as etapas abaixo para alterar a configuração atual do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Crie um novo pool de recursos e configure os limites de CPU.

2. Configure o grupo de carga de trabalho ad hoc para que use o novo pool de recursos.

Exemplo

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Quero garantir que o grupo de administração tenha sempre recursos para executar consultas de diagnóstico, mas essas consultas não devem ocupar mais do que 10 por cento dos recursos de memória do servidor.

R: Para isso você deve criar um novo pool de recursos. Siga as etapas abaixo para alterar a configuração atual do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Crie um novo pool de recursos e defina limites de recurso.

2. Configure o grupo de carga de trabalho de administração para que use o novo pool.

Exemplo

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

P: Como meus relatórios estão, no momento, no pool de recursos padrão, mas usando um grupo de carga de trabalho separado (GroupReports), eu gostaria de garantir que qualquer coisa no grupo de recursos padrão tenha uma prioridade de uso de recurso inferior aos relatórios.

R: Para isso, deve-se definir a configuração IMPORTANCE no grupo padrão.

Observação
Você pode alterar as configurações para o grupo padrão, mas não o pool padrão. Se você achar que é preciso uma alteração para o pool padrão, esse é um forte indicador da necessidade de criar um novo pool de recursos.

Siga a etapa abaixo para alterar a configuração atual do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Altere a configuração para o grupo padrão.

Exemplo

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: Quero criar um grupo de carga de trabalho novo que use um pool de recursos existente. Quero, então, alterar a função de classificação para que mova o grupo para um novo pool de recursos.

R: Siga as etapas a seguir para definir esse novo ambiente do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Crie um novo pool de recursos com as configurações padrão.

2. Crie um grupo de carga de trabalho novo que esteja em um pool existente.

3. Crie e registre uma função de classificação nova para tratar as solicitações.

Exemplo

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: Resolvi que um grupo de carga de trabalho (criado no Cenário 6) não é mais necessário, por isso quero descartar o grupo de carga de trabalho e o pool de recursos no qual ele está.

R: Siga as etapas a seguir para alterar o ambiente do Administrador de Recursos. Um exemplo de configuração é fornecido.

1. Crie e registre função de classificação nova que classificará solicitações no grupo de carga de trabalho restante.

2. Descarte o grupo de carga de trabalho.

3. Descarte o pool de recursos.

4. Aplique as alterações de configuração.

Exemplo

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO