Thiago Zavaschi R2 www.zavaschi.com

SQL Server Resource Governor

Oi pessoal!

Há algum tempo atrás eu havia iniciado um artigo para a SQL Magazine, mas devido a alguns compromissos não pude finalizar/enviar para a revista.

O artigo está praticamente finalizado e é sobre Resource Governor. Segue o artigo:

Introdução

O Resource Governor é um mecanismo da plataforma SQL Server, tendo seu aparecimento na versão 2008, para controlar o acesso a recursos de memória e processamento. Boa gerência dos recursos disponíveis para o SQL Server pode significar o sucesso ou o fracasso da sua solução de negócio baseada neste sistema gerenciador de bancos de dados (SGBD).

A ideia deste artigo é demonstrar o recurso, onde e como se aplica, e sensibilizar o leitor no que tange a considerações relacionadas à concorrência que ocorre sobre os recursos do(s) servidor(es).

Já viu este cenário?

A aplicação tem seu desenvolvimento concluído (ou é gerado um release ao final de um ciclo), é colocada em um ambiente de validação/homologação e nesta etapa são feitos os testes para verificar a regra de negócio. Colocando os méritos das técnicas de validação um pouco de lado e assumindo que está tudo de acordo, a aplicação é, por fim, posta em produção. E é aí que os problemas mais graves começam...

Algumas semanas depois da aplicação estar em produção, os usuários começam a relatar problemas como a lentidão no sistema, timeouts (especialmente se for uma aplicação web), e etc. Esses problemas são relatados ao mesmo tempo em que as verificações feitas no banco de dados indicam que o consumo de memória e CPU estão próximos ao limite.

Uma análise mais detalhada mostra que a causa da lentidão é causada pela geração dos relatórios, pois os mesmo são extraídos a partir da base de dados de produção. Existem diversas estratégias que podem ser utilizadas para contornar o problema: replicação, espelhamento (mirroring), solução utilizando BI e bases OLAP, etc, etc, etc. Mesmo com a adoção de uma destas estratégias o uso do resource governor é interessante conforme veremos na sequência.

Em um cenário em que a compra de um novo servidor ou a utilização de uma solução de BI não é factível o resource governor pode dar um grande auxílio!

Mas o que é o resource governor?

O resource governor é “simplesmente” um mecanismo para a gerência dos recursos de memória e processador utilizados pelo SQL Server. Com o resource governor é possível classificar as queries disparadas contra a base de dados em workload groups pré-definidos, e estes, por sua vez, são mapeados em resource pools que efetivamente definem as limitações de memória e processamento.

As queries precisam ser classificadas em um dos workload groups criados, tal tarefa é executada por uma função de classificação (classifier function). O código desta função deve ser muito escrito, para garantir o tratamento adequado das queries, que podem ter características de mais de um workload group. A figura 1 contempla um esquema deste mecanismo.

Mas em quais cenários o Resource Governor é interessante ser aplicado? Principalmente para três cenários: Controle de queries “soltas” executadas no banco; controle da concorrência previsível de workloads (base de produção x relatórios, por exemplo); e a priorização dos workloads.

Figura 1. Arquitetura macro do mecanismo de Resouce Governor

Criando Resource Pools

Os resource pools representam os recursos físicos do servidor (memória e CPU). No SQL Server 2008 estes pools definem as quantidades máxima e mínima da utilização dos recursos. Existem livros que tratam resource pools como sendo instâncias virtuais do SQL Server, eu penso que é quase isso, visto que poderíamos ter uma separação mais consistente (além de memória e CPU). No SQL Server 2008 existem dois resource pools pré-definidos: Internal e o Default.

O pool internal é usado somente pela engine do SQL Server, não é possível apagá-lo ou fazer quaisquer alterações sobre as suas capacidades mínimas e máximas sobre os recursos.

O pool default será usado pelos workloads que não estão associados a nenhum resource pool. Caso o resource governor não esteja configurado na sua instância do SQL Server, todas as suas queries executarão neste pool. Não é possível apagá-lo ou alterá-lo, com exceção aos seus valores máximos e mínimos associados aos recursos que podem ser configurados.

Os administradores de bancos de dados podem criar novos resource pools. Seguindo o raciocínio do cenário anterior, vamos criar dois pools: um para as queries “normais” e outro para as queries executadas para extrair dados para os relatórios. A listagem 1 mostra o comando Transact-SQL para a criação do Resource Pool. Um detalhe importante é que a soma dos valores mínimos dos pools não pode ser maior que 100 %. O valor máximo individual de cada pool é de até 100 %, mas devemos lembrar de que este valor não será o real caso haja mais de um pool com este valor máximo. O cálculo do valor máximo efetivo deve ser feito subtraindo a soma dos demais mínimos, e este sim será o valor mais elevado atingido pelo SQL Server. Para nossos exemplos, inicialmente colocaremos ambos os pools com percentual máximo de 100 % de CPU.

