Limitando eventos e duração de ajuste
Você pode controlar as sessões de ajuste do Orientador de Otimização do Mecanismo do Banco de Dados limitando o tempo que o aplicativo leva para fazer ajustes ou limitando o número de eventos que ele ajusta. Os limites na duração de análise de ajuste são úteis quando é preciso ajustar um servidor de produção durante uma janela de manutenção. A limitação do número de eventos ajustados é útil ao se comparar duas configurações hipotéticas ajustando a mesma carga de trabalho para cada uma.
O Orientador de Otimização do Mecanismo de Banco de Dados pode não ajustar todos os eventos em uma carga de trabalho para uma determinada configuração. Ao especificar o número de eventos a serem ajustados, você garante que a sessão de ajuste para cada configuração considera o mesmo número de eventos ajustáveis, de forma que os resultados sejam mais comparáveis.
Controlando a duração do ajuste
O Orientador de Otimização do Mecanismo de Banco de Dados analisa uma carga de trabalho até que ela encontre o conjunto ideal de estruturas de desempenho físico possível para os bancos de dados especificados. A quantidade de tempo que isso leva depende da extensão da carga de trabalho. O Orientador de Otimização do Mecanismo de Banco de Dados pode levar vários dias para concluir uma análise completa em uma carga de trabalho que contenha centenas de eventos. Embora os bancos de dados fiquem disponíveis ao longo da análise, o desempenho pode ser afetado.
Para controlar em quanto tempo o Orientador de Otimização do Mecanismo de Banco de Dados analisa uma carga de trabalho, defina o tempo máximo. O tempo máximo é o número de horas e de minutos que o Orientador de Otimização do Mecanismo de Banco de Dados usará para analisar uma carga de trabalho. O resultado de uma definição de tempo máximo é uma recomendação baseada na melhor solução que o Orientador de Otimização do Mecanismo de Banco de Dados pode produzir antes que o prazo expire.
Na GUI (interface gráfica do usuário) do Orientador de Otimização do Mecanismo de Banco de Dados, esse parâmetro é definido como 1 hora por padrão. No utilitário de linha de comando dta, o limite padrão é definido como 8 horas. Se a análise completa exigir menos tempo que o limite definido, o ajuste terminará após a conclusão. Você pode especificar um tempo de ajuste ilimitado para gerar a melhor recomendação possível em ambas as interfaces de ferramenta.
As subseções a seguir descrevem como definir essa opção de ajuste na GUI e no prompt de comando.
Definir o tempo de ajuste na interface gráfica do usuário
Você define o limite de tempo de ajuste na GUI do Orientador de Otimização do Mecanismo de Banco de Dados selecionando Limitar tempo de ajuste na guia Opções de Ajuste e marcando a data e a hora em Parar em.
Os seguintes detalhes são aplicáveis:
Em geral, ao especificar um tempo de ajuste mais longo, você obtém uma melhora geral de resultados, porque a carga de trabalho pode ser analisada mais amplamente. As recomendações são baseadas em informações mais completas.
Por padrão, o limite máximo de ajuste está definido como 1 hora.
Para especificar um tempo de ajuste ilimitado, desmarque a caixa de seleção Limitar tempo de ajuste.
Definir o tempo de ajuste no utilitário de linha de comando dta
Você pode definir o limite de tempo de ajuste por meio do utilitário de linha de comando dta especificando um valor para o argumento -A. Por exemplo, examine o seguinte comando dta:
dta -E -s MySession -D AdventureWorks2008R2 -if MyWorkloadScript.sql -fa IDX -A 120
sendo que -E especifica uma conexão confiável, -D especifica o banco de dados a ser ajustado, -if especifica o arquivo de carga de trabalho, -fa IDX especifica que o Orientador de Otimização do Mecanismo de Banco de Dados deve considerar apenas a adição de índices em sua recomendação, e -A 120 significa que o ajuste deve ser concluído em 120 minutos.
Ao executar o utilitário dta no prompt de comando (em vez de chamá-lo em um script para ajuste automático), pressione CTRL+C para parar o ajuste. O Orientador de Otimização do Mecanismo de Banco de Dados retorna a melhor recomendação possível com base na quantidade de carga de trabalho consumida naquele momento.
Os seguintes detalhes são aplicáveis:
Em geral, ao especificar um tempo de ajuste mais longo, você obtém uma melhora geral de resultados, porque a carga de trabalho pode ser analisada mais amplamente. Portanto, as recomendações são baseadas em informações mais completas.
Para especificar o ajuste com tempo ilimitado, use o argumento de linha de comando –A 0. Use esse argumento para obter a melhor recomendação para a carga de trabalho inteira.
Se a opção –A não for especificada, ela será padronizada como –A 480 (um tempo de ajuste de 8 horas).
Até mesmo se você pressionar CTRL+C, o utilitário dta poderá levar um tempo para popular os relatórios de análise antes de encerrar.
Definir o tempo de ajuste no arquivo de entrada XML
Você também pode definir um limite de tempo de ajuste máximo no arquivo de entrada XML, que pode ser usado com o utilitário de linha de comando dta. Para especificar um limite de tempo de ajuste em minutos no arquivo de entrada XML, use o subelemento TuningTimeInMin no elemento pai TuningOptions. Para obter mais informações sobre o arquivo de entrada XML, consulte Referência do arquivo de entrada XML (Orientador de Otimização do Mecanismo de Banco de Dados).
Controlando o número de eventos a ser ajustado
É possível controlar o número de eventos ajustado em cargas de trabalho pelo Orientador de Otimização do Mecanismo de Banco de Dados. Essa opção está disponível apenas no utilitário de linha de comando dta usando o argumento –n. O comportamento do utilitário dta quando o argumento -n é usado depende do valor especificado para o argumento -A que especifica o tempo limite de ajuste, da seguinte maneira:
O utilitário dta é interrompido quando já tiver ajustado o número especificado de eventos ou o limite de tempo de ajuste especificado tiver decorrido, o que acontecer primeiro. Se o argumento -n não for especificado, por padrão, o dta ajustará todos os eventos na carga de trabalho. Se o argumento -A não for especificado, o dta assumirá como padrão um tempo limite de ajuste de 8 horas.
Para verificar se o número de eventos especificado com o argumento -n foi ajustado, especifique 0 para o argumento -A que especifica um tempo de ajuste ilimitado. Por exemplo, -n 250 -A 0.
Especificar o número de eventos a ser ajustado no arquivo de entrada XML
Você também pode especificar um número de eventos a ser ajustado no arquivo de entrada XML que pode ser usado com o utilitário de linha de comando dta. Para especificar um número de eventos a ser ajustado no arquivo de entrada XML, use o subelemento NumberOfEvents no elemento pai TuningOptions. Para obter mais informações sobre o arquivo de entrada XML, consulte Referência do arquivo de entrada XML (Orientador de Otimização do Mecanismo de Banco de Dados).
Práticas recomendadas para limitar o tempo e o número de eventos ajustado
A seguir, há sugestões para limitar o tempo e o número de eventos ajustado:
Para consultas únicas ou cargas de trabalho pequenas (com menos de 100 eventos), especifique um tempo de ajuste ilimitado. Uma especificação de tempo de ajuste ilimitado leva às melhores recomendações pelo Orientador de Otimização do Mecanismo de Banco de Dados e, na maioria dos casos, o ajuste será concluído em um prazo relativamente curto.
Para cargas de trabalho grandes (mais de 100 eventos), considere as opções a seguir, na ordem em que estão listadas. Só recorra à última opção (4), depois de ter considerado as opções de 1 a 3.
Se o usuário tiver uma restrição de tempo, limite o tempo de ajuste.
Se o ajuste de um número fixo de eventos for suficiente (por exemplo, os primeiros 10.000 eventos podem ser representativos do restante da carga de trabalho), use o utilitário de linha de comando dta e especifique o número de eventos usando o argumento –n.
Se estiver usando o utilitário de linha de comando dta e desejar limitar ainda mais o tempo de ajuste, você poderá usar os argumentos –A e –n. Por exemplo, especificar -A 240 e –n 1000 faz com que o Orientador de Otimização do Mecanismo de Banco de Dados interrompa o ajuste assim que 1000 eventos tenham sido ajustados ou que tenham decorrido 4 horas, o que ocorrer primeiro.
O tempo gasto para o ajuste depende da complexidade das consultas (número de tabelas referenciadas), dos conjuntos de recursos escolhidos (o ajuste de exibições indexadas consome mais tempo do que o ajuste de índices) e do tamanho dos dados (para criação de estatísticas). Na maioria dos casos, a maior parte do tempo que o Orientador de Otimização do Mecanismo de Banco de Dados gasta fazendo ajustes está em fazer chamadas para o otimizador de consulta. Uma regra básica para poder estimar o tempo adequado para o Orientador de Otimização do Mecanismo de Banco de Dados fazer ajustes é a seguinte:
Para consultas simples que referenciam de uma a três tabelas, calcule aproximadamente 1 segundo por consulta caso esteja ajustando apenas índices, e cerca de 10 segundos por consulta caso esteja ajustando índices e exibições indexadas. Para consultas complexas que referenciam mais de três tabelas, calcule aproximadamente 10 segundos por consulta caso esteja ajustando apenas índices, e cerca de 100 segundos caso esteja ajustando índices e exibições indexadas.
Se o Orientador de Otimização do Mecanismo de Banco de Dados indicar que foi consumido 100% da carga de trabalho, isso significará que a carga de trabalho inteira foi analisada, mas não necessariamente ajustada. Para determinar se toda a carga de trabalho foi ajustada, pesquise a seguinte mensagem no final do log de ajuste:
"Nenhum evento na carga de trabalho foi ajustado. Convém aumentar o tempo associado ou especificar o número de eventos a ser considerado no XML de entrada."
Se essa mensagem estiver presente no log de ajuste, o Orientador de Otimização do Mecanismo de Banco de Dados não pôde ajustar a carga de trabalho inteira. Para solucionar isso, especifique um tempo de ajuste maior. Para garantir que todos os eventos da carga de trabalho sejam ajustados, especifique um tempo de ajuste ilimitado. Se decidir não especificar um tempo de ajuste ilimitado, o Orientador de Otimização do Mecanismo de Banco de Dados tentará ajustar o maior número de eventos possíveis dentro do tempo de ajuste especificado.
Observação Não existe mapeamento direto entre o modos Rápido, Médio ou Completo no Assistente de Ajuste do Índice do Microsoft SQL Server 2000 para os argumentos –A e –n no Orientador de Otimização do Mecanismo de Banco de Dados. Em geral, fazer ajuste em um determinado modo (Rápido, Médio, ou Completo) no SQL Server 2000 leva um determinado tempo. A mesma quantidade de tempo normalmente oferece recomendações comparáveis ou melhores no Orientador de Otimização do Mecanismo de Banco de Dados do SQL Server 2005. É recomendável que os usuários do modo Completo usem o Orientador de Otimização do Mecanismo de Banco de Dados com um tempo de ajuste ilimitado e um número ilimitado de eventos a ser ajustado na carga de trabalho.
Métodos adicionais para limitar a duração do ajuste
Além de limitar o tempo de execução do Orientador de Otimização do Mecanismo de Banco de Dados, você pode ajustar apenas um subconjunto das tabelas no banco de dados.
As seguintes opções também estão disponíveis:
Mantenha as PDS (estruturas de design físicas) existentes.
Mantenha apenas índices.
Mantenha apenas índices clusterizados.
O Orientador de Otimização do Mecanismo de Banco de Dados inclui todas as dicas de índice ou de consulta na recomendação final, até mesmo se o índice não for ideal para a tabela. O índices em outras tabelas referenciadas na consulta podem ser propostos e recomendados. Porém, todos os índices especificados como dicas sempre farão parte da recomendação final. As dicas podem impedir que o Orientador de Otimização do Mecanismo de Banco de Dados escolha um plano de execução melhor. Considere a remoção de todas a dicas de índice das consultas antes da análise da carga de trabalho.