BOAS PRÁTICAS DE CONFIGURAÇÃO DE MEMÓRIA E PROCESSADOR NO SGBD MICROSOFT SQL SERVER 2008 R2

17 

Texto

(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 (rogerioces@gmail.com).

2

DBA, Mestre em informática e professor do Centro Universitário Newton Paiva (iremar.prof@uol.com.br).

(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.

(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.

Imagem

Referências

temas relacionados :