Reduzir a carga de ajuste do servidor de produção

O Orientador de Otimização do Mecanismo de Banco de Dados depende do otimizador de consulta para analisar uma carga de trabalho e fazer recomendações de ajuste. Executar essa análise no servidor de produção adiciona à carga do servidor e pode prejudicar o desempenho do servidor durante a sessão de ajuste. Você pode reduzir o impacto na carga do servidor durante uma sessão de ajuste usando um servidor de teste além do servidor de produção.

Como o Orientador de Otimização do Mecanismo de Banco de Dados usa um servidor de teste

A maneira tradicional de usar um servidor de teste é copiar todos os dados do servidor de produção para o servidor de teste, ajustar o servidor de teste e implementar a recomendação no servidor de produção. Esse processo elimina o impacto no desempenho no servidor de produção, mas, no entanto, não é a solução ideal. Por exemplo, copiar grandes quantidades de dados da produção para o servidor de teste pode consumir quantidades substanciais de tempo e recursos. Além disso, o hardware do servidor de teste raramente é tão poderoso quanto o hardware implantado para servidores de produção. O processo de ajuste depende do otimizador de consulta e as recomendações geradas são baseadas em parte no hardware subjacente. Se o hardware do servidor de teste e produção não for idêntico, a qualidade da recomendação do Orientador de Otimização do Mecanismo de Banco de Dados será reduzida.

Para evitar esses problemas, o Orientador de Otimização do Mecanismo de Banco de Dados ajusta um banco de dados em um servidor de produção descarregando a maior parte da carga de ajuste em um servidor de teste. Ele faz isso usando as informações de configuração de hardware do servidor de produção e sem realmente copiar os dados do servidor de produção para o servidor de teste. O Orientador de Otimização do Mecanismo de Banco de Dados não copia dados reais do servidor de produção para o servidor de teste. Ele copia apenas os metadados e as estatísticas necessárias.

As etapas a seguir descrevem o processo de ajuste de um banco de dados de produção em um servidor de teste:

1. Verifique se o usuário que deseja usar o servidor de teste existe em ambos os servidores.

   Antes de começar, verifique se o usuário que deseja usar o servidor de teste para ajustar um banco de dados no servidor de produção existe em ambos os servidores. Isso requer que você crie o usuário e seu logon no servidor de teste. Se você for membro da função de servidor fixa sysadmin em ambos os computadores, essa etapa não será necessária.

2. Ajuste a carga de trabalho no servidor de teste.

   Para ajustar uma carga de trabalho em um servidor de teste, você deve usar um arquivo de entrada XML com o utilitário de linha de comando dta . No arquivo de entrada XML, especifique o nome do servidor de teste com o subelemento TestServer , além de especificar os valores para os outros subelementos no elemento pai tuningOptions .

   Durante o processo de ajuste, o Orientador de Otimização do Mecanismo de Banco de Dados cria um banco de dados shell no servidor de teste. Para criar esse banco de dados shell e ajustá-lo, o Orientador de Otimização do Mecanismo de Banco de Dados faz chamadas ao servidor de produção para o seguinte:

   1. O Orientador de Otimização do Mecanismo de Banco de Dados importa metadados do banco de dados de produção para o banco de dados do shell do servidor de teste. Esses metadados incluem tabelas vazias, índices, exibições, procedimentos armazenados, gatilhos e assim por diante. Isso possibilita que as consultas de carga de trabalho sejam executadas no banco de dados do servidor de teste shell.

   2. O Orientador de Otimização do Mecanismo de Banco de Dados importa estatísticas do servidor de produção para que o otimizador de consulta possa otimizar com precisão as consultas no servidor de teste.

   3. O Orientador de Otimização do Mecanismo de Banco de Dados importa parâmetros de hardware que especificam o número de processadores e a memória disponível do servidor de produção para fornecer ao otimizador de consulta as informações necessárias para gerar um plano de consulta.

3. Depois que o Orientador de Otimização do Mecanismo de Banco de Dados terminar de ajustar o banco de dados do shell do servidor de teste, ele gerará uma recomendação de ajuste.

4. Aplique a recomendação recebida do ajuste do servidor de teste ao servidor de produção.

A ilustração a seguir mostra o servidor de teste e o cenário do servidor de produção:

Uso do servidor de teste do

Exemplo

Primeiro, verifique se o usuário que deseja executar o ajuste existe nos servidores de teste e de produção.

Depois que as informações do usuário forem copiadas para o servidor de teste, você poderá definir a sessão de ajuste do servidor de teste no arquivo de entrada XML do Orientador de Otimização do Mecanismo de Banco de Dados. O arquivo de entrada XML de exemplo a seguir ilustra como especificar um servidor de teste para ajustar um banco de dados com o Orientador de Otimização do Mecanismo de Banco de Dados.

Neste exemplo, o MyDatabaseName banco de dados está sendo otimizado em MyServerName. O script Transact-SQL MyWorkloadScript.sql é usado como a carga de trabalho. Esta carga de trabalho contém eventos que são executados em relação a MyDatabaseName. A maioria das chamadas do otimizador de consulta para este banco de dados, que ocorrem como parte do processo de ajuste, é tratada pelo banco de dados do shell que reside em MyTestServerName. O banco de dados do shell é composto por metadados e estatísticas. Esse processo faz com que a sobrecarga de ajuste seja descarregada no servidor de teste. Quando o Orientador de Otimização do Mecanismo de Banco de Dados gera sua recomendação de ajuste usando esse arquivo de entrada XML, ele deve considerar apenas índices (<FeatureSet>IDX</FeatureSet>), sem particionamento, e não precisa manter nenhuma das estruturas de design físicas existentes no MyDatabaseName.

<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://schemas.microsoft.com/sqlserver/2004/07/dta">
  <DTAInput>
    <Server>
      <Name>MyServerName</Name>
      <Database>
        <Name>MyDatabaseName</Name>
      </Database>
    </Server>
    <Workload>
      <File>MyWorkloadScript.sql</File>
    </Workload>
    <TuningOptions>
      <TestServer>MyTestServerName</TestServer>
      <FeatureSet>IDX</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
    </TuningOptions>
  </DTAInput>
</DTAXML>

Consulte Também

Considerações para Uso de Servidores de TesteReferência de Arquivo de Entrada XML (Assistente de Ajuste do Mecanismo de Banco de Dados)