Mecanismo de Banco de Dados Orientador de Otimização

O Orientador de Otimização do Mecanismo de Banco de Dados da Microsoft (DTA) analisa bancos de dados e faz recomendações que você pode usar para otimizar o desempenho da consulta. Você pode usar o Orientador de Otimização do Mecanismo de Banco de Dados para selecionar e criar um conjunto ideal de índices, exibições indexadas ou partições de tabela sem ter uma compreensão especializada da estrutura do banco de dados ou dos internos do SQL Server. Usando o DTA, você pode executar as tarefas a seguir.

• Solucionar problemas de desempenho de uma consulta de problema específica

• Otimizar um grande conjunto de consultas em um ou mais bancos de dados

• Executar uma análise de hipóteses exploratórias de possíveis alterações de design físico

• Gerenciar espaço de armazenamento

Benefícios do Orientador de Otimização do Mecanismo de Banco de Dados

Otimizar o desempenho da consulta pode ser difícil sem entender completamente a estrutura do banco de dados e as consultas que são executadas no banco de dados. O Orientador de Otimização do Mecanismo de Banco de Dados pode facilitar essa tarefa analisando o cache do plano de consulta atual ou analisando uma carga de trabalho de Transact-SQL consultas criadas e recomendando um design físico apropriado. Para administradores de banco de dados mais avançados, o DTA expõe um mecanismo poderoso para executar a análise de hipóteses exploratórias de diferentes alternativas de design físico. O DTA pode fornecer as informações a seguir.

• Recomende a melhor combinação de índices para bancos de dados usando o otimizador de consulta para analisar consultas em uma carga de trabalho.

• Recomenda partições alinhadas ou não alinhadas para bancos de dados referenciados em uma carga de trabalho.

• Recomenda exibições indexadas para bancos de dados referenciados em uma carga de trabalho.

• Analise os efeitos das alterações propostas, incluindo o uso do índice, a distribuição de consultas entre tabelas e o desempenho da consulta na carga de trabalho.

• Recomenda maneiras de ajustar o banco de dados para um pequeno conjunto de consultas de problema.

• Permita que você personalize a recomendação especificando opções avançadas, como restrições de espaço em disco.

• Forneça relatórios que resumem os efeitos da implementação das recomendações para uma determinada carga de trabalho.

O Orientador de Otimização do Mecanismo de Banco de Dados foi projetado para lidar com os seguintes tipos de cargas de trabalho de consulta.

• Somente consultas OLTP (processamento de transações online)

• Somente consultas OLAP (processamento analítico online)

• Consultas OLTP e OLAP mistas

• Cargas de trabalho pesadas de consulta (mais consultas do que modificações de dados)

• Cargas de trabalho pesadas de atualização (mais modificações de dados do que consultas)

Componentes e conceitos do DTA

Interface gráfica do usuário do Orientador de Otimização do Mecanismo de Banco de Dados
Uma interface fácil de usar na qual você pode especificar a carga de trabalho e selecionar várias opções de ajuste.

dta Utilidade
A versão do Orientador de Otimização do Mecanismo de Banco de Dados no prompt de comando. O utilitário dta foi projetado para permitir o uso da funcionalidade do Orientador de Otimização do Mecanismo de Banco de Dados em aplicativos e scripts.

Carga
Um arquivo de script Transact-SQL, um arquivo de rastreamento ou uma tabela de rastreamento que contém uma carga de trabalho representativa para os bancos de dados que você deseja ajustar. A partir do SQL Server 2012, você pode especificar o cache do plano como a carga de trabalho.

Arquivo de entrada XML
Um arquivo formatado em XML que o Orientador de Otimização do Mecanismo de Banco de Dados pode usar para ajustar cargas de trabalho. O arquivo de entrada XML dá suporte a opções avançadas de ajuste que não estão disponíveis no utilitário gui ou dta .

Limitações e restrições

O Orientador de Otimização do Mecanismo de Banco de Dados tem as seguintes limitações e restrições.

• Ele não pode adicionar nem remover índices exclusivos ou índices que impõem restrições PRIMARY KEY ou UNIQUE.

• Ele não pode analisar um banco de dados definido como modo de usuário único.

• Se você especificar um espaço máximo em disco para ajustar recomendações que excedam o espaço disponível real, o Orientador de Otimização do Mecanismo de Banco de Dados usará o valor especificado. No entanto, quando você executa o script de recomendação para implementá-lo, o script pode falhar se mais espaço em disco não for adicionado primeiro. O espaço máximo em disco pode ser especificado com a opção -B do utilitário dta ou inserindo um valor na caixa de diálogo Opções avançadas de ajuste .

• Por motivos de segurança, o Orientador de Otimização do Mecanismo de Banco de Dados não pode ajustar uma carga de trabalho em uma tabela de rastreamento que reside em um servidor remoto. Para contornar essa limitação, você pode usar um arquivo de rastreamento em vez de uma tabela de rastreamento ou copiar a tabela de rastreamento para o servidor remoto.

• Quando você impõe restrições, como as impostas quando especifica um espaço máximo em disco para ajustar recomendações (usando a opção -B ou a caixa de diálogo Opções Avançadas de Ajuste ), o Orientador de Otimização do Mecanismo de Banco de Dados pode ser forçado a remover determinados índices existentes. Nesse caso, a recomendação resultante do Orientador de Otimização do Mecanismo de Banco de Dados pode produzir uma melhoria negativa esperada.

