Como limitar CPU e memória no SQL Server

O SQL Server possui um recurso chamado “Resource Governor”. Com ele é possível limitar o uso de CPU e memória RAM alocada, por uma conexão ou por um grupo de usuários. Embora seja possível limitar os recursos que se tornam disponiveis para um grupo de carga de trabalho, qualquer pedido que esteja em execução não é limitado e você não pode limitar as operações internas do SQL Server.

O “Resource Governor” trabalha com três componentes:
* Grupos de carga de trabalho (Workload Groups)
* Pools de recursos (Resource Pools)
* Funções de classificação (Classification)

Grupo de carga de trabalho – Workload group

Um grupo de carga de trabalho (workload group) é apenas um nome associado a sessão de usuário, ele não define uma carga de trabalho de consulta, mas sim um login que esteja executando as consultas, ou seja, são apenas rótulos que você associa a uma conexão, no momento que ela é criada, para que o “Resource Governor” posa atribuir a conexão ao pool de recursos (resource pool) apropriado.

Função classificadora – Classifier function

Trata-se de uma função que devemos criar para o banco de dados “master”. Apenas uma função pode estar ativar por vez, neste contexto. Ela não deve possuir parâmetro algum. Deve retornar um valor scalar. Seu retorno deve ser o nome do grupo de trabalho, em que a sessão deve trabalhar. É apenas uma função comum do T-SQL.

Funções de classificação (Classification)

Um pool de recursos define a quantidade de alocação minima e máxima de CPU e/ou memória RAM. O valor minimo indica a menor quantidade garantida de um recurso que está disponível para o pool de recursos. A soma de todos os valores minimo de todos os pool criados não podem ultrapassar 100.

Views do Resource Governor

Abaixo temos algumas views para visualizar informações sobre a configuração do Resource Governor.

SELECT * FROM sys.dm_resource_governor_resource_pools;
SELECT * FROM sys.dm_resource_governor_workload_groups;
SELECT * FROM sys.dm_resource_governor_configuration;

sys.dm_resource_governor_resource_pools: Retorna informações sobre o estado atual pool de recursos, a configuração atual de pools de recursos, e as estatísticas do conjunto de recursos. Mais detalhes aqui.
sys.dm_resource_governor_workload_groups: Retorna da carga de trabalho e estatísticas de grupo e a configuração atual na memória do grupo de carga de trabalho. Mais detalhes aqui.
sys.dm_resource_governor_configuration: Retorna uma linha que contém o atual estado de configuração na memória para o Administrador de Recursos. Mais detalhes aqui.

Implementação

Para configurar o “Resource Governor” devemos seguir os seguintes passos:

1. Ativar o Resource Governor
2. Criar um ou mais pools de recursos
3. Criar um ou mais grupos de carga de trabalho
4. Criar uma função classificadora
5. Associar cada grupo de carga de trabalho a um pool de recursos

Ativando o Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

Criando pools de recursos

Abaixo temos um T-SQL, para criar um pool de recursos

CREATE RESOURCE POOL ReportPool
WITH (MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 30, MIN_MEMORY_PERCENT = 0, MAX_MEMORY_PERCENT = 50)

Criando grupo de carga de trabalho

Abaixo temos um T-SQL, para criar um grupo de carga de trabalho

CREATE WORKLOAD GROUP ReportGroup
USING ReportPool;

Criando uma função classificadora

Abaixo tem uma função simples. O objetivo dela é definir qual grupo de trabalho deve ser utilizado para sessão.

CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @groupName SYSNAME = 'default';
	
	IF(SUSER_SNAME() = 'ReportGroupUser')
		SET @groupName = 'ReportGroup';
		
	RETURN @groupName;
END

Acima criamos uma função chamada “ResourceGovernorClassifier”, ela apenas verifica o nome do usuário (supondo que exista um usuário “ReportGroupUser”), na sessão atual. E determina o retorno do nome do Grupo de trabalho que deve ser retornado.

Associando cada grupo de carga de trabalho a um pool de recursos

Abaixo, temos um código T-SQL, para associar uma função ao “Resource Governor”.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);

Dica de teste

Uma forma de verificar se tudo ocorreu como esperado, é abrir o “Performance monitor” e utilizar alguns contadores de CPU e memória RAM. Para verificar se os limites impostos na configuração estam sendo respeitados.

Espero ter ajudado!
Até a próxima!

Anúncios
Marcado com: , , , , , ,
Publicado em SQL Server
Um comentário em “Como limitar CPU e memória no SQL Server
  1. Jean Carpes disse:

    Bom Dia! Pode me informar se essa funcionalidade pode ser utilizada no SQL Server 2012 STANDARD? Ou somente em Enterprise?

Deixe seu comentário...

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: