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

Aplica-se a:SQL Server

O Microsoft Database Engine Tuning Advisor (DTA) analisa bases de dados e faz recomendações que pode usar para otimizar o desempenho das consultas. Pode usar o Database Engine Tuning Advisor para selecionar e criar um conjunto ótimo de índices, vistas indexadas ou partições de tabela sem ter um conhecimento especializado da estrutura da base de dados ou do interior do SQL Server. Usando o DTA, pode realizar as seguintes tarefas:

• Diagnosticar o desempenho de uma consulta problemática específica.

• Ajuste um grande conjunto de consultas em uma ou mais bases de dados

• Realize uma análise exploratória hipotética das potenciais alterações no design físico

• Gerir espaço de armazenamento

Observação

O Orientador de Otimização do Mecanismo de Banco de Dados não tem suporte para o Banco de Dados SQL do Azure ou para a Instância Gerenciada SQL do Azure. Em vez disso, considere as estratégias recomendadas em Monitoramento e ajuste de desempenho no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure. Para o Banco de Dados SQL do Azure, consulte também as recomendações de desempenho do Supervisor de Banco de Dados para o Banco de Dados SQL do Azure.

Benefícios do Consultor de Otimização do Motor de Base de Dados

Otimizar o desempenho das consultas pode ser difícil sem uma compreensão completa da estrutura da base de dados e das consultas que são executadas contra a base de dados. O Database Engine Tuning Advisor (DTA) pode facilitar esta tarefa analisando o cache atual do plano de execução de consultas ou analisando uma carga de trabalho de consultas Transact-SQL que você cria e recomendando um design físico apropriado. Para administradores de bases de dados mais avançados, a DTA expõe um mecanismo poderoso para realizar análises exploratórias hipotéticas de diferentes alternativas de design físico. A DTA pode fornecer a seguinte informação.

• Recomende a melhor combinação de índices de rowstore e columnstore para bases de dados, utilizando o otimizador de consultas para analisar consultas numa carga de trabalho.

• Recomende partições alinhadas ou não alinhadas para bases de dados referenciadas numa carga de trabalho.

• Recomende vistas indexadas para bases de dados referenciadas numa carga de trabalho.

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

• Recomende formas de ajustar a base de dados para um pequeno conjunto de consultas de problemas.

• Permite-lhe personalizar a recomendação especificando opções avançadas, como restrições de espaço no disco.

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

• Considere alternativas em que forneça possíveis opções de design sob a forma de configurações hipotéticas para o Database Engine Tuning Advisor avaliar.

• Ajuste cargas de trabalho de várias fontes, incluindo o SQL Server Query Store, Plan Cache, ficheiro ou tabela de rastreamento do SQL Server Profiler, ou um ficheiro .SQL.

O Database Engine Tuning Advisor foi concebido para lidar com os seguintes tipos de cargas de trabalho de consulta:

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

• Apenas consultas de processamento analítico online (OLAP)

• Consultas mistas OLTP e OLAP

• Cargas de trabalho com muitas consultas (mais consultas do que modificações de dados)

• Cargas de trabalho com muitas atualizações (mais modificações de dados do que consultas)

Componentes e Conceitos da DTA

Interface gráfica do Database Engine Tuning Advisor
Uma interface fácil de usar onde pode especificar a carga de trabalho e selecionar várias opções de afinação.

dta Utilidade
A versão da linha de comandos do Database Engine Tuning Advisor. O utilitário dta foi projetado para permitir que você use a funcionalidade do Orientador de Otimização do Mecanismo de Banco de Dados em aplicativos e scripts.

workload
Um ficheiro de script Transact-SQL, ficheiro de traços ou tabela de traços que contenha uma carga de trabalho representativa para as bases de dados que pretende ajustar. A partir do SQL Server 2012 (11.x), pode especificar a cache do plano como carga de trabalho. A partir do SQL Server 2016 (13.x), pode especificar a Loja de Consultas como carga de trabalho.

Ficheiro de entrada XML
Um ficheiro em formato XML que o Database Engine Tuning Advisor pode usar para ajustar cargas de trabalho. O ficheiro de entrada XML suporta opções avançadas de afinação que não estão disponíveis nem na interface gráfica nem na utilidade dta .

Limitações e Restrições

O Database Engine Tuning Advisor apresenta as seguintes limitações e restrições.

• Não pode adicionar ou eliminar índices únicos ou índices que impõem restrições de PRIMARY KEY ou UNIQUE.

• Não pode analisar uma base de dados configurada para modo de utilizador único.

• Se especificar um espaço máximo em disco para recomendações de afinação que exceda o espaço disponível realmente, o Database Engine Tuning Advisor utiliza o valor que especificar. No entanto, quando executa o script de recomendação para o implementar, o script pode falhar se não for adicionado mais espaço em disco primeiro. O espaço máximo em disco pode ser especificado com a opção -B da utilidade dta , ou introduzindo um valor na caixa de diálogo Opções Avançadas de Ajuste .

• Por razões de segurança, o Database Engine Tuning Advisor não pode ajustar uma carga de trabalho numa tabela de traços que reside num servidor remoto. Para contornar esta limitação, pode usar um ficheiro de rastreamento em vez de uma tabela de traços ou copiar a tabela de traços para o servidor remoto.

