SQL Server – Utilizando o Resource Governor para maior controle dos recursos do servidor

O Resource Governor nada mais é do que uma feature disponível a partir do SQL Server 2008 Enterprise e que nos dá a possibilidade de gerenciar a carga de trabalho e o consumo de recursos do servidor, especificando os limites de quantidade de CPU e memória que as solicitações recebidas podem utilizar, de acordo com um perfil a ser definido (a partir do hostname, nome do usuário, grupo do AD, software utilizado, etc), evitando que esses usuários não causem grandes impactos no ambiente como um todo.

Uma observação importante a ser dita é que o Resource Governor ganhou um importante upgrade a partir do SQL Server 2014, que é a possibilidade de controlar e gerenciar também a carga de I/O das requisições recebidas, permitindo limitar a quantidade máxima de IOPS utilizada para os determinados perfis.

Não só determinar quantidades máximas, o Resource Governor também pode ser utilizado para definir quantidades mínimas de recursos serão utilizados pelo perfil (Pool) e garantir que essas quantidades definidas estejam sempre disponíveis para essas sessões.

O Resource Governor é muito utilizado para limitar determinados perfis de acesso ao banco de dados e evitar que sessões de instâncias diferentes tenham que ficar disputando recursos do servidor entre si. Alguns exemplo onde o uso do Administrador de Recursos (Resource Governor) pode ser implementado:

• limitar o uso máximo de IOPS de queries de usuários ad-hoc
• limitar o uso máximo de CPU dos jobs da instância
• limitar a quantidade de memória máxima utilizada em um sistema específico
• limitar o o uso máximo de IOPS de usuários que fazem parte de um determinado grupo do AD
• definir a quantidade máxima de paralelismo das consultas de um determinado perfil

O funcionamento interno do Resource Governor se resume a três componentes principais (Classifier Function, Workload Group e Resource Pool), e atuam da seguinte forma:

• É criada uma nova conexão e uma requisição é realizada por esta sessão (Sessão 1 de n).
• A sessão é classificada de acordo com os critérios pré-definidos pela função de classificação (Classifier function). Esses critérios podem ser nome do usuário, grupo de AD a qual faz parte, role de sistema ou role de database a qual faz parte, nome do software utilizado, etc.
• A requisição da sessão é roteada para um workload group de acordo com o retorno da função de classificação, por exemplo, Grupo 4.
• O workload group utiliza o resource pool ao qual está associado, por exemplo, Pool 2.
• O resource pool fornece e limita os recursos requeridos pelo aplicativo, por exemplo, Aplicativo 3.

Referência: Inglês – Português

As funções de classificação ou Classifier Functions são funções criadas pelo usuário no database “master”, que serão aplicadas automaticamente em todas as novas conexões feitas na instância que tem o Resource Governor habilitado.

Essas funções irão retornar o nome do workload group a ser utilizado por essa nova conexão e partir da lógica implementada por ela, que pode utilizar funções nativas no SQL Server como HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER() e IS_MEMBER() para determinar critérios para classificar a sessão e determinadas qual workload group será utilizando quando program_name like ‘%Management Studio%’, por exemplo.

Por este motivo, é importante ter em mente que uma função mal implementada ou que utilize muitos recursos para atribuir as sessões, pode acabar gerando um grande problema de performance na instância. Por este motivo, é uma boa prática habilitar a conexão DAC para permitir a conexão na instância bypassando o uso da função de classificação. Caso você não faça isso e precise

Caso o você não tenha criado uma função de classificação, ou não tenha atribuído a função ao Resource Governor ou retorno da função seja “default”, NULL ou o nome de um grupo retornado pela função não exista, essa sessão será roteada para o workload group “default”.

Vale ressaltar que apenas uma função de classificação pode ser utilizada pelo Resource Governor e, como a função é criada utilizando o parâmetro WITH SCHEMABINDING, ela só pode ter removida ou alterada se você remover a associação da função com o Resource Governor, utilizando o comando
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=NULL).

Exemplo de criação

Referência: Inglês – Português

Os workload groups ou Grupos de carga de trabalho são como contêineres para requisições com critérios semelhantes. Cada workload group é associado a um pool de recurso, sendo que um pool de recurso pode ser associado a N workload groups.

O workload group de uma sessão é definido através da função de classificação (Classifier function) e ele possui algumas funções e limitações muito importantes, como a definição de importância das requisições que fazem parte do workload group.

Através do parâmetro IMPORTANCE = { LOW | MEDIUM (Padrão) | HIGH }, você pode definir que as requisições desse workload group terão prioridade sobre as conexões de outro workload group que estejam utilizando o mesmo resource pool (esse parâmetro não afeta as requisições que estejam utilizando outro resource pool). Esse valor serve para definir a ordem da lista de requisições do mesmo scheduler. Caso você tenha outros schedulers disponíveis, 1 query com importância LOW e 1 query com importância HIGH, as 2 consultas podem ser executadas concorrentemente.