• Quando você especifica uma restrição para limitar o tempo de ajuste (usando a opção -A com o utilitário dta ou marcando a opção Limitar tempo de ajuste na guia Opções de Ajuste), o Orientador de Ajuste do Banco de Dados pode exceder esse limite de tempo para produzir uma melhoria esperada precisa e os relatórios de análise de qualquer parte da carga de trabalho que foi consumida até agora.

• O Orientador de Otimização do Mecanismo de Banco de Dados pode não fazer recomendações nas seguintes circunstâncias:

  1. A tabela que está sendo ajustada contém menos de 10 páginas de dados.

  2. Os índices recomendados não ofereceriam melhorias suficientes no desempenho da consulta em relação ao design atual do banco de dados físico.

  3. O usuário que executa o Orientador de Otimização do Mecanismo de Banco de Dados não é membro da db_owner função de banco de dados ou da sysadmin função de servidor fixa. As consultas na carga de trabalho são analisadas no contexto de segurança do usuário que executa o Orientador de Otimização do Mecanismo de Banco de Dados. O usuário deve ser membro da db_owner função de banco de dados.

• O Orientador de Ajuste do Mecanismo de Banco de Dados armazena dados da sessão de ajuste e outras informações no banco de dados msdb. Se alterações forem feitas no banco de dados msdb, você poderá correr o risco de perder dados de sessão de ajuste. Para eliminar esse risco, implemente uma estratégia de backup apropriada para o msdb banco de dados.

Considerações sobre desempenho

O Orientador de Otimização do Mecanismo de Banco de Dados pode consumir recursos significativos de processador e memória durante a análise. Para evitar a lentidão do servidor de produção, siga uma destas estratégias:

• Ajuste seus bancos de dados quando o servidor estiver gratuito. O Orientador de Otimização do Mecanismo de Banco de Dados pode afetar o desempenho da tarefa de manutenção.

• Use o recurso servidor de teste/servidor de produção. Para obter mais informações, consulte Reduzir a carga de ajuste do servidor de produção.

• Especifique apenas as estruturas de design do banco de dados físico que você deseja que o Orientador de Otimização do Mecanismo de Banco de Dados analise. O Orientador de Otimização do Mecanismo de Banco de Dados fornece muitas opções, mas especifica apenas aquelas necessárias.

Dependência do procedimento armazenado estendido xp_msver

O Orientador de Otimização do Mecanismo de Banco de Dados depende do xp_msver procedimento armazenado estendido para fornecer funcionalidade completa. Esse procedimento armazenado estendido é ativado por padrão. O Orientador de Otimização do Mecanismo de Banco de Dados usa esse procedimento armazenado estendido para buscar o número de processadores e a memória disponível no computador em que reside o banco de dados que você está ajustando. Se xp_msver não estiver disponível, o Orientador de Otimização do Mecanismo de Banco de Dados assumirá as características de hardware do computador em que o Orientador de Otimização do Mecanismo de Banco de Dados está em execução. Se as características de hardware do computador em que o Orientador de Otimização do Mecanismo de Banco de Dados estiver em execução não estiverem disponíveis, um processador e 1024 megabytes (MBs) de memória serão assumidos.

Essa dependência afeta as recomendações de particionamento porque o número de partições recomendadas depende desses dois valores (número de processadores e memória disponível). A dependência também afeta os resultados de ajuste quando você usa um servidor de teste para ajustar o servidor de produção. Nesse cenário, o Orientador de Otimização do Mecanismo de Banco de Dados usa xp_msver para buscar propriedades de hardware do servidor de produção. Depois de ajustar a carga de trabalho no servidor de teste, o Orientador de Otimização do Mecanismo de Banco de Dados usa essas propriedades de hardware para gerar uma recomendação. Para obter mais informações, confira xp_msver (Transact-SQL).

Tarefas do Orientador de Otimização do Mecanismo de Banco de Dados

A tabela a seguir lista tarefas comuns do Orientador de Otimização do Mecanismo de Banco de Dados e os tópicos que descrevem como executá-las.

Tarefa do Orientador de Otimização do Mecanismo de Banco de Dados	Tópico
Inicialize e inicie o Orientador de Otimização do Mecanismo de Banco de Dados. Crie uma carga de trabalho especificando o cache de plano, criando um script ou gerando um arquivo de rastreamento ou uma tabela de rastreamento. Ajuste um banco de dados usando a ferramenta gráfica do Orientador de Otimização do Banco de Dados. Crie arquivos de entrada XML para ajustar cargas de trabalho. Exibir descrições das opções de interface do usuário do Orientador de Otimização do Mecanismo de Banco de Dados.	Iniciar e usar o Orientador de Otimização do Mecanismo de Banco de Dados
Exiba os resultados da operação de ajuste do banco de dados. Selecione e implemente recomendações de ajuste. Realize uma análise exploratória de cenários hipotéticos sobre a carga de trabalho. Examinar sessões de ajuste existentes, clonar sessões com base nas existentes ou edite as recomendações de ajuste existentes para avaliação ou implementação adicionais. Exibir descrições das opções de interface do usuário do Orientador de Otimização do Mecanismo de Banco de Dados.	Exibir e trabalhar com a saída do Orientador de Otimização do Mecanismo de Banco de Dados