Considerações sobre Performance no SQL Server 2008 R2

Todas as opções relacionadas abaixo podem ser ajustadas através das propriedades da instância ou através do comando sp_configure.

O componente gerenciador de memória do Microsoft SQL Server elimina a necessidade de gerenciamento manual da memória disponível para o SQL Server. Quando o SQL Server é iniciado, ele determina, dinamicamente, quanta memória alocar, de acordo com a quantidade de memória utilizada atualmente pelo sistema operacional e outros aplicativos. Assim como muda a carga no computador e no SQL Server, a memória alocada também muda. Para obter mais informações, consulte Arquitetura de memória.

Podem ser usadas as seguintes opções de configuração de servidor para configurar o uso de memória e influir no desempenho do servidor:

  • min server memory
  • max server memory
  • max worker threads
  • index create memory
  • min memory per query

A opção de configuração de servidor min server memory pode ser usada para garantir que o SQL Server não libere memória abaixo da memória de servidor mínima configurada quando o limite for atingido. Esta opção de configuração pode ser definida como um valor específico, de acordo com o tamanho e a atividade do SQL Server. Se decidir definir este valor, especifique um valor razoável para garantir que o sistema operacional não solicite memória demais a partir do SQL Server, o que pode influir no desempenho do SQL Server.

A opção de configuração max server memory pode ser usada para especificar a quantidade máxima de memória que o SQL Server pode alocar ao ser iniciado e durante sua execução. Essa opção de configuração pode ser definida como um valor específico, caso você tenha ciência de que há vários aplicativos em execução ao mesmo tempo como o SQL Server e queira garantir que eles tenham memória suficiente para execução. Se esses outros aplicativos, como servidores Web ou de email, solicitarem memória apenas conforme a necessidade, não defina a opção de configuração de servidor max server memory, pois o SQL Server já libera memória conforme a necessidade para eles. Muitas vezes, contudo, os aplicativos usam qualquer memória que esteja disponível ao serem iniciados e não solicitam mais, quando necessário. Se um aplicativo que se comporta dessa maneira estiver em execução no mesmo computador e ao mesmo tempo que o SQL Server, defina a opção de configuração de servidor max server memory com um valor que garanta que a memória solicitada pelo aplicativo não seja alocada pelo SQL Server.

Não defina as opções de configuração de servidor min server memory e max server memory com o mesmo valor, tornado fixa, assim, a quantidade de memória alocada para o SQL Server. A alocação dinâmica de memória produz o melhor desempenho global no decorrer do tempo. Para obter mais informações, consulte Opções de memória do servidor.

No SQL Server, é possível usar a API AWE (Address Windowing Extensions) para fornecer acesso à memória física em excesso dos limites definidos em uma memória virtual configurada. A quantidade específica de memória que você pode usar depende da configuração de hardware e do suporte do sistema operacional. A quantidade de memória física suportada aumentou com a introdução dos sistemas operacionais MicrosoftWindows Server 2003. A memória física acessível pelo recurso AWE, portanto, depende do sistema operacional em uso.

  • O Windows Server 2003, Standard Edition, suporta até 4 GB de memória física.
  • O Windows Server 2003, Enterprise Edition, suporta até 32 GB de memória física.
  • O Windows Server 2003, Datacenter Edition, suporta até 64 GB de memória física.

O recurso AWE não é necessário e não é configurado nos sistemas operacionais de 64 bits.

A opção de configuração de servidor max worker threads pode ser usada para especificar o número de threads utilizados para dar suporte a usuários conectados ao SQL Server. O valor padrão para max worker threads, 0, permite ao SQL Server configurar automaticamente o número de threads de trabalho ao iniciar. Essa configuração é melhor para a maioria dos sistemas; porém, dependendo de sua configuração de sistema, definir max worker threads como um valor específico, às vezes, melhora o desempenho.

O valor padrão para max worker threads, 0, permite ao SQL Server configurar automaticamente o número de threads de trabalho ao iniciar. Essa configuração é melhor para a maioria dos sistemas; porém, dependendo de sua configuração de sistema, definir max worker threads como um valor específico, às vezes, melhora o desempenho.

