1
BOAS PRÁTICAS DE CONFIGURAÇÃO DE MEMÓRIA E PROCESSADOR
NO SGBD MICROSOFT SQL SERVER 2008 R2
ROGÉRIO CESAR M. DA CONCEIÇÃO1
IREMAR NUNES DE LIMA 2
Resumo: Este artigo analisa os detalhes de configuração de memória e processador no
Sistema Gerenciador de Banco de Dados Microsoft SQL Server 2008 R2. São
identificadas as boas práticas para um DBA aplicar na configuração de memória e
processador para uma instância SQL Server 2008 R2.
Palavras-chave: Banco de Dados, Microsoft SQL Server, Configuração, Memória,
Processador.
1. INTRODUÇÃO
O Microsoft SQL Server é um dos SGBDs (Sistema Gerenciador de Banco de Dados)
mais conhecidos e importantes do mercado atualmente. Ele vem melhorando a cada
nova versão, e a última, a 2008 R2, veio consolidar suas vantagens e oferecer novos
recursos aos DBAs (Database Adminstrators).
1 Especialista em Banco de Dados e Business Intelligence ([email protected]). 2 DBA, Mestre em informática e professor do Centro Universitário Newton Paiva ([email protected]).
2
Um grande número de DBAs instalam o SGBD sem fazer alterações das configurações
padrões, pois estas configurações são suficientes para um grande número de situações.
Mas em alguns cenários é importante fazer ajustes de forma a tirar o máximo proveito
da capacidade do servidor. Este é o tema deste artigo, onde serão explicadas algumas
boas práticas em termos de configuração de memória e CPU, com a análise das
vantagens e desvantagens de cada uma.
2. CONFIGURAÇÃO DE MEMÓRIA E PROCESSADOR NO SQL Server
2.1 Por que os DBAs devem avaliar a necessidade de se fazer alterações na
configuração padrão do SQL Server 2008 ?
Um DBA deve avaliar se vale a pena alterar as configurações do SQL Server 2008 para
melhoria de desempenho da aplicação. Podem-se evitar determinados tipos de
contenções e incidentes no Banco de Dados relacionados a particularidades das
aplicações.
Um ponto importante a ser considerado: sempre antes de implementar uma mudança,
utilize um change log (lista de mudanças). Faça as anotações de como eram as
configurações antes e como ficaram após as modificações. Verifique o impacto das
modificações, e se a mudança trouxer uma piora na qualidade, desfaça e procure outra
alternativa. Faça isto num ambiente de teste: só depois que houver a certeza da melhora
obtida aplica-se em produção.
2.2 Boas práticas para gerenciamento de memória
Na versão 32 bits, a memória disponível ao SQL Server é limitada a 2 GB. É possível
alterar esta configuração como será mostrado na seção 2.2.1. Na versão 64 bits não
existe esta limitação.
3
2.2.1. SQL Server 2008 em plataformas 32 bits
Sistemas em 32 bits tem como limitação aceitar um máximo de 4 GB de memória RAM
(isto não é exclusivo do SQL Server, mas da plataforma 32 bits). Dos 4 GB de memória
RAM máximo que o servidor possui, 2 GB são reservados para o Windows. Com isso
ele deixa o SQL Server 2008 com somente 2 GB de RAM. Quem optar por uma edição
do SQL Server 2008 em 32 bits deve fazer alguns ajustes para conseguir liberar mais
memória a ele. Um destes ajustes é utilizar a opção /3GB ou usar as extensões de janela
de endereço (AWE) com a opção /PAE. No Windows Server 2003, pode-se alterar o
arquivo boot.ini e utilizar a opção /3GB, deixando o Windows com 1 GB de memória
RAM. Com isso o SQL Server consegue utilizar até 3GB de memória RAM. No
Windows Server 2008 32 bits, esta mesma opção é feita de outra forma, através do
comando BCDEdit com a opção increaseuserva, usando um parâmetro opcional que vai
determinar o tamanho do espaço do usuário disponível, como 3GB.
Se o servidor de 32 bits possuir mais de 4 GB de memória RAM, é possível fazer outros
ajustes, utilizando a opção /PAE. A “Intel introduziu pela primeira vez as Extensões de
Endereço Físico (PAE s) de 36 bits no Pentium Pro na década de 1990. Os 4 bits extras
permitem que os aplicativos adquiram memória física acima de 4GB (até 64GB) como
memória não paginada mapeados dinamicamente no espaço de endereço de 32 bits”.
(College, 2010, tradução do autor). Como se observa, é possível utilizar mais de 4 GB
de RAM em plataformas de 32 bits. Ela é acionada no Windows Server 2003 da mesma
forma que a opção /3Gb, no boot.ini, usando a opção /PAE. E no Windows Server 2008
com o comando BCDEdit e opção /PAE. Ativando o /PAE, é possível configurar o
AWE permitindo aumentar o acesso à memória. Uma forma de habilitar o AWE é
usando o comando sp_configure (figura 01) ou através da janela Propriedades do
servidor no SQL Server Management Studio. Para isso, deve-se de dentro do SQL
4
Server Management Studio, clicar com o botão direito em cima da sua instância, e
selecionar Properties. Em seguida, clicar no item Memory.
Mas como sempre, uma prática tem suas vantagens e desvantagens. As desvantagens
são que a memória acima de 4GB acessada usando PAE /AWE só pode ser usada pelo
data cache do SQL Server. O cache de procedimento (procedure cache), utilizado para
os planos compilação de consulta, não é capaz de tirar proveito dessa memória; os
componentes do Analysis Services e do Integration Services não são capazes de utilizar
a memória acessada usando PAE/AWE; e por último, pode haver sobrecarga no espaço
de memória mapeado pelo AWE em sistemas 32 bits.
Figura 01: Usando o comando sp_configure para reconfigurar o valor máximo de memória para o SQL. Fonte: Próprio autor
Um ponto importante a ser observado é que em plataformas 32-bit com o sistema AWE
habilitado, a conta do serviço que executa o serviço do SQL Server deve ter o direito de
usar lock pages (bloquear páginas) na memória. Como conseqüência, a memória AWE
não é paginada para disco pelo sistema operacional.
Essas são as considerações sobre boas práticas para utilizar o SQL Server 2008 em
plataformas de 32 bits. Em plataformas de 64 bits, estas limitações não ocorrem, como
será visto seguir.
5
2.2.2. SQL Server 2008 em plataformas 64 bits
Na plataforma 64 bits, muitos dos problemas que o SQL Server 2008 encontra em
plataforma 32 bits não ocorrem; inclusive ela é a recomendada caso o servidor possua
mais de 4 GB de memória RAM, pois não somente a instância SQL Server 2008 pode
usar mais memória como também todos os seus componentes, como o Analysis
Services e Integration Services sem precisar fazer alterações.
Existe uma boa prática para SQL Server 2008 em ambientes 64 bits: definir o lock
pages (bloqueio de páginas) direto na memória. Ele tem a vantagem de impedir que o
Windows faça paginação de memória fora do SQL Server. Sem esta configuração
habilitada, certas ações, tais como cópias de arquivos grandes podem levar a um
aumento de paginação no Windows. Isso pode levar uma redução significativa no
desempenho do SQL Server.
2.2.3. Boas práticas de configuração de memória em plataformas de 32 ou 64 bits
As boas práticas descritas a seguir podem ser aplicadas indiferentes da plataforma
escolhida do SQL Server 2008, seja 32 ou 64 bits. Elas são baseadas nos parâmetros de
configuração descritos na figura 02 a seguir. Para se chegar nesta figura deve-se dentro
do SQL Server Management Studio, clicar com o botão direito em cima da sua
6
instância, e selecionar Properties. Em seguida, clicar no item Memory.
Figura 02: Opções de configuração da memória do SQL Server 2008 Fonte: Próprio autor
Conforme pode ser visto é possível definir os valores mínimo e máximo de memória a
ser utilizado pelo SQL Server. Isto é importante porque quando o SQL Server 2008
inicia, ele adquire memória até ficar pronto para uso e vai liberando para outros
processos do sistema operacional conforme não for mais necessário. O valor mínimo é o
valor em que o SQL Server pára de liberar memória para o sistema operacional, e o
valor máximo é o valor máximo de memória que ele pode adquirir. O padrão de valor
mínimo de memória é 0 e o máximo é 2.147.483.647 bytes. Devem-se fazer os ajustes
de acordo com as características da aplicação e outros recursos instalados no servidor.
Com estes valores definidos, tem-se mais segurança para que o SQL Server não fique
7
com pouca memória disponível nem que fique com mais memória do que o necessário,
prejudicando as outras aplicações no servidor que necessitem de mais memória.
O parâmetro denominado lock pages, já citado anteriormente, define o valor de
memória máxima que o SQL Server irá consumir. Se não for definido o valor máximo
deste parâmetro, quando o banco de dados estiver sob uma carga enorme, ele irá
consumir o tanto de memória RAM que o SO liberar, podendo deixar outras aplicações
do servidor sem memória disponível inclusive deixando o próprio servidor instável.
Para acioná-lo, é preciso clicar em iniciar, executar e digitar gpedit.msc. Na caixa de
diálogo Group Policy, expanda Computer Configuration, e em seguida expanda
Windows Settings. Então expanda Security Settings, e depois expanda Local Policies.
Selecione a pasta User Rights Assignment. No Painel de detalhes são exibidas as
diretivas. Neste painel, clique duas vezes em Lock pages in memory. Em seguida na
caixa de diálogo Local Security Policy Setting, clique em adicionar. Na caixa de diálogo
Select Users or Groups, adicione uma conta com privilégios de execução para o
sqlservr.exe. Um ponto importante: em sistemas 32 bits, configurar este privilégio sem
usar o AWE pode prejudicar significativamente o desempenho do sistema.
Uma boa prática com relação aos valores mínimo e máximo de memória é definir estes
valores para cada instância que o servidor possuir. Se não for definido, e a primeira
instância solicitar mais memória, ela poderá prejudicar o funcionamento e desempenho
das outras instâncias.
Outro parâmetro mostrado na figura 02 refere-se ao index creation memory. Ele
controla a quantidade de memória alocada para ordenações na criação de índices. Esta
opção é auto-configurável, mas pode-se defini-la manualmente para ganho no tempo de
8
execução, caso tenha um cenário de criação massiva de índices em tabelas grandes do
banco de dados.
Por último, o parâmetro minimun memory per query, define quanto de memória
(mínimo) o servidor vai disponibilizar para cada consulta. É possível definir um valor
no intervalo de 512 bytes até 2 Gigabytes. O padrão é 1.024 kb. Aumentando o valor
poderá melhorar o desempenho de algumas consultas. Mas, em contrapartida poderá
levar a uma maior competição para obter os recursos da memória. Cabe ao DBA avaliar
como estão tops SQL da instância que necessitam de mais recursos de memória, para
ajustar um valor que ajude no desempenho.
Uma última recomendação com relação ao gerenciamento de memória no SQL Server é
quanto de memória disponibilizar para a instância. Normalmente o DBA quer dar tudo
que for possível, mas ele precisa verificar se existem outras aplicações que consomem
recursos do servidor. Algumas delas são o próprio Windows, os drivers para cartões e
unidades de fitas, o antivírus e o sistema de backup, entre outros. Dependendo das suas
características eles podem consumir uma grande quantidade de memória. Por isso, deve-
se levantar quanto de memória estes softwares precisam e então dividir entre ele e o que
será possível dar de memória para o banco de dados. Não existe um “valor mágico”: o
DBA deve determiná-lo de acordo com os recursos que ele possui, as aplicações que
utiliza e o trabalho que o banco de dados fará.
Uma novidade da versão SQL Server 2008 R2 com relação a memória foi a
possibilidade de inclusão de memória “a quente” no servidor. Ela possibilita incluir
mais memória no servidor do SQL Server 2008, sem precisar reiniciá-lo. Deve-se ficar
atento ao fato de que é necessário um hardware especial fornecido pelo fabricante.
2.3 Boas práticas para gerenciar a CPU
9
Quando o SQL Server é iniciado ele cria um processo no sistema operacional. Mas
como ele precisa suportar centenas e até milhares de requisições, o SQL Server usa o
conceito de threads. As threads são usadas para melhor organizar as tarefas que o SQL
Server tem que fazer, e também para ganhar em desempenho. Se uma thread está
esperando algo para então fazer a sua ação, ele pode utilizar outras threads para fazer
outras tarefas e assim garantir o desempenho da aplicação. E é possível fazer alguns
ajustes nas configurações para que as threads possam ser mais eficientes.
Uma alteração possível é usar a opção Boost SQL Server Priority, o nível de prioridade
da thread do SQL Server. O padrão normal é sete, e esse valor garante que as threads
são atribuídas e executadas sem causar problemas de estabilidade no servidor. A opção
Boost SQL Server Priority altera o nível de prioridade para treze, o que garante que as
threads do SQL Server executarão com mais prioridades que as outras do sistema
operacional. Mas isso pode trazer diversos problemas de estabilidade. Um caso em que
esta configuração pode ser útil é num ambiente de teste de carga com melhorias no
desempenho. Em geral a configuração padrão é recomendada. Para se chegar até ela,
dentro do SQL Server Management, clique com o botão direito em cima da sua
instância e selecione Properties. Então escolha a opção Processors. Ela contém o item
Boost SQL Server priority, permitindo marcá-lo ou desmarcá-lo, conforme mostrado na
figura 03 abaixo.
10
Figura 03: Opções de configuração da CPU do SQL Server 2008 Fonte: Próprio autor
Outra opção que pode ser ajustada é o pooling de threads, que define o máximo de
threads trabalhando simultaneamente. Esta funcionalidade gerencia as threads conforme
a necessidade da instância. Esta opção fica dentro do item processors, de nome:
Maximum worker threads. O valor padrão para ela é 0, o que permite o SQL Server
definir o pool de threads automaticamente conforme o número de CPUs e se o sistema é
de 32 ou 64 bits. O valor em um ambiente de 1 a 4 CPUs, em 32 bits é 256; em 64 bits,
512. Isto varia conforme o número de CPUs aumenta, chegando até 32 CPUs com 480
threads em 32 bits e 960 threads em sistema de 64 bits. O usuário pode alterar o valor
máximo para até 1024 em sistemas 32 bits e 2048 em sistemas 64 bits. Mas isso tem
uma conseqüência, porque quanto maior o número de threads, mais memória precisa ser
11
reservada, consumindo mais recursos, e por isso deve-se ficar atento à estabilidade do
servidor.
Pode-se também habilitar o recurso de modos de fibras (fiber mode), também conhecido
como lightweight pooling. Essa configuração vai permitir o SQL Server criar fibras em
vez de threads. A fibra é uma versão leve de uma thread que é capaz de alternar o
contexto em modo de usuário ao invés do modo kernel. Esta opção pode ser bastante
interessante em servidores com um grande número de CPUs perto de sua capacidade: o
desempenho pode aumentar se esta opção estiver habilitada. Novamente, vale o aviso de
fazer vários testes no ambiente com relação à estabilidade.
Uma característica que deve ser utilizada somente se for estritamente necessário, e em
casos bem específicos, pois a própria Microsoft diz que nas próximas versões do SQL
Server ela será removida. A prática consiste em configurar as threads para que sejam
executadas por determinada CPU. Esta opção se chama máscara de afinidade de CPU.
Isto pode ser interessante em casos que é preciso reservar recursos para uma
determinada instância, que compartilha o SQL Server com outras no servidor. Isto evita
que uma determinada instância consuma todo o servidor, deixando as outras instâncias
sem recursos. Ela pode ser ativada com a procedure de sistema sp_configure ou através
do SQL Server Management, clicando com o botão direito em cima da sua instância e
selecionando Properties. Então escolha a opção Processors. Ela contém o item
Automatically set processor affinity mask for all processors, permitindo marcá-lo ou
desmarcá-lo. Isto é possível nos servidores Windows 2000 e 2003. No Windows 2008,
ela está desabilitada e não é possível acioná-la (provavelmente porque como foi dito
anteriormente, as próximas versões do SQL Server não irão mais contemplá-las).
12
Outra configuração possível é alterar o grau máximo de paralelismo (MAXDOP). Esta
funcionalidade controla o número máximo de CPUs que pode ser usado na execução de
uma única tarefa. Isto tem as suas vantagens, como por exemplo, uma grande consulta
pode ser dividida em partes diferentes, com cada parte executando threads em CPUs
separadas (paralelismo de consultas). Uma outra vantagem é seu uso em sistemas
OLAP, pelo fato das consultas serem maiores. Com isso, dividindo as consultas em
partes menores e distribuindo-as em vários CPUs, com cada CPU executando uma
parte, é possível utilizar mais recursos para aumentar o desempenho das consultas
grandes. Por padrão, o SQL Server decide isto quando estiver compilando cada
consulta, se vai utilizar uma consulta paralela através do MAXDOP ou não, quando o
valor d MAXDOP é 0. É possível alterar o seu valor para 1, que vai desabilitar as
consultas paralelas, ou a um número que seja até o número de CPUs disponíveis no
servidor. Um ponto a ser dito com relação a MAXDOP com valor 1 é que algumas
operações, como recriar índices se beneficia muito do paralelismo. Mas elas são
incapazes de fazê-lo se o MAXDOP está configurado com valor 0. O usuário terá que
analisar as situações para definir o melhor valor de MAXDOP. O máximo grau de
paralelismo pode ser definido através do comando sp_configure ou através do SQL
Server Management Studio.
Pode-se definir o custo limite para paralelismo, deixando o MAXDOP no padrão ou
num valor maior que 1. O SQL Server vai calcular o custo limite para o paralelismo.
Isto representa o tempo (em estimativa) que a consulta precisa para ser executada em
série em uma única CPU. O valor padrão é 5 segundos, e estimativas de consultas que
demoram mais do que isso serão consideradas para o paralelismo. Para alguns casos,
aumentar este valor é a melhor situação para configurar o MAXDOP quando se tem
muitas consultas paralelas indesejadas.
13
Uma novidade da versão SQL Server 2008 R2 com relação a processadores foi o
aumento no suporte de processadores lógicos, passando dos 64 da ultima versão para até
256.
3. ANALISANDO O SP_CONFIGURE
Como foi citado algumas vezes neste artigo, vamos dar uma olhada melhor no comando
sp_configure. Ele é um comando onde o usuário pode executar modificações na sua
instância, configurando memória, servidor, as consultas e outros ajustes. Rodando o
comando sp_configure sem parâmetros, o SQL Server exibirá apenas algumas
configurações. O padrão dele é esconder algumas opções mais avançadas. Mas como
mostrado na figura 04 abaixo pode-se visualizar todas as configurações.
Figura 04: Executando sp_configure para exibir todas as opções de configuração Fonte: Próprio autor
Existem 65 registros que possibilitam fazer diversas modificações na instância. As que
interessam neste artigo são os registros que configuram memória e CPU (processors).
São os registros:
14
• awe enabled
• max degree of parallelism
• max server memory (MB)
• max worker threads
• min memory per query (KB)
• min server memory (MB)
Por exemplo, vamos alterar a quantidade de memória mínima do servidor (opção min
server memory (MB)). Na figura 05 o valor dela está como 0, tanto na coluna
config_value quanto na run value.
Figura 05: Opção min server memory (MB) está com o valor 0 Fonte: Próprio autor
Executando EXEC sp_configure 'min server memory (MB)', '400', é definido a memória
mínima disponível para a instância é 400 megabytes. O SQL server exibe a mensagem:
"Configuration option 'min server memory (MB)' changed from 0 to 400. Run the
RECONFIGURE statement to install." Repare que mesmo que se tenha executado o
comando sp_configure fazendo a definição da nova quantidade mínima de memória, ele
avisa que se o comando RECONFIGURE não for executado a alteração feita não entrou
15
em vigor. Apenas a coluna config_value contém o valor 400, a coluna run_value
continua com o valor 0. A figura 06 mostra exatamente esta diferença.
Figura 06: Valor da coluna run_value Fonte: Próprio autor
Após executar o comando RECONFIGURE, a modificação entra em vigor. Veja a
figura 07.
Figura 07: Comando RECONFIGURE Fonte: Próprio autor
16
Um ponto importante é que nem todas as modificações entram em funcionamento após
ser executado o comando RECONFIGURE. Para algumas, é necessário reiniciar o
serviço do SQL Server. Para saber quais são elas consulte a tabela sys.configurations.
Nela existe o campo is_dynamic. Se o valor deste campo for igual a 0, as modificações
só entram em vigor após o reinício do serviço do SQL Server. E ainda, se o serviço for
reiniciado antes de ter executado o comando RECONFIGURE, a modificação também
não entrará em vigor. Por isso, é preciso executar o comando RECONFIGURE e depois
reiniciar o SQL Server para determinadas configurações.
4. CONCLUSÃO
As configurações de memória e processador devem ser usadas conforme a necessidade
do usuário, e devem-se verificar as suas conseqüências. Os ajustes padrão do SQL
Server atendem a um grande número de situações e só devem ser alteradas caso o DBA
tenha consciência das suas vantagens e as implicações que as mesmas podem causar na
aplicação e na instância.
17
REFERÊNCIAS
COLLEDGE, Rod. SQL Server 2008 Administration in Action. United States of America: Manning Publications Co., 2010. 466 p.
MSDN, Microsoft. Sp_configure (Transact-SQL). Disponível em: <http://msdn.microsoft.com/pt-br/library/ms188787.aspx>. Acesso em: 19 abril 2011.
MSDN, Microsoft. Opção index create memory. Disponível em: <http://msdn.microsoft.com/pt-br/library/ms175123.aspx>. Acesso em 19 abril 2011.
MSDN, Microsoft. Opção max worker threads. Disponível em: <http://msdn.microsoft.com/pt-br/library/ms187024.aspx>. Acesso em 19 abril 2011.
MSDN, Microsoft. Opção de máscara de afinidade. Disponível em: <http://msdn.microsoft.com/pt-br/library/ms187104.aspx>. Acesso em 19 abril 2011.
MENDES. Silas. Configurar instância SQL via script. Disponível em: <http://silasmendes.com/dba/tag/sp_configure>. Acesso em 22 abril 2011.
MSDN, Microsoft. Inclusão de Memória a Quente. Disponível em: <http://msdn.microsoft.com/pt-br/library/ms175490%28v=SQL.100%29.aspx>. Acesso em 22 abril 2011.
MSDN, Microsoft. How to: Enable the Lock Pages in Memory Option (Windows). Disponível em: <http://msdn.microsoft.com/en-us/library/ms190730.aspx>. Acesso em 22 abril 2011.