Listagem 1. Criação dos Resource Pools

CREATE RESOURCE POOL NormalQueries

WITH (MAX_CPU_PERCENT = 100)

CREATE RESOURCE POOL RelatorioQueries

WITH (MAX_CPU_PERCENT = 100)

A sintaxe associada aos Resource Pools e os parâmetros passíveis de configuração, podem ser vistos na listagem 2 (o “valor” é um número correspondido de 1 a 100, para os valores máximos, e de 0 a 100 para os valores mínimos). Acredito que o nome dos parâmetros e a sintaxe são auto explicativos e por isso não entrarei em maiores detalhes, para mais informações consultar o Books Online do SQL Server 2008). Um detalhe importante é a utilização de aspas (“, ”) ou de colchetes ([, ]) na palavra default para evitar conflitos com a palavra reservada DEFAULT, isto vale para as sintaxes subseqüentes também).

Listagem 2. Sintaxe e Parâmetros Configuráveis - Resource Pool

CREATE|ALTER|DROP RESOURCE POOL { pool_name | "default" }

[WITH

( [ MIN_CPU_PERCENT = valor ]

     [ [ , ] MAX_CPU_PERCENT = valor ]

     [ [ , ] MIN_MEMORY_PERCENT = valor ]

     [ [ , ] MAX_MEMORY_PERCENT = valor ] )

]

[;]

Se não forem criados Resource Pools próprios, isto é, sempre utilizar o pool padrão, podemos dizer que é um comportamento similar ao encontrado no SQL Server 2005. Devemos também nos atentar para a limitação do número de pools que é de 20 (incluindo os dois pré-definidos).

Criando Workload Groups

Já vimos como fazer a criação dos resource pools, mas também foi visto que necessitamos de workload groups mapeados nestes pools para configurar o resource governor, pois se associarmos usuários diretamente aos pools poderia ser extremamente custoso qualquer gerência, para não dizer quase impossível. Acredito que um dos elementos mais interessantes dos workload groups é a capacidade de definir prioridade (baixa, média, alta) aos mesmos, o que aumenta ainda mais a granularidade da configuração possível. Por padrão a prioridade é a média (MEDIUM).

Um pool pode mapear zero ou mais workloads, por sua vez, mapeados com as queries dos usuários, mas um workload group só pode estar em um único pool (e sempre estará em um).

Vamos criar três workload groups (para exemplificar múltiplos workloads por pool): AdhocAdm, UsuariosNormais, Relatorios. Os comandos Transact-SQL para a criação dos workloads, para o nosso exemplo, podem ser vistos na listagem 3. A cláusula USING serve para mapear o workload sendo criado a um pool existente. Como já mencionado, as queries que se encaixarem nos workload groups não associados a um resource pool, utilizarão o resource pool default (sempre existente).

Listagem 3. Criação dos Workload Groups

CREATE WORKLOAD GROUP AdhocAdm

USING NormalQueries

CREATE WORKLOAD GROUP UsuariosNormais

USING NormalQueries

CREATE WORKLOAD GROUP Relatorios

USING RelatorioQueries

A listagem 4 mostra a sintaxe completa associada à manipulação de workload groups e também os parâmetros configuráveis.

Listagem 4. Sintaxe do Workload Group e seus Parâmetros Configuráveis

CREATE|ALTER|DROP WORKLOAD GROUP { group_name | "default" }

[WITH

([IMPORTANCE = { LOW | MEDIUM | HIGH }]

[[,] REQUEST_MAX_MEMORY_GRANT_PERCENT = valor ]

[[,] REQUEST_MAX_CPU_TIME_SEC = valor ]

[[,] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = valor ]

[[,] MAX_DOP = valor ]

[[,] GROUP_MAX_REQUESTS = valor ] )

]

[USING { pool_name | "default" }]

[;]

A sintaxe associada é um pouco mais complexa que a dos resource pools e por isso entrarei nos méritos de cada parâmetro.

IMPORTANCE: Define a prioridade do workload group, por padrão (se não especificada) é a média. Este grau de prioridade é usado para cálculos entre workgroups dentro do mesmo resource pool e não afeta os outros workload groups associados a outros resource pools.

REQUEST_MAX_MEMORY_GRANT_PERCENT: Especifica a quantidade máxima de memória que uma requisição única consegue utilizar do pool. Esta porcentagem é relativa ao tamanho da memória disponível ao pool (MAX_MEMORY_PERCENT). O parâmetro valor precisa ser zero ou um inteiro positivo até 100. O valor padrão (se não especificado) é 25.