• Quando impõe restrições, como as impostas ao especificar um espaço máximo em disco para recomendações de ajuste (usando a opção -B ou a caixa de diálogo Opções Avançadas de Ajuste ), o Database Engine Tuning Advisor pode ser forçado a eliminar certos índices existentes. Neste caso, a recomendação resultante do Database Engine Tuning Advisor pode produzir uma melhoria esperada negativa.

• Quando especifica uma restrição para limitar o tempo de afinação (usando a opção -A com o utilitário dta ou verificando Limitar o tempo de afinação no separador Opções de Ajuste ), o Database Engine Tuning Advisor pode exceder esse limite de tempo para produzir uma melhoria precisa esperada e os relatórios de análise para a parte da carga de trabalho consumida até então.

• O Database Engine Tuning Advisor pode não fazer recomendações nas seguintes circunstâncias:

  1. A tabela a ser ajustada contém menos de 10 páginas de dados.

  2. Os índices recomendados não ofereceriam melhoria suficiente no desempenho das consultas em relação ao atual design físico da base de dados.

  3. O utilizador que executa o Database Engine Tuning Advisor não pertence à função de base de dados db_owner nem à função de servidor fixo sysadmin. As consultas na carga de trabalho são analisadas no contexto de segurança do utilizador que executa o Database Engine Tuning Advisor. O utilizador deve ser membro da função db_owner da base de dados.

• O Database Engine Tuning Advisor armazena os dados das sessões de afinação e outras informações na msdb base de dados. Se forem efetuadas alterações na msdb base de dados, pode correr o risco de perder os dados de ajuste de desempenho da sessão. Para eliminar este risco, implemente uma estratégia de backup adequada para a msdb base de dados.

Considerações sobre desempenho

O Database Engine Tuning Advisor pode consumir recursos significativos de processador e memória durante a análise. Para evitar atrasar o seu servidor de produção, siga uma destas estratégias:

• Ajusta as tuas bases de dados quando o teu servidor estiver livre. O Database Engine Tuning Advisor pode afetar o desempenho das tarefas de manutenção.

• Use a funcionalidade de servidor de teste/servidor de produção. Para mais informações, veja Reduzir a Carga de Ajuste do Servidor de Produção.

• Especifica apenas as estruturas físicas de design de bases de dados que queres que o Database Engine Tuning Advisor analise. O Database Engine Tuning Advisor oferece muitas opções, mas especifica apenas aquelas que são necessárias.

Dependência do procedimento armazenado expandido xp_msver

O Consultor de Ajuste do Motor de Base de Dados depende do procedimento armazenado xp_msver estendido para fornecer funcionalidade total. Este procedimento armazenado estendido é ativado por defeito. O Database Engine Tuning Advisor utiliza este procedimento armazenado alargado para obter o número de processadores e memória disponível no computador onde reside a base de dados que está a ajustar. Se xp_msver não estiver disponível, o Database Engine Tuning Advisor assume as características de hardware do computador onde o Database Engine Tuning Advisor está a correr. Se as características de hardware do computador onde o Database Engine Tuning Advisor está a correr não estiverem disponíveis, assumem-se um processador e 1024 megabytes (MBs) de memória.

Esta dependência afeta as recomendações de partição porque o número de partições recomendadas depende destes dois valores (número de processadores e memória disponível). A dependência também afeta os resultados da sua afinação quando usa um servidor de teste para ajustar o seu servidor de produção. Neste cenário, o Database Engine Tuning Advisor utiliza xp_msver para obter propriedades de hardware do servidor de produção. Após ajustar a carga de trabalho no servidor de teste, o Database Engine Tuning Advisor utiliza estas propriedades de hardware para gerar uma recomendação. Para mais informações, consulte xp_msver (Transact-SQL).

Tarefas do Consultor de Otimização do Mecanismo de Base de Dados

A tabela seguinte lista tarefas comuns do Database Engine Tuning Advisor e os artigos que descrevem como as realizar.

Tarefa do Assistente de Otimização do Motor de Base de Dados	Artigo
Inicialize e inicie o Assistente de Otimização do Motor de Base de Dados. Crie uma carga de trabalho especificando a cache do plano, criando um script ou gerando um ficheiro ou tabela de traços. Ajuste uma base de dados utilizando a ferramenta de interface gráfica Database Engine Tuning Advisor. Cria ficheiros de entrada XML para ajustar cargas de trabalho. Consulte as descrições das opções da interface de utilizador do Database Engine Tuning Advisor.	Iniciar e usar o Orientador de Otimização do Mecanismo de Banco de Dados
Veja os resultados da operação de ajuste da base de dados. Selecione e implemente recomendações de afinação. Realizar análises exploratórias hipotéticas contra a carga de trabalho. Revê sessões de afinação existentes, clona sessões com base nas existentes ou editar as recomendações de afinação existentes para avaliação ou implementação posterior. Consulte as descrições das opções da interface de utilizador do Database Engine Tuning Advisor.	Visualize e trabalhe com a saída do Database Engine Tuning Advisor