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