O parâmetro REQUEST_MAX_MEMORY_GRANT_PERCENT especifica a quantidade máxima (em %) de memória que uma conexão pode tirar do pool. Observem que especificar esse valor para 0 irá impedir que consultas que utilizem SORT e HASH JOIN executem.

O parâmetro REQUEST_MAX_CPU_TIME_SEC permite definir o tempo máximo, em segundos, que uma query pode executar (o valor padrão é 0, ou seja, sem limite). Caso a requisição ultrapasse o tempo limite, um evento de trace será gerado. Até a versão SQL Server 2017 CU3, a sessão não era interrompida quando atingia esse limite, mas a partir dessa versão, isso pode ser configurado utilizando a traceflag 2422.

O parâmetro MAX_DOP permite definir a quantidade máxima de paralelismo (Maximum Degree Of Parallelism – MAXDOP) das requisições feitas no workload group, onde valor padrão é 0 e os valores permitidos são entre 0 e 64. Essa configuração sobrepõe o parâmetro sp_configure ‘max degree of parallelism’ e também a query hint OPTION(MAXDOP N).

O parâmetro GROUP_MAX_REQUESTS define a quantidade máxima de requisições simultâneas executadas pelo workload group. O valor padrão é 0, ou seja, sem limitação. Quando esse número é atingido, as novas conexões ficarão em espera (wait state) até que o número de requisições simultâneas fique abaixo do máximo.

Exemplo de criação

Referência: Inglês – Português

O pool de recursos representa os recursos físicos do servidor. É possível pensar no pool como uma instância virtual do SQL Server dentro de uma instância do SQL Server. Dois pools de recursos (interno e padrão) são criados quando o SQL Server é instalado e você pode criar mais pools de acordo com a sua necessidade (máximo de 64 pools de usuário).

O pool interno é utilizado para os processos em background do SQL Server e é prioritário sobre qualquer outro pool. Caso ele necessite de todos os recursos disponíveis na instância, ele irá utilizar. Além do pool interno, existe também o pool padrão, que é utilizado para o restante das conexões que não foram classificadas para utilizar o pool interno e nem os pools personalizados do usuário.

Quando você for criar um pool, você deve especificar os limites máximo e mínimo de CPU, Memória e I/O (IOPS).

O parâmetro MIN_CPU_PERCENT lhe permite especificar o valor mínimo de CPU utilizados por todas as requisições do pool, e é especialmente útil quando ocorre um contenção de CPU, pois o SQL Server irá garantir que esse valor esteja disponível para uso pelas conexões deste pool. Caso o pool não esteja em utilização, esse CPU reservado ficará disponível para uso pelos outros pools até que o pool em questão volte a ter conexões.

O parâmetro MAX_CPU_PERCENT lhe permite especificar o % máximo de CPU que as requisições do pool poderão utilizar na instância. Observe que o percentual máximo de CPU é um máximo oportunista. Se houver uma capacidade de CPU disponível, a carga de trabalho a usará até 100%. O valor máximo será aplicado apenas quando houver contenção para recursos da CPU.

O parâmetro CAP_CPU_PERCENT define um limite máximo “real” do uso de CPU pelo pool. As cargas de trabalho associadas com o pool podem usar a capacidade da CPU acima do valor de MAX_CPU_PERCENT quando ela está disponível, mas não acima do valor de CAP_CPU_PERCENT.

O parâmetro MIN_MEMORY_PERCENT permite especificar e reservar uma quantidade mínima de memória que sempre será pré-alocada na instância para o pool, independente se o pool estiver sendo utilizado ou não.

Vale lembrar que a soma do valor mínimo de todos os pools não pode ser superior a 100% e o valor do parâmetro MAX_CPU_PERCENT do pool deve ser superior ao valor do parâmetro MIN_CPU_PERCENT.

Os parâmetros MIN_IOPS_PER_VOLUME e MAX_IOPS_PER_VOLUME são utilizados para determinar as quantidades mínima e máxima de IOPS utilizado pelas conexões do pool.

Exemplo de criação

Referência: Inglês – Português

Para verificar o status do Resource Governor, basta realizar uma consulta na view sys.resource_governor_configuration e analisar a coluna is_enabled.

Segue a lista de views do Resource Governor:

• sys.resource_governor_configuration
• sys.resource_governor_external_resource_pool_affinity
• sys.resource_governor_external_resource_pools
• sys.resource_governor_resource_pool_affinity
• sys.resource_governor_resource_pools
• sys.resource_governor_workload_groups

Segue a lista de DMV’s do Resource Governor

• sys.dm_resource_governor_configuration
• sys.dm_resource_governor_external_resource_pool_affinity
• sys.dm_resource_governor_external_resource_pools
• sys.dm_resource_governor_resource_pool_affinity
• sys.dm_resource_governor_resource_pool_volumes
• sys.dm_resource_governor_resource_pools
• sys.dm_resource_governor_workload_groups

Agora que já vimos como o Resource Governor funciona na teoria, vamos colocar tudo em prática no exemplo abaixo:

Result:

