Para obter o máximo de performance, DBAs configuram o SQL Server para atender às suas necessidades de negócio e muitas vezes acabam alterando configurações de forma equivocada. Neste artigo, iremos tratar sobre as principais configurações utilizadas para se obter uma boa performance no banco de dados SQL Server 2005, além de conhecer regras básicas utilizando configurações de desempenho, sem muito esforço em alterações na estrutura do banco de dados.
Introdução
Se você é um DBA iniciante e está procurando uma boa configuração para não ter problemas de performance, ou se é um DBA experiente que conhece várias maneiras de configurar um banco de dados no SQL Server 2000, mas não conhece as novas configurações utilizadas no SQl Server 2005, vale a pena conhecer as principais chaves de configuração listadas neste artigo. No SQL Server 2000 já havia um grande número de configurações que poderiam ser alteradas, um total de 36; mesmo com este grande número, muitos DBAs se achavam impossibilitados de alterar algumas opções do banco de dados que eram desejáveis de configurar. Pensando nisso, a Microsoft resolveu liberar mais 27 novas configurações para o SQL Server 2005, chegando a um total de 63.
Conhecendo as configurações
A maneira mais fácil de se obter uma lista das configurações que o banco de dados possui é executando uma store procedure de servidor chamada SP_CONFIGURE . Esta store procedure irá listar configurações que estão sendo utilizadas atualmente no banco de dados e seu resultado pode variar dependendo de cada servidor ou versão do SQL Server.
Como exemplo, abra o SQL Server Management Studio, clique no botão New Query para ativar o Query Editor, digite e execute o seguinte comando:
EXEC SP_CONFIGURE
Este comando irá listar as seguintes configurações. Figura 1:
Configurações do servidor SQL Server 2005
Resultado da consulta disparada pela store proceure SP_CONFIGURE.Resultado da consulta disparada pela store proceure SP_CONFIGURE.
Repare que para cada configuração, temos cinco colunas de informações:
Name : nome da configuração.
Minimum : valor mínimo para a configuração listada.
Maximum : valor máximo para a configuração listada.
Config_value : valor configurado.
Run_value : valor que está sendo executado. Pode ser que o valor que está sendo executado não seja o valor configurado; isto acontece porque determinadas configurações, quando alteradas, só entrarão em vigor após o servidor ser reiniciado.
Nota: Na maioria das configurações, o valor pode ser definido como 0 (zero) ou 1 (um), que podem significar habilitado ou desabilitado, mas esta regra não se aplica a todos os casos.
Alterando uma Configuração
A store procedure de servidor SP_CONFIGURE, além de exibir as configurações, pode ser utilizada também para alterá-las. A sintaxe para alterar uma configuração é a seguinte:
SP_CONFIGURE [`nome da configuração`], [novo valor a ser configurado]
GO
RECONFIGURE WITH OVERRIDE
GO
[`nome da configuração`] - nome da configuração que deseja alterar. Estes nomes são os mesmos listados na coluna Name quando executamos o comando EXEC SP_CONFIGURE.
[novo valor a ser configurado] - o novo valor a ser configurado. Lembrando que, mesmo utilizando o comando RECONFIGURE WITH OVERRIDE, algumas configurações serão executadas apenas ao reiniciar o servidor.
Como exemplo, vamos alterar a configuração clr enabled para desabilitar o suporte ao CLR (common language runtime) no banco de dados, alterando o valor de 1 para 0. No Query Editor, digite:
SP_CONFIGURE `clr enabled`,0
GO
RECONFIGURE WITH OVERRIDE
GO
Para conferir se o valor 0 foi configurado, podemos executar um comando que traga apenas dados sobre a configuração clr enabled. Neste caso, basta disparar a mesma store procedure, incuindo apenas o nome da configuração que deseja verificar. No Query Editor, digite:
SP_CONFIGURE `clr enabled`
Como resultado, serão exibidas somente as informações desta configuração. Figura 2:
Resultado da consulta a configuração clr enabled.Resultado da consulta a configuração clr enabled.
Configurações importantes
Dentre as configurações listadas através da store procedure de servidor SP_CONFIGURE, vale aqui destacar algumas configurações importantes para o conhecimento e manutenção do banco de dados, as quais permitem obter um ganho considerável em performance. São elas:
1) Recovery interval
Altera o intervalo de recuperação. Se o seu SQL Server é muito ativo, recebendo uma carga enorme de transações, e mantém o monitor de performance em 100% na maior parte do tempo, esta é uma boa configuração a ser alterada. Por padrão, o SQL Server tem o valor 0 nesta configuração, o que significa que o SQL Server terá até 1 minuto para realizar uma recuperação após ter que ser reiniciado, o que pode não ser suficiente. Para este caso, podemos aumentar este intervalo para 5 minutos, como no exemplo abaixo:
exec SP_CONFIGURE `recovery interval`,5
GO
O SQL Server irá solicitar o comando RECONFIGURE para concluir a operação. A sintaxe para este comando é a seguinte:
RECONFIGURE WITH OVERRIDE
GO
2) Network Packet Size
Altera o tamanho padrão de pacotes enviados. O tamanho padrão é de 4096 bytes para cada pacote. Esta configuração pode ser alterada para um valor maior, quando a sua aplicação armazena e trafega com freqüência dados com um grande número de bytes, como por exemplo imagens e longos trechos de texto. Aumentando o número de bytes, temos menos pacotes trafegados e assim aceleramos o tráfego na rede. Por outro lado, se o banco trafega dados em pouca quantidade de bytes, aumentar o tamanho dos pacotes pode ser prejudicial. O exemplo abaixo altera o tamanho do pacote de dados para 6000 bytes:
exec SP_CONFIGURE `network packet size`,6000
GO
RECONFIGURE WITH OVERRIDE
GO
3) Min Memory Per Query
Altera o valor mínimo de memória para cada query. O valor mínimo padrão é 1024k, e pode ser aumentado somente quando o servidor possui muita memória RAM disponível e se existe a certeza de que as queries disparadas ocupam um valor bem maior que 1024kb, ou seja, as chamadas consultas “pesadas”.
O exemplo abaixo altera o valor mínimo por query para 3072k.
exec SP_CONFIGURE `min memory per query`,3072
GO
RECONFIGURE WITH OVERRIDE
GO
4) Max Degree Of Parallelism
Altera o grau máximo de paralelismo. O que é paralelismo? Paralelismo é capacidade do Query Optimizer de usar múltiplas CPUs para executar uma única consulta. Por padrão, o valor 0 indica que o paralelismo está ativado e pode usar os CPUs existentes no servidor. Se você alterar esta definição para 1, o paralelismo é desativado para todas as CPUs. Devemos desativar o pararelismo somente quando for desnecessário o uso de CPUs múltiplos para executar uma consulta.
O exemplo abaixo ativa o pararelismo no servidor, caso ele esteja com o valor 1 (desativado).
exec SP_CONFIGURE `max degree of parallelism`,0
GO
RECONFIGURE WITH OVERRIDE
GO
5) Priority Boost
Altera a prioridade dos processos referentes ao SQL Server no Servidor. Aqui temos uma configuração muito interessante: os processos oriundos do SQL Server terão uma prioridade maior de execução do que os de outros aplicativos no servidor, podendo ser utilizado quando temos a certeza de que existem no servidor outras aplicações rodando além do SQL server, e que sejam aplicações com baixo grau de importância quanto à performance. Apesar de parecer um grande negócio, o ganho em performance é quase imperceptível, mas pode valer a pena nas circustâncias citadas. Não utilize-o quando temos somente o SQL Server rodando no servidor.
O valor padrão é 0, mas pode ser habilitado para 1 e assim definir o grau de prioridade maior. Segue a sintaxe:
exec SP_CONFIGURE `priority boost`,1
GO
RECONFIGURE WITH OVERRIDE
GO
6) Max Server Memory e Min Server Memory
Configuram o valor máximo e mínimo de memória para um servidor SQL, respectivamente. Esta opção pode ser interessante quando, no servidor onde reside o SQL Server, existirem aplicações que em determinados momentos consomem muita memória, deixando pouca memória para o SQL Server. Caso o seu banco de dados seja a única aplicação no servidor, aconselha-se não alterar estas configurações e deixar, por exemplo, o valor padrão 0 para o mínimo de memória, que significa auto-ajustável. O exemplo abaixo altera o mínimo de memória para 2 MB.
exec SP_CONFIGURE `min server memory (MB)`,2
GO
RECONFIGURE WITH OVERRIDE
GO
7) Index Create Memory
Define o tamanho de memória utilizado para criação de índices. O valor padrão de 0 diz para o SQL Server determinar automaticamente o valor ideal a ser utilizado. Em praticamente todos os casos, SQL Server irá configurar a quantidade de memória otimizada. Em outros casos, pode acontecer de um índice ocupar espaço demais em uma tabela e tornar as consultas lentas. Somente se essa ocorrência for verificada, é que torna-se importante definir um espaço em memória para os índices.
O exemplo abaixo fixa o espaço de 2048k em memória para criação de índices.
exec SP_CONFIGURE `index create memory`, 2048
GO
RECONFIGURE WITH OVERRIDE
GO
Nested Triggers
Configura o uso ou não de triggers aninhados. Por padrão é configurada a opção 1, que significa que o uso de triggers aninhados está ativo. Sabemos que eles consomem uma boa parte do processamento de uma transação, então caso deseje que os desenvolvedores não use estes tipos de triggers, basta setar esta configuração para o valor 0. Lembre-se que caso optar por desabilitar este tipo de trigger, você estará ganhando em performance, porem pode prejudicar a lógica e flexibilidade da aplicação. A sintaxe para desativar triggers aninhados é a seguinte:
exec SP_CONFIGURE `nested triggers`, 0
GO
RECONFIGURE WITH OVERRIDE
GO
9) Query Governor Cost Limit
Configura o limite de custo de uma consulta. Esta opção é uma das poucas que normalmente sofrem alterações por parte dos DBAs, pois permite que seja gerenciado o tempo em segundos para uma consulta ser disparada. O valor padrão para essa configuração é 0, o que significa que não há limites para quanto tempo uma consulta pode ser executada. Deixar o banco de dados sem limite de tempo para consultas pode ser perigoso para consultas lentas, principalmente quando há diversos usuários simultâneos acessando o banco de dados. Por outro lado, com um tempo em segundos limitado, os desenvolvedores serão levados a criar consultas mais rápidas. O exemplo a seguir configura o tempo de consultas para 100 segundos.
exec SP_CONFIGURE `query governor cost limit`, 100
GO
RECONFIGURE WITH OVERRIDE
GO
Configurações Avançadas
O SQL Server 2005 é um SGBD onde a maioria das configurações já vêm ajustadas para se obter a melhor performance possível. Elas podem ser alteradas conforme a regra de cada negócio, mas no geral é aconselhável que se mantenha o valor padrão já configurado no banco. As configurações avançadas só permitem serem alteradas quando a configuração show advanced options estiver setada com o valor 1. Segue a listagem de algumas configurações onde na maioria dos casos aconselha-se a manter o valor padrão:
1) Awe Enabled
Esta é uma configuração utilizada somente caso o SQL Server 2005 esteja rodando em um ambiente de 32 bits. Se o servidor onde o SQL Server reside possui 4GB de memória RAM ou menos que isto, então aconselha-se manter o valor padrão, que é 0 (zero), fazendo com que a memória AWE (Advanced Windowing Extensions) não seja utilizada.
A API AWE permite executar aplicações que foram desenvolvidas para usar mais de 4 GB de memória RAM no Windows 2003 Enterprise Server ou Windows 2003 Datacenter Server. Lembrando que se o sistema operacional for Windows 2003 Enterprise Server, tanto o SQL Server 2005 Standard como o Enterprise Editions podem usar até 32 GB de memória RAM. Se o sistema operacional for Windows 2003 Datacenter Server, estes dois SGBDs podem utilizar até 64GB de memória RAM.Porque não alterar: Atenção nesta configuração, porque ela pode possuir valores configurados de formas diferentes para cada versão do SQL Server e também do sistema operacional. Para aplicações de pequeno e médio porte, onde não seja necessário o uso de mais de 4GB de mamória RAM, aconselha-se utilizar o valor 0.
2) Affinity I/O Mask
A Affinity I/O Mask permite que possamos configurar um conjunto de CPUs multiprocessadas para um servidor. O valor a ser configurado depende do número de CPUs a serem utilizadas, conforme a tabela a seguir. Tabela 1:
Valor Descrição
Opções da configuração Affinity I/O Mask.Opções da configuração Affinity I/O Mask.
Porque não alterar: como padrão, esta configuração vem armazenada com o valor 0 (zero). Este valor significa que o SQL Server está programado para ser executado em qualquer número de CPUs disponíveis.
3) Affinity Mask
Se o Affinity I/O Mask informa o número total de CPUs utilizadas no servidor, é o Affinity Mask que define quais processadores o SQL Server usará, podendo por exemplo escolher 4 entre 6 processadores disponíveis. Isto pode ser interessante em servidores que rodam múltiplas instâncias do SQL Server ou outros aplicativos, onde você pode definir que processador utilizar para cada aplicativo.
Porque não alterar: não é interessante alterar o Affinity Mask caso você tenha somente o SQL Server rodando em um servidor. O valor padrão para Affinity Mask é 0 (zero); isto permite utilizar um algoritmo do sistema operacional específico para determinar que thread executar em que CPU, e quando mover um thread de uma CPU para outra CPU.
Conclusão
Alterar as configurações do SQL Server pode ser uma ótima maneira para obter um desempenho, porém requer um considerável conhecimento da estrutura e funcionamento do banco de dados. Isto é bom, já quando conhecemos estas configurações podemos utilizá-las para adaptar a cada negócio; mas também pode ser perigoso, pois uma série de recursos que foram por default configurados para uma boa performance podem ser alterados de forma errada, gerando gravíssimos problemas no banco de dados. O ideal é realizar as alterações de configuração em um servidor teste e, caso haja sucesso, implementá-las no banco de dados em produção.