REQUEST_MAX_CPU_TIME_SEC: Especifica o máximo de tempo (segundos) que uma query pode esperar por um recurso até ficar disponível antes da query falhar. O valor precisa ser 0 ou um inteiro positivo. Por padrão este valor é zero. O Resource Governor não impede a query de executar caso o tempo limite seja atingido, mas um evento é gerado (mais informações no Books Online do SQL Server 2008, ver sobre “CPU Threshold Exceeded Event Class”).

REQUEST_MEMORY_GRANT_TIMEOUT_SEC: Especifica o tempo máximo (segundos) que uma query pode esperar por memória (work buffer memory) até ficar disponível. O valor necessita ser um valor maior ou igua a 0 (padrão). Atingir o tempo limite não significa necessariamente que a execução da query irá falhar, isto só ocorrerá se existirem muitas solicitações concorrentes, a query executará na verdade com uma quantidade menor de memória disponível (o que pode afetar o desempenho da mesma).

MAX_DOP: Especifica o grau máximo de paralelismo (DOP: degree of parallelism) para requisições paralelas. O valor precisa ser 0 ou um número inteiro positivo, onde o valor máximo é de 64. O valor padrão é 0, e significa que útilizará o valor global configurado.

GROUP_MAX_REQUESTS: Especifica o número máximo de requisições simultâneas que são permitidas executar em um mesmo workload group. O valor precisa ser 0 ou um inteiro positivo. O valor padrão é 0 (requisições ilimitadas, teoricamente).

Por fim, a cláusula USING indica qual resource pool será utilizado, por padrão será o pool default.

Com o mapeamento resource pool ó workload group feito, devemos configurar a Classifier Function (função de classificação) que definirá qual o workload associado à chamada sendo feita.

Função de Classificação (Classifier Function)

Seguindo o raciocínio com base na figura 1, vemos que para efetuar o direcionamento das queries que chegam ao servidor SQL Server (idealmente colocado em um servidor físico diferente do servidor de aplicação) é necessária uma função de classificação. Para a construção do algoritmo de classificação é possível utilizar os parâmetros presentes na string de conexão, tais como: endereço IP; nome da aplicação; nome do usuário; entre outros. Sabendo o que é possível utilizar, é necessário definir as políticas que estarão presentes na função. Entre as funções auxiliares disponíveis podemos citar: SUSER_NAME(), HOST_NAME(), SUSER_SNAME(), etc. Temos também as novas funções: CONNECTIONPROPERTY(), ORIGINAL_DB_NAME() e também as novas opções para LOGINPROPERTY(). Para maior detalhamento do comportamento destas funções, favor olhar o Books Online do SQL Server 2008.

Para efeito de exemplificação utilizaremos os seguintes critérios:

Caso o usuário tenha aberto uma sessão através do SQL Server Management Studio (nome da aplicação) então deverá ser classificado no workload AdhocAdm.

Caso seja o usuário ‘Relatorio’, então será classificado para o workload de Relatorios.

Todos os demais que não se enquadrarem em nenhuma das regras anteriores serão classificados para o workload de UsuariosNormais.

Acompanhe o script de criação destas regras na listagem 5.

Listagem 5. Criação da Classifier Function

USE MASTER

GO

CREATE FUNCTION fnClassificadora()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @x SYSNAME

IF APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'

BEGIN

SET @x = 'AdhocAdm';

RETURN @x;

END

DECLARE @user VARCHAR(50) = SUSER_SNAME();

IF @user = 'Relatorio'

BEGIN

SET @x = 'Relatorios';

RETURN @x;

END

SET @x = 'UsuariosNormais';

RETURN @x;

END

A função de classificação demonstrada é bastante simples, mas contempla todas as definições que mencionei anteriormente. Fique atento, uma mesma sessão pode se encaixar em mais de um workload group, imagine (segundo nosso exemplo) se o usuário fosse “Relatorio”, mas utilizando o SQL Server Management Studio, logo caracterizando a presença em dois grupos distintos. Uma função de classificação bem programada também é peça fundamental de uma configuração bem sucedida do resource governor, com isso poderíamos ter uma função relativamente grande e complexa (dependendo do caso).

A função de classificação está criada, agora falta associá-la ao resource governor. A listagem 6 demonstra o comando Transact-SQL para realizar a associação.

Listagem 6. Associação da Classifier Function com o Resource Governor

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.fnClassificadora)

Todas as configurações não terão efeito até que o RECONFIGURE seja executado. O script para o RECONFIGURE pode ser observado na listagem 7.

Listagem 7. Execução do RECONFIGURE

ALTER RESOURCE GOVERNOR

RECONFIGURE

Este foi o último ponto de configuração do resource governor, isto significa que a partir desse momento as queries serão executadas segundo os critérios configurados.

---

É isso pessoal, espero que ajude vocês nos seus estudos!

Grande abraço,
Thiago Zavaschi