Lição 2: Usando o Orientador de Otimização do Mecanismo de Banco de Dados

Aplica-se a:SQL Server

O Database Engine Tuning Advisor ajuda-o a ajustar bases de dados, gerir sessões de afinação e visualizar recomendações de afinação. Os usuários com conhecimento avançado de estruturas de projeto físico podem usar essa ferramenta para realizar análises exploratórias de ajuste de banco de dados. Os novatos em ajuste de banco de dados também podem usar a ferramenta para encontrar a melhor configuração de estruturas de projeto físico para as cargas de trabalho que ajustam. Esta lição fornece prática básica para administradores de bases de dados que são novos na interface gráfica do Database Engine Tuning Advisor e para administradores de sistemas que possam não ter um conhecimento aprofundado de estruturas físicas de design.

Pré-requisitos

Para concluir este tutorial, você precisa do SQL Server Management Studio, acesso a um servidor que esteja executando o SQL Server e um banco de dados AdventureWorks2025.

• Instale o SQL Server Management Studio.
• Instale SQL Server 2017 Developer Edition.
• Baixe os bancos de dados de exemplo do AdventureWorks .

Para instruções sobre restauração de bases de dados no SSMS, consulte Restaurar uma Cópia de Segurança da Base de Dados Usando SSMS.

Ajustar a carga de trabalho

Utilize o Database Engine Tuning Advisor para encontrar o melhor design físico da base de dados para o desempenho das consultas nas bases de dados e tabelas que escolher para otimização.

1. Copie um exemplo de instrução SELECT e cole essa declaração no Editor de Consultas do SQL Server Management Studio. Guarda o ficheiro num MyScript.sql diretório onde o possas encontrar facilmente. Um exemplo que funciona contra a AdventureWorks base de dados é fornecido no código seguinte.

   USE [AdventureWorks2022];
   GO
   
   -- Might need to modify database name to match database
   SELECT DISTINCT pp.LastName,
                   pp.FirstName
   FROM Person.Person AS pp
        INNER JOIN HumanResources.Employee AS e
            ON e.BusinessEntityID = pp.BusinessEntityID
   WHERE pp.BusinessEntityID IN (
       SELECT SalesPersonID
       FROM Sales.SalesOrderHeader
       WHERE SalesOrderID IN (
           SELECT SalesOrderID
           FROM Sales.SalesOrderDetail
           WHERE ProductID IN (
               SELECT ProductID
               FROM Production.Product AS p
               WHERE ProductNumber = 'BK-M68B-42')));
   

   

2. Inicie o Assistente de Otimização do Motor de Base de Dados. Selecione Orientador de Otimização de Banco de Dados no menu Ferramentas do SQL Server Management Studio (SSMS). Para obter mais informações, consulte Iniciar o Assessor de Otimização do Mecanismo de Base de Dados. Conecte-se ao SQL Server na caixa de diálogo Conectar ao Servidor .

3. No separador Geral do painel direito da interface gráfica Database Engine Tuning Advisor, escreva MySessionnome da Sessão.

4. Selecione Arquivo para sua carga de trabalho e selecione o ícone de binóculos para Procurar um arquivo de carga de trabalho. Localiza o MyScript.sql ficheiro que guardaste no Passo 1.

   

5. Selecione AdventureWorks2025 na lista Banco de dados para análise de carga de trabalho , selecione AdventureWorks2025 na grade Selecionar bancos de dados e tabelas para ajustar e selecione Salvar log de ajuste. Banco de dados para análise de carga de trabalho especifica o primeiro banco de dados ao qual o Orientador de Otimização do Mecanismo de Banco de Dados se conecta ao ajustar uma carga de trabalho. Após o início do ajuste, o Orientador de Otimização do Mecanismo de Banco de Dados se conecta aos bancos de dados especificados pelas instruções USE DATABASE contidas na carga de trabalho.

   

6. Selecione o separador Opções de Afinação . Não defines opções de afinação para esta prática, mas tira um momento para rever as opções de afinação padrão. Prima F1 para ver a Ajuda desta página com separadores. Selecione Opções Avançadas para ver mais opções de afinação. Selecione Ajuda na caixa de diálogo Opções Avançadas de Afinação para obter informações sobre as opções de afinação que aí são apresentadas. Selecione Cancelar para fechar a caixa de diálogo Opções Avançadas de Ajuste , deixando as opções padrão selecionadas.

   

7. Selecione o botão Iniciar Análise na barra de ferramentas. Enquanto o Orientador de Otimização do Mecanismo de Banco de Dados está analisando a carga de trabalho, você pode monitorar o status na guia Progresso . Quando o ajuste estiver concluído, a guia Recomendações será exibida.

   Se receber um erro sobre a data e hora de parada de sintonia, verifique a Stop na hora na guia principal Configurações de Sintonia. Certifique-se de que a data e hora na opção Stop na sejam maiores do que a data e hora atuais e, se necessário, altere-as.

   

8. Depois de concluir a análise, guarde a sua recomendação como um script Transact-SQL selecionando Guardar Recomendações no menu Ações . Na caixa de diálogo Guardar Como , navegue até ao diretório onde quer guardar o script de recomendações e escreva o nome MyRecommendationsdo ficheiro .

   

Ver recomendações de ajuste

1. Na guia Recomendações, use a barra de deslocamento na parte inferior da página com separadores para visualizar todas as colunas de Recomendações de Índice. Cada linha representa um objeto de base de dados (índices ou vistas indexadas) que o Database Engine Tuning Advisor recomenda que elimine ou crie. Desloce-se até à coluna mais à direita e selecione uma Definição. O Assistente de Ajuste do Motor de Banco de Dados exibe uma janela de Visualização de Script SQL onde pode visualizar o script de Transact-SQL que cria ou elimina o objeto de banco de dados naquela linha. Selecione Fechar para fechar a janela de pré-visualização.

   Se estiver a ter dificuldade em localizar uma Definição que contenha um link, selecione para limpar a caixa de seleção Mostrar objetos existentes no final da página com abas. Esta ação diminui o número de linhas apresentadas. Quando você desmarca essa caixa de seleção, o Orientador de Otimização do Mecanismo de Banco de Dados mostra apenas os objetos para os quais ele gerou uma recomendação. Marque a caixa de seleção Mostrar objetos existentes para exibir todos os objetos de banco de dados que existem atualmente no AdventureWorks2025 banco de dados. Use a barra de rolagem no lado direito da página com guias para exibir todos os objetos.

   

2. Clique com o botão direito do rato na grelha no painel Recomendações de Índice . Este menu do botão direito do rato permite que selecione e desselecione recomendações. Permite-lhe também alterar a fonte para o texto da tabela.

   

3. No menu Ações , selecione Guardar Recomendações para guardar todas as recomendações num único script Transact-SQL. Nomeia o script MySessionRecommendations.sql.

   Abra o MySessionRecommendations.sql script no Editor de Consultas do SQL Server Management Studio para o visualizar. Podes aplicar as recomendações à AdventureWorks2025 base de dados de exemplo executando o script no Editor de Consultas, mas não faças isso. Feche o script no Editor de Consultas sem executá-lo.

   Como alternativa, pode também aplicar as recomendações selecionando Aplicar Recomendações no menu Ações do Database Engine Tuning Advisor, mas não aplique essas recomendações agora nesta prática.

4. Se existir mais de uma recomendação na guia Recomendações , limpe algumas das linhas que listam objetos de banco de dados na grade Recomendações de Índice .

5. No menu Ações , selecione Avaliar Recomendações. Database Engine Tuning Advisor cria uma nova sessão de otimização onde pode avaliar um subconjunto das recomendações originais de MySession.

6. Digite EvaluateMySession o nome da sua nova Sessão e selecione o botão Iniciar Análise na barra de ferramentas. Repita os Passos 2 e 3 nesta nova sessão de afinação para ver as suas recomendações.

Resumo

Pode precisar de avaliar um subconjunto de recomendações de ajuste se precisar de alterar as opções de ajuste após executar uma sessão. Por exemplo, pode pedir ao Database Engine Tuning Advisor para considerar vistas indexadas quando especificar opções de ajuste para uma sessão, mas depois de gerada a recomendação decide não usar vistas indexadas.

Use a opção Avaliar Recomendações no menu Ações para que o Database Engine Tuning Advisor reavalie a sessão sem considerar as visualizações indexadas. Quando utiliza a opção Avaliar Recomendações , as recomendações geradas anteriormente são hipoteticamente aplicadas ao design físico atual para chegar ao design físico da segunda sessão de afinação.

Pode visualizar mais informações sobre os resultados de afinação no separador Relatórios, que é descrito na próxima tarefa desta lição.

Ver relatórios de ajuste

Embora seja útil visualizar os scripts que implementam os resultados da afinação, o Database Engine Tuning Advisor também fornece muitos relatórios úteis que pode consultar. Estes relatórios fornecem informações sobre as estruturas de design físico existentes na base de dados que está a ajustar e sobre as estruturas recomendadas. Consulte os relatórios de ajuste selecionando o separador Relatórios .

1. Selecione a guia Relatórios no Orientador de Otimização de Banco de Dados.

2. No painel Resumo de Ajuste , você pode exibir informações sobre essa sessão de ajuste. Use a barra de rolagem para exibir todo o conteúdo do painel. Observe a melhoria percentual esperada e o espaço usado por recomendação. Podes limitar o espaço usado pela recomendação quando defines as opções de afinação. Na guia Opções de ajuste , selecione Opções avançadas. Verifique Definir espaço máximo para recomendações e especifique em megabytes o espaço máximo que uma configuração de recomendação pode usar. Use o botão Voltar no navegador de ajuda para retornar a este tutorial.

   

3. No painel Relatórios de Ajuste, selecione Relatório de Custo de Instrução na lista Selecione relatório. Se precisar de mais espaço para exibir o relatório, arraste a borda do painel Monitor de Sessão para a esquerda. Cada instrução Transact-SQL executada em relação a uma tabela em seu banco de dados tem um custo de desempenho associado a ela. Reduza este custo de desempenho criando índices eficazes nas colunas frequentemente acedidas numa tabela. Este relatório mostra a melhoria percentual estimada entre o custo original de execução de uma instrução na carga de trabalho e o custo se a recomendação de ajuste for implementada. A quantidade de informação contida no relatório baseia-se na duração e complexidade da carga de trabalho.

   

4. Clique com o botão direito no painel de relatório de custos do extrato na área da grelha e selecione Exportar para Ficheiro. Guarde o relatório como MyReport. Uma extensão .xml é automaticamente acrescentada ao nome do arquivo. Pode abrir MyReport.xml no seu editor XML favorito ou no SQL Server Management Studio para ver o conteúdo do relatório.

5. Retorne à guia Relatórios do Orientador de Otimização do Mecanismo de Banco de Dados e clique com o botão direito do mouse no relatório de custo da declaração novamente. Analise as outras opções disponíveis. Podes mudar a fonte do relatório que estás a ver. Alterar a fonte aqui também a altera nas outras abas.

6. Selecione outros relatórios na lista de relatórios Select para se familiarizar com eles.

Resumo

Exploraste o separador de Relatórios da interface gráfica do Database Engine Tuning Advisor para a MySession sessão de afinação. Pode usar estes mesmos passos para explorar os relatórios gerados para a EvaluateMySession sessão de ajuste. Clique duas vezes em EvaluateMySession no painel Monitor de Sessão para começar.

Próximo passo