Caso você prefira, pode utilizar também a interface (GUI) do Management Studio para configurar o Resource Group e Workload group:

E com isso, abrir a interface de edição/criação de Resource Group e Workload group (é a mesma interface para ambos):

Vale lembrar que nem todos os parâmetros estão disponíveis na interface do SQL Server Management Studio (Testei na versão 17.4), como, por exemplo, os parâmetros de I/O. Portanto, sugiro que utilize a linha de comando para gerenciar o Resource Governor.

Para quem nunca utilizou ou testou o Resource Governor, podem existir os seguintes questionamentos:
– E performance das consultas? Ela vai mudar após implementar essa feature ?
– Será que o Resource Governor realmente funciona?
– É possível mesmo limitar recursos, como disco, de determinadas sessões ?

Vamos fazer um teste simples para comprovar isso:

• Criar um contador no Perfmon do Windows (Monitor de Desempenho) para monitorar o contador Disk Read IO/sec do objeto MSSQL$SQL2016:Resource Pool Stats.
• Criar um Resource Pool, limitando a quantidade máxima de IOPS (MAX_IOPS_PER_VOLUME) em 200.
• Executar o comando DBCC CHECKDB (dirceuresende ) WITH NO_INFOMSGS
• Aguardar alguns segundos e alterar o resource pool, aumentando a quantidade máxima de IOPS em 2.000.
• Executar novamente o comando DBCC CHECKDB (dirceuresende ) WITH NO_INFOMSGS
• Coletar os resultados e comparar os gráficos

Após realizar esses passos, vamos analisar o resultado:

Conforme visualizado no gráfico, o Resource Governor cumpriu o seu papel e limitou corretamente a quantidade de IOPS do disco utilizado em 200 IOPS. Após aumentar o limite para 2.000 IOPS, pudemos observar que o disco conseguiu chegar até 1.000 IOPS e só não pôde ultrapassar esse valor por limitações de hardware mesmo, onde também pode-se observar que devido ao limite maior de consumo de IOPS, o tempo para executar o checkdb foi menor.

Para desativar o Resource Governor na sua instância, basta utilizar os comandos abaixo:

Lembrando que as conexões atuais devem ser finalizadas (ou reiniciar a instância), caso contrário, o Resource Governor continuará ativo e você irá receber essa mensagem de erro:

Para excluir também os Workloads groups e Resource Pools, pode também utilizar os comandos abaixo:

Após ativar o Resource Governor, você pode monitorar o uso de CPU do resource pool de forma resumida utilizando a consulta abaixo:

Result:

Ou monitorar todos os parâmetros de CPU dos pools e workload groups:

Você também pode monitorar o uso de I/O dos resource pools utilizando a query abaixo:

Result:

Você também pode utilizar a query abaixo para verificar qual o workload group utilizado pelas sessões ativas em execução no momento:

Result:

Caso você queira uma versão ainda mais completa dessa query, que consegue praticamente replicar o comportamento da sp_whoisactive, dê uma lida no post SQL Server – Query para retornar as consultas em execução (sp_WhoIsActive sem consumir TempDB).

Abaixo, vou listar algumas boas práticas para se utilizar no Resource Governor e assim, evitar problemas futuros ao utilizar esse recurso:

• Utilizar o DAC: Como todas as novas conexões passam pela função de classificação do Resource Governor, um problema nessa função pode dificultar o acesso à instância para corrigir o problema. Para contornar isso facilmente, você pode habilitar e utilizar o DAC (Dedicated Administrator Connection) na sua instância, e assim, conseguir logar e alterar a função de classificação. Caso não saiba como fazer isso, dê uma lida no post Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server.
• Cuidado com a função de classificação: Conforme citado acima, um erro de codificação na função de classificação pode levar a efeitos muito ruins na sua instância. Para isso, evite ao máximo utilizar funções ou recursos que podem deixar o tempo de resposta dessa função alto. Caso a função precise consultar uma tabela física, garanta que o acesso está rápido o suficiente e os índices estão satisfazendo as condições dessa consulta.
• Pool padrão e Workload group padrão: Uma boa prática ao utilizar o Resource Governor, é deixar uma parte dos recursos disponíveis para o pool padrão e para o workload group padrão. Isso é importante porque todas as sessões que não forem de processos internos do SQL Server e não puderem ser classificadas em nenhum pool personalizado, irão fazer parte do pool padrão. Se esse pool não tiver recursos necessários disponíveis, essas requisições poderão ter problemas de lentidão.
• Sempre monitore: Após implementar o Resource Governor, você deverá estar sempre atento e acompanhando o fluxo das requisições e validar se estão sendo direcionadas para os workload groups corretos, além de monitorar possíveis novas necessidades que necessitem de uma alteração na função de classificação
• Cuidado com as configurações de memória: Conforme já citado neste post, as configurações de memória são pré-alocadas ao se criar o pool, ou seja, mesmo que o pool não tenha nenhuma conexão ativa e não esteja utilizando essa memória pré-alocada, ela continuará reservada pelo pool.