Database Engine Tuning AdvisorDatabase Engine Tuning Advisor

Este tópico aplica-se a: Simdo SQL Serversembanco de dados do SQL AzuresemAzure SQL Data Warehouse sem Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

O DTA (Orientador de Otimização do Mecanismo de Banco de Dados) do MicrosoftMicrosoft analisa bancos de dados e faz recomendações que você pode usar para otimizar desempenho de consulta.The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. 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 e partições de tabela sem precisar de conhecimentos avançados sobre a estrutura do banco de dados ou dos recursos internos do SQL ServerSQL Server.You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. Com o DTA, é possível executar as tarefas a seguir.Using the DTA, you can perform the following tasks.

  • Solucionar problemas de desempenho de uma consulta de problema específicaTroubleshoot the performance of a specific problem query

  • Ajustar um conjunto grande de consultas por um ou mais bancos de dadosTune a large set of queries across one or more databases

  • Executar uma análise E-Se exploratória de possíveis alterações de design físicoPerform an exploratory what-if analysis of potential physical design changes

  • Gerenciar o espaço de armazenamentoManage storage space

Benefícios do Orientador de Otimização do Mecanismo de Banco de DadosDatabase Engine Tuning Advisor Benefits

A otimização do desempenho de consulta pode ser difícil sem uma compreensão completa da estrutura de banco de dados e as consultas que são executadas no banco de dados.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. O Orientador de Otimização do Mecanismo de Banco de Dados pode fazer essa tarefa com mais facilidade analisando o cache de planos da consulta atual ou analisando uma carga de trabalho de consultas Transact-SQLTransact-SQL que você cria e recomendando um design físico apropriado.The Database Engine Tuning Advisor can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. Para administradores de banco de dados mais experientes, o DTA expõe um mecanismo avançado para executar a análise E-Se exploratória de diferentes alternativas de design físico.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. O DTA pode fornecer as seguintes informações.The DTA can provide the following information.

  • Recomendar a melhor combinação de índices rowstore e columnstore para bancos de dados utilizando o otimizador de consulta para analisar consultas em uma carga de trabalho.Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • Recomendar partições alinhadas ou desalinhadas para bancos de dados referenciados em uma carga de trabalho.Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • Recomendar exibições indexadas para bancos de dados referenciados em uma carga de trabalho.Recommend indexed views for databases referenced in a workload.

  • Analisar os efeitos das mudanças propostas, inclusive o uso de índice, distribuição de consultas entre tabelas e desempenho de consultas na carga de trabalho.Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • Recomendar modos de ajuste do banco de dados para um pequeno conjunto de consultas de problema.Recommend ways to tune the database for a small set of problem queries.

  • Permitir a personalização da recomendação especificando opções avançadas, como restrição de espaço em disco.Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • Fornecer relatórios que resumam os efeitos de implementação das recomendações para uma determinada carga de trabalho.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • Considerar alternativas em que você fornece possíveis escolhas de design no formulário de configurações hipotéticas para avaliação do Orientador de Otimização do Mecanismo de Banco de Dados.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • Ajustar cargas de trabalho de uma variedade de fontes, incluindo arquivo e tabela do Repositório de Consultas do SQL Server, do Plan Cache, de Rastreamento do SQL Server Profiler ou um arquivo .SQL.Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

O Orientador de Otimização do Mecanismo de Banco de Dados é projetado para tratar os seguintes tipos de cargas de trabalho de consulta.The Database Engine Tuning Advisor is designed to handle the following types of query workloads.

  • Processamento de transações online (OLTP) somente consultaOnline transaction processing (OLTP) queries only

  • Processamento analítico online (OLAP) somente consultaOnline analytical processing (OLAP) queries only

  • Consultas OLAP e OLTP mistasMixed OLTP and OLAP queries

  • Cargas de trabalho de consulta pesadas (mais consultas do que modificações de dados)Query-heavy workloads (more queries than data modifications)

  • Cargas de trabalho de atualização pesadas (mais modificações de dados do que consultas)Update-heavy workloads (more data modifications than queries)

Componentes e conceitos do DTADTA Components and Concepts

Interface gráfica do usuário do Orientador de Otimização do Mecanismo de Banco de DadosDatabase Engine Tuning Advisor Graphical User Interface
Uma interface fácil de usar na qual você pode especificar a carga de trabalho e selecionar várias opções de ajuste.An easy-to-use interface in which you can specify the workload and select various tuning options.

Utilitáriodta dta Utility
A versão do prompt de comando do Orientador de Otimização do Mecanismo de Banco de Dados.The command prompt version of Database Engine Tuning Advisor. 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.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

carga de trabalhoworkload
Um arquivo de script, um arquivo de rastreamento ou uma tabela de rastreamento Transact-SQL que contém uma carga de trabalho representativa para os bancos de dados a serem ajustados.A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. Começando com o SQL Server 2012 (11.x)SQL Server 2012 (11.x), você pode especificar o cache de planos como carga de trabalho.Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), you can specify the plan cache as the workload. A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x), é possível especificar o Repositório de Consultas como a carga de trabalho.Beginning with with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can specify the Query Store as the workload.

Arquivo de entrada XMLXML input file
Um arquivo formatado pelo XML que o Orientador de Otimização do Mecanismo de Banco de Dados pode usar para ajustar cargas de trabalho.An XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. O arquivo de entrada XML dá suporte a opções de ajuste avançadas que não estão disponível na GUI nem no utilitário dta .The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

Limitações e restriçõesLimitations and Restrictions

O Orientador de Otimização do Mecanismo de Banco de Dados tem as seguintes limitações e restrições:The Database Engine Tuning Advisor has the following limitations and restrictions.

  • Ele não pode adicionar ou remover índices exclusivos ou índices que impõem restrições PRIMARY KEY ou UNIQUE.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • Ele não pode analisar um banco de dados que está definido para modo de usuário único.It cannot analyze a database that is set to single-user mode.

  • Se você especificar um espaço máximo em disco para ajustar recomendações que excedem o espaço disponível real, o Orientador de Otimização do Mecanismo de Banco de Dados usará o valor especificado.If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. No entanto, quando você executa o script de recomendação para implementá-lo, o script poderá apresentar erro se antes não for adicionado mais espaço em disco.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. 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 de Ajuste Avançadas .Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • Por razões 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.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. Para driblar essa limitação, você pode usar um arquivo de rastreamento em vez de uma tabela de rastreamento ou pode copiar a tabela de rastreamento para o servidor remoto.To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • Quando você impõe restrições, como as impostas quando você especifica um espaço máximo em disco ao ajustar recomendações (usando a opção -B ou a caixa de diálogo Opções de Ajuste Avançadas ), o Orientador de Otimização do Mecanismo de Banco de Dados pode ser forçado a remover certos índices existentes.When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. Nesse caso, a recomendação Orientador de Otimização do Mecanismo de Banco de Dados resultante pode produzir um aperfeiçoamento esperado negativo.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • Quando você especifica uma restrição para limitar o tempo de ajuste (usando a opção -A com o utilitário dta ou marcando Limitar tempo de ajuste na guia Opções de Ajuste ), o Orientador de Otimização do Mecanismo de Banco de Dados pode exceder o tempo limite para produzir um aperfeiçoamento esperado preciso e os relatórios de análise para as porções da carga de trabalho consumidas até o momento.When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • O Orientador de Otimização do Mecanismo de Banco de Dados não faz recomendações nas seguintes circunstâncias:Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. A tabela que está sendo ajustada contém menos de 10 páginas de dados.The table being tuned contains less than 10 data pages.

    2. Os índices recomendados não oferecem aperfeiçoamento suficiente no desempenho da consulta no design do banco de dados físico atual.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. O usuário que executa o Orientador de Otimização do Mecanismo de Banco de Dados não é um membro da função de banco de dados db_owner nem da função de servidor fixa sysadmin .The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. 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.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. O usuário deve ser um membro da função de banco de dados db_owner .The user must be a member of the db_owner database role.

  • O Orientador de Otimização do Mecanismo de Banco de Dados armazena dados de sessão de ajuste e outras informações no banco de dados msdb .Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Se forem feitas mudanças no banco de dados msdb você poderá perder dados da sessão de ajuste.If changes are made to the msdb database you may risk losing tuning session data. Para eliminar esse risco, implemente uma estratégia de backup apropriada para o banco de dados msdb .To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Considerações sobre desempenhoPerformance Considerations

O Orientador de Otimização do Mecanismo de Banco de Dados pode consumir recursos de processador e memória significativos durante a análise.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. Para evitar a redução de velocidade do servidor de produção, siga uma destas estratégias:To avoid slowing down your production server, follow one of these strategies:

  • Ajuste os bancos de dados quando o servidor estiver livre.Tune your databases when your server is free. O Orientador de Otimização do Mecanismo de Banco de Dados pode afetar o desempenho da tarefa de manutenção.Database Engine Tuning Advisor can affect maintenance task performance.

  • Use o recurso servidor de teste/servidor de produção.Use the test server/production server feature. Para obter mais informações, consulte Reduzir a carga de ajuste do servidor de produção.For more information, see Reduce the Production Server Tuning Load.

  • Especifique só as estruturas de design de banco de dados físico que você quer que o Orientador de Otimização do Mecanismo de Banco de Dados analise.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. O Orientador de Otimização do Mecanismo de Banco de Dados fornece muitas opções, mas só especifica as que são necessárias.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Dependência em Procedimento armazenado estendido xp_msverDependency on xp_msver Extended Stored Procedure

O Orientador de Otimização do Mecanismo de Banco de Dados depende do procedimento armazenado estendido xp_msver para fornecer a funcionalidade completa.Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. Esse procedimento armazenado estendido é ativado por padrão.This extended stored procedure is turned on by default. 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 onde o banco de dados que está sendo ajustado está localizado.Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. Se o 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 no qual o Orientador de Otimização do Mecanismo de Banco de Dados está sendo executado.If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. Se as características de hardware do computador onde o Orientador de Otimização do Mecanismo de Banco de Dados está sendo executado não estiverem disponíveis, supõe-se um processador e 1024 megabytes (MBs) de memória.If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

Essa dependência afeta as recomendações de particionamento porque o número de partições recomendado depende destes dois valores (número de processadores e memória disponível).This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). A dependência também afeta os resultados de ajustes quando você usar um servidor de teste para ajustar o servidor de produção.The dependency also affects your tuning results when you use a test server to tune your production server. Nesse cenário,o Orientador de Otimização do Mecanismo de Banco de Dados usa o xp_msver para buscar propriedades de hardware do servidor de produção.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. Após ajustar a carga de trabalho no servidor de teste, o Orientador de Otimização do Mecanismo de Banco de Dados usa estas propriedades de hardware para gerar uma recomendação.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. Para obter mais informações, veja xp_msver (Transact-SQL).For more information, see xp_msver (Transact-SQL).

Tarefas do Orientador de Otimização do Mecanismo de Banco de DadosDatabase Engine Tuning Advisor Tasks

A tabela a seguir lista as tarefas comuns do Orientador de Otimização do Mecanismo de Banco de Dados e os tópicos que descrevem como executá-los.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

Tarefa do Orientador de Otimização do Mecanismo de Banco de DadosDatabase Engine Tuning Advisor Task TópicoTopic
Inicializar e iniciar o Orientador de Otimização do Mecanismo de Banco de Dados.Initialize and start the Database Engine Tuning Advisor.

Criar uma carga de trabalho especificando o cache de planos, criando um script ou gerando um arquivo de rastreamento ou uma tabela de rastreamento.Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

Ajuste um banco de dados usando a ferramenta de interface gráfica Orientador de Otimização do Mecanismo de Banco de DadosTune a database by using the Database Engine Tuning Advisor graphical user interface tool.

Criar arquivos de entrada XML para ajustar cargas de trabalho.Create XML input files to tune workloads.

Exibir as descrições das opções de interface do usuário do Orientador de Otimização do Mecanismo de Banco de Dados.View descriptions of the Database Engine Tuning Advisor user interface options.
Iniciar e usar o Orientador de Otimização do Mecanismo de Banco de DadosStart and Use the Database Engine Tuning Advisor
Exibir os resultados da operação de ajuste do banco de dados.View the results of the database tuning operation.

Selecionar e implementar recomendações de ajuste.Select and implement tuning recommendations.

Executar uma análise E-Se exploratória na carga de trabalho.Perform what-if exploratory analysis against the workload.

Revisar sessões de ajuste existente, clonar sessões com base nas existentesReview existing tuning sessions, clone sessions based on existing ones
ou editar recomendações de ajuste existente para avaliação adicional ou implementação.or edit existing tuning recommendations for further evaluation or implementation.

Exibir as descrições das opções de interface do usuário do Orientador de Otimização do Mecanismo de Banco de Dados.View descriptions of the Database Engine Tuning Advisor user interface options.
Exibir e trabalhar com a saída do Orientador de Otimização do Mecanismo de Banco de DadosView and Work with the Output from the Database Engine Tuning Advisor