A tabela a seguir mostra o número configurado automaticamente de máximo de threads de trabalho para várias combinações de CPUs e versões do SQL Server.

Número de CPUs

Computador de 32 bits

Computador de 64 bits

<= 4 processadores

256

512

8 processadores

288

576

16 processadores

352

704

32 processadores

480

960

Cuidado

Nós recomendamos 1024 como o máximo para o SQL Server de 32 bits.


No SQL Server, a opção index create memory controla a quantidade máxima de memória alocada inicialmente para criar índices. Se mais tarde for preciso mais memória para criação de índice e a memória estiver disponível, o servidor irá usá-la, excedendo assim a configuração dessa opção. Se a memória adicional não estiver disponível, a criação de índice continuará usando a memória já alocada.

Devido à introdução de tabelas e índices particionados no SQL Server 2005, os requisitos mínimos de memória para criação de índice podem aumentar significativamente no caso de índices particionados não alinhados e um alto grau de paralelismo. Iniciando com o SQL Server 2005, essa opção controla a quantidade inicial total de memória alocada para todas as partições de índice em uma única operação de criação de índice. A consulta terminará com uma mensagem de erro se a quantidade definida por essa opção for inferior ao mínimo exigido para a execução da consulta.

A opção index create memory é autoconfigurável e normalmente opera sem necessidade de ajustes. Porém, se você tiver dificuldade para criar índices, considere o aumento do valor dessa opção de seu valor de execução.

O valor padrão dessa opção é 0 (autoconfigurável).

Use a opção query wait para especificar o tempo em segundos (de 0 a 2147483647) que uma consulta deve esperar por recursos antes de o tempo limite ser excedido. Se o valor padrão -1 for usado, ou se –1 for especificado, o tempo limite será calculado como 25 vezes o custo estimado da consulta.

No Microsoft SQL Server, consultas que solicitam muita memória (como as que envolvem classificação e hash) são enfileiradas quando não há memória disponível executar a consulta. O tempo limite da consulta é atingido após um tempo definido calculado pelo SQL Server (25 vezes o custo estimado da consulta) ou o tempo especificado pelo valor não negativo da espera da consulta.

Use a opção min memory per query para especificar a quantidade mínima de memória (em kilobytes (KM)) que será alocada para a execução de uma consulta. Por exemplo, se min memory per query for definida como 2.048 KB , a consulta terá a garantia de obter no mínimo esse total de memória. Você pode definir min memory per query como qualquer valor entre 512 e 2.147.483.647 bytes (2 gigabytes (GB)). O padrão é 1.024 KB.

O processador de consulta do SQL Server tenta determinar a quantia ideal de memória que será alocada para uma consulta. A opção min memory per query deixa o administrador especificar a quantia mínima de memória que qualquer consulta única recebe. Consultas geralmente receberão mais memória que isto se elas tiverem operações hash e de ordenamento num volume grande de dados. Aumentar o valor de min memory per query pode melhorar o desempenho para algumas consultas de tamanho pequeno a médio, mas fazer isso poderia conduzir a uma maior competição por recursos de memória.

Use a opção recovery interval para definir o número máximo de minutos por banco de dados necessário para o Microsoft SQL Server recuperar bancos de dados. Cada vez que uma instância do SQL Server se inicia, ela recupera cada banco de dados, revertendo transações não confirmadas e efetuando roll forward em transações confirmadas, mas cujas alterações ainda não foram gravadas em disco quando uma instância do SQL Server parou. Essa opção de configuração define um limite superior no tempo necessário para recuperar cada banco de dados. O padrão é 0, indicando a configuração automática pelo SQL Server. Na prática, isso significa um tempo de recuperação inferior a um minuto e um ponto de verificação a cada minuto aproximadamente para bancos de dados ativos.

A opção recovery interval controla quando o SQL Server emite um checkpoint em cada banco de dados. Os checkpoints são realizados por banco de dados. Em um checkpoint, o SQL Server assegura que todas as informações de log e todas as páginas modificadas sejam liberadas da memória para o disco. Isso limita o tempo necessário para recuperação, limitando o número de roll forwards para assegurar que elas estão no disco. As modificações anteriores ao ponto de verificação não precisam ter roll forward efetuado porque elas já foram liberadas para o disco no ponto de verificação.

A freqüência de pontos de verificação em cada banco de dados depende da quantidade de modificações de dados realizadas e não de medidas baseadas em tempo. Um banco de dados usado principalmente em operações somente leitura não terá muitos checkpoints. Um banco de dados de transação terá pontos de verificação freqüentes.

Mantenha o recovery interval definido como 0 (auto-configuração) a menos que você note que os pontos de verificação prejudicam o desempenho porque ocorrem muito freqüentemente. Se for esse o caso, tente aumentar o valor aos poucos.

Referências:

http://msdn.microsoft.com/en-us/library/ms188284.aspx

http://msdn.microsoft.com/pt-br/library/ms190731.aspx

http://msdn.microsoft.com/pt-br/library/ms175123.aspx

http://msdn.microsoft.com/pt-br/library/ms181047.aspx

http://msdn.microsoft.com/en-us/library/ms189892.aspx

Sobre Viviane Ribeiro

Data Lover. Seasoned sales/technical professional, author, community champion, technical trainer and public speaker with more than 15 years of experience in IT positions where last 11 years were related to Business Intelligence, BIG Data, Cloud and Database technologies (Oracle, PostgreSQL and SQL Server). She has been responsible for successfully creating roadmaps, designing, implementing and managing complex technology solutions for high profile customers.

  1. Bom post, quase fiz algo bem parecido semana passada, mas uns problemas do nada me atrapalharam, anyway, parabéns…obs: vi o.post pelo rt do laerte…..

  2. Olá Viviane,
    Nunca tinha visto um post com tantas informações. Performace sempre é um lado complicado de escrever, pois sempre o melhor desempenho\configuração do seu software vai depender do seu hardware.
    Alguns pontos gostaria de observar;

    A API AWE não é um recursos exclusivo para ambiente 32bits com mais de 4Gb de memoria. Em ambientes x64 vc não precisa especificar nas configurações do SQL Server que quer utilizar AWE, porem como é um recursos do windows e não do SQL Server basta vc especificar o “Lock pages” que seu processo estará utilizando API AWE. ou seja API AWE é altamente necessario em ambientes X64, porem o seu uso é transparente.

    Excelente post e com boa quantidade e qualidade de informações;

    • Oi Leivio, obrigada pelo complemento no post… na realidade é justamente isso que procurei observar no post… que nas configurações do SQL Server para ambientes 64 bits não é necessário ativar o AWE através do sp_configure. Normalmente o pessoal fica em dúvida se deve ou não ativar. Como procurei ser o mais objetiva possível no post acabei não abordando alguns detalhes, mas excelente colocação.😉

      Você não precisa habilitar o AWE via sp_configure se você estiver executando a versão de 64 bits em x64, como SQL de 64 bits não precisa usar a api AWE para endereçar um grande espaço de memória.

      Recomenda-se conceder a conta de serviço do SQL o privilégio “Bloquear páginas na memória” (Lock pages in memory). Isso deve lhe dar um benefício de desempenho em situações de “restrições” memória.

      Em resumo, a versão 64 bits do SQL Server pode resolver toda a memória no servidor sem ter que usar AWE ativado através do sp_configure. Um benefício pode ser observado quando “lock pages in memory” está habilitada e isso resulta no uso de AWE internamente, independentemente da configuração sp_configure.

  3. Leandro Ribeiro

    Parabéns Viviane pelo post… muito bem escrito e as observações do Leivio e sua complementação como resposta, deixaram o post ainda mais completo. Show de Bola!

  4. Marcos Silva

    Maravilhosas essas dicas Vi, ta ai um bom conteúdo, onde eu hospedo meus sites, na inter.net, eles me dão várias dicas bem legais como essas suas. Ajudam muito quando a gente ta começando. Vlw Vi

Deixe uma resposta

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: