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

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

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. A execução dessa análise no servidor de produção aumenta a 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 do 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 que ele gera são baseadas em parte no hardware subjacente. Se o hardware do servidor de teste e de 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. Apenas copia 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 login no servidor de teste. Se for membro da função fixa de servidor sysadmin em ambos os computadores, esta 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 para o 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 shell do servidor de teste.

   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 consultas 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 especificando 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 concluir o ajuste do banco de dados shell do servidor de teste, ele gerará uma recomendação de ajuste.

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

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

Observação

O recurso de ajuste do servidor de teste não é suportado na interface gráfica do usuário (GUI) do Orientador de Otimização do Mecanismo de Banco de Dados.

Exemplo

Primeiro, certifique-se de que o usuário que deseja executar o ajuste existe nos servidores de teste e 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 exemplo de arquivo de entrada XML 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 banco de dados MyDatabaseName está a ser ajustado em MyServerName. O script Transact-SQL e o MyWorkloadScript.sqlsão usados como carga de trabalho. Essa carga de trabalho contém eventos que são executados contra MyDatabaseName. A maioria das chamadas do otimizador de consulta para esse banco de dados, que ocorrem como parte do processo de ajuste, são manipuladas pelo banco de dados shell que reside em MyTestServerName. O banco de dados shell é composto por metadados e estatísticas. Esse processo resulta na sobrecarga de ajuste sendo descarregada para o 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ísico existentes em MyDatabaseName.

<?xml version="1.0" encoding="utf-16" ?>  
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://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>  

Ver também

considerações sobre o uso de servidores de teste
Referência de arquivo de entrada XML (Orientador de Otimização do Mecanismo de Banco de Dados)