Otimização de consultas no SQL Server com Tuning Advisor

Neste artigo abordaremos a utilização da ferramenta Database Engine Tuning Advisor, que está presente no SQL Server com recomendações para a melhoria no desempenho de nossas consultas com base em mudanças de estruturas. Veremos mais sobre o seu uso e suas diferentes interfaces através de exemplos simples para a sua utilização.

Temos sempre a necessidade de buscar técnicas diferentes que possibilitem otimizar o desempenho de nossas consultas, como é o caso de atualizações estatísticas, criação de índices, utilização de tabelas de particionamento, dentre outras opções. Isso é bastante útil para o dia a dia de um programador, mas o que poderia melhorar ainda mais a situação seria se tivéssemos algumas recomendações de melhorias antes de começarmos a planejar ou mesmo a otimizar as nossas consultas, para isso, podemos fazer uso do utilitário Database Engine Tuning Advisor, presente no SQL Server, e nos fornece recomendações com base nas nossas cargas de trabalho, ou mais conhecidas, workloads.

Trabalhando com a interface gráfica do DETA

O Database Engine Tuning Advisor – DETA - com base na sua carga de trabalho, nos fornece recomendações para que tenhamos a melhor combinação de índices, tanto clusterizados quanto não clusterizados, ou exibições indexadas, partições não-alinhadas e estatísticas necessárias.

Antes de começarmos a trabalhar com o DETA, o que precisamos inicialmente é coletar a carga de trabalho com base num conjunto de consultas SQL Server que desejamos otimizar e ajustar. Neste caso, podemos usar consultas diretas e arquivos de rastreamento, e traçar tabelas geradas a partir do SQL Server Profiler como opção de entrada para a carga de trabalho ao ajustar os bancos de dados, o que é uma forma de sua utilização.

Quando começarmos a análise com o DETA, ele irá analisar a carga de trabalho prevista e recomenda para adicionar, remover ou modificar estruturas físicas de design presentes em nossos bancos de dados, como por exemplo, a criação de índices e particionamentos. Ele também recomenda que objetos adicionais para estatísticas sejam criados para apoiar as estruturas físicas de design. O DETA fornece scripts T-SQL para que possamos implementar rapidamente a recomendação e obtermos relatórios resumidos sobre os efeitos da aplicação dessas recomendações para a carga de trabalho prevista. Para usarmos de forma interativa, o DETA possui uma interface gráfica bastante agradável e intuitiva para o usuário e, ao usá-lo no modo automático, ele nos fornece uma interface de linha de comando (CLI), por padrão.

Para iniciarmos a interface GUI do DETA, vamos buscar através de pesquisa no Windows por “SQL Server 2014 Database Engine Tuning Advisor”. Ao abri-lo, precisaremos acessar a nossa instância do SQL Server com a nossa base de dados de testes para ajustes. Veja na Figura 1 a exibição em árvore do lado esquerdo, onde podemos ver todas as sessões de ajustes criadas anteriormente. Por padrão, o nome da sessão é o nome do usuário anexado com data e hora em que a sessão foi criada, mas podemos alterar o nome padrão, clicando com o botão direito do mouse sobre a sessão específica na view da árvore e, em seguida, clicamos em "Renomear Session". Já no lado direito podemos perceber que para o nosso workload (carga de trabalho) podemos fornecer um arquivo com consultas, uma tabela ou mesmo um Plain Cache como entradas.

Figura 1. View do Database Advisor Tuning

Agora que fizemos essa análise inicial, começaremos com nosso exemplo para vermos como ele funciona na prática. Para esta etapa utilizaremos o SQL Server 2014 e a base de dados AdventureWorks2012_Database, a qual podemos baixar através do link que está ao final do artigo.

Vamos abrir o SQL Management, e em seguida, execute o script presente na Listagem 1 para criarmos o banco de dados que iremos trabalhar. Em seguida criaremos duas novas tabelas com base nos dados das tabelas SalesOrderHeader e SalesOrderDetail da nossa base de dados. Notem que utilizamos aqui o comando SELECT INTO para criarmos as novas tabelas como tabelas de heap (pilha), ou seja, sem índices das tabelas de origem no banco de dados que criamos anteriormente.

Listagem 1. Criando a base de dados de testes

CREATE DATABASE TuningAdvisorDevmedia
  GO
  USE TuningAdvisorDevmedia
  GO
  SELECT * INTO [SalesOrderHeader] FROM [AdventureWorks2012].Sales.[SalesOrderHeader]
  GO
  SELECT * INTO [SalesOrderDetail] FROM [AdventureWorks2012].Sales.[SalesOrderDetail]
  GO

Percebam que essas estruturas que utilizamos nos retornam vários dados de consulta, como mostra a Figura 2.

Figura 2. Estrutura de tabelas da base de testes

Selecione o nosso gerenciador e clique com o botão direito do mouse sobre ele. Aparecerá as opções de menu, dentre as quais escolheremos a opção “Analyse Query in Database Engine Tuning Advisor”, como mostrado na Figura 3.

Figura 3. Selecionando a opção de Análise de query com o Database Engine Advisor

Use o código da Listagem 2 para iniciar os testes.

Listagem 2. Script para testes.

-- Esta consulta seleciona todos os registros que tenham a data da ordem nesse período
  SELECT *
  FROM SalesOrderHeader SOH
  INNER JOIN SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
  WHERE OrderDate = '2005-12-19 00:00:00.000'
  GO
  -- Esta consulta nos retorna algumas somas referentes a qtd de itens e valores
  SELECT OrderDate, ProductID, SUM(OrderQty) AS OrderQty, SUM(UnitPriceDiscount) AS UnitPriceDiscount, SUM(LineTotal)
  FROM SalesOrderHeader SOH 
  INNER JOIN SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
  GROUP BY OrderDate, ProductID
  HAVING SUM(OrderQty) >= 500
  GO
  -- Esta consulta retorna as informações contidas dentro de um espaço delimitado de tempo
  SELECT SalesOrderNumber, PurchaseOrderNumber, DueDate, ShipDate
  FROM SalesOrderHeader SOH 
  INNER JOIN SalesOrderDetail SOD ON   SOH.SalesOrderID =   SOD.SalesOrderID
  WHERE OrderDate IN ('2005-12-01', '2005-12-14', '2005-12-19')
  GO
  -- Esta consulta retorna uma quantidade de informações com base na numeração do rastreamento
  SELECT SOH.SalesOrderID, SalesOrderNumber, PurchaseOrderNumber, DueDate, ShipDate
  FROM SalesOrderHeader SOH 
  INNER JOIN SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
  WHERE CarrierTrackingNumber IN ('F5E6-4B81-A3', '8A91-4DA7-97')
  ORDER BY SOH.SalesOrderID
  GO

Nessa listagem temos a utilização de quatro consultas. Ao clicarmos na opção de "Analyse Query in Database Engine Tuning Advisor", iremos iniciar a interface gráfica do usuário do Database Engine Tuning Advisor, conforme mostra a Figura 4. Vamos realizar a importação das consultas que selecionamos no gerenciador como a carga de trabalho para esta sessão de ajuste.

Figura 4. O DETA a partir do gerenciador usando consultas de carga de trabalho.

A partir de nossa seleção no gerenciador do SQL Server, realizamos o envio da query que iriamos utilizar e esta informação foi atualizada de forma inteligente no próprio DETA. Como vocês devem ter notado, ao lado da guia “General”, temos a guia “Tuning Options”, que define várias informações para diferentes ajustes de melhorias, como mostra a Figura 5.

Para este artigo não alteraremos as opções apresentadas por padrão, então podemos clicar no ícone "Start Analyses" na barra de ferramentas e deixar o DETA começar a analisar a carga de trabalho prevista e a partir daí, fornecer as recomendações sobre a conclusão.

Figura 5. Especificar diferentes opções de ajuste e análise de começar.

No começo deste artigo criamos duas tabelas heaps e, portanto, com base em associações, o DETA nos forneceu algumas melhorias para serem utilizadas em nossas consultas, como por exemplo, a criação de índices, como foram vários os apresentados. Além disso, ele nos apresenta uma estimativa de melhoria com relação ao desempenho de nossas consultas. O resultado pode ser visto na Figura 6.

Figura 6. Análise de Resultados.

O DETA também nos fornece scripts T-SQL para implementa rapidamente as recomendações feitas pela aplicação. Com base nisso, basta clicarmos nos links presentes na guia de recomendação e veremos o script melhorado para implementação de índices clusterizados e não-clusterizados (dependendo do caso) na tabela SalesOrderDetail, como podemos ver na Figura 7.

Figura 7. Recomendações apresentadas para a melhoria das consultas.

Para que possamos ter diferentes perspectivas e podermos implementar as recomendações sem complicações, podemos ver que o DETA inclui vários tipos de relatórios, onde ao abri-los, podemos descobrir, por exemplo, o custo e a frequência de consultas a partir da carga de trabalho prevista, ou mesmo as relações existentes entre as consultas e os índices aos quais eles fazem referência, além de outras informações importantes, como podemos ver na Figura 8.

Figura 8. Análise de resultados por relatórios.

Através da seleção de cada uma das opções apresentadas, podemos ver um demonstrativo referente à melhoria de desempenho que se espera para cada uma das consultas de carga de trabalho. Podemos ver que temos alguns relatórios referentes aos efeitos de implementação de mudanças, inclusão de índice, distribuição de consultas entre tabelas, e o desempenho das consultas na carga de trabalho. O DETA faz o melhor esforço possível para fornecer recomendações com base na carga de trabalho fornecida, mas que pode não ser o melhor ajuste, uma vez que ele é baseado apenas na carga de trabalho que foi especificada, embora essas recomendações apresentem uma boa maneira para que possamos começar a otimizar o desempenho de nossas consultas.

Trabalhando com interface de linha de comando

O utilitário DTA fornece um arquivo executável, um prompt de comando que podemos usar para realizar o tuning dos bancos de dados. Ele nos permite utilizar o Database Engine Tuning Advisor em arquivos lotes (batches) e scripts. Além disso, ele pode receber plano de cache (plan cache) como entrada, trace files, table traces, e os scripts Transact-SQL como cargas de trabalho, além de também receber entradas XML. Mas para que possamos de fato utilizá-lo, precisamos antes saber de alguns pontos importantes:

• Quando as table traces forem utilizadas como sendo cargas de trabalho (Workload), as tabelas utilizadas para o rastreamento devem estar no mesmo servidor em que o DETA esteja sendo executado;
• Antes de iniciar um table trace, é necessário verificar se nenhum outro rastreamento está em execução;
• Quando um tuning está em execução por muito tempo, pode-se interromper a sessão que está sendo utilizada e, em seguida, utiliza-se as recomendações de melhorias apresentadas.

Veremos agora algumas formas que podemos utilizar a sintaxe do DTA. Como ele não é o foco desse artigo, não o abordaremos por completo. Veremos apenas alguns dos comandos mais comuns.

Para verificar a sintaxe de como podemos utilizar um plan cache para realizar o tuning da nossa base de dados usamos o comando a seguir:

dta -E -D DatabaseName -ip -s SessionName

O “-ip” serve para analisar os 1000 maiores eventos de plan cache ocorridos para a base de dados selecionada. Podemos incluir “-n”, para verificarmos uma nova quantidade de eventos, como mostrado a seguir:

dta -E -D DatabaseName -ip –n 500 -s SessionName1

Em casos que precisarmos utilizar as configurações padrão do utilitário DTA com um workload para realizar o tuning da base de dados, podemos utilizar a seguinte sintaxe:

dta -E -D DatabaseName -if WorkloadFile -s SessionName

O parâmetro “-E” especifica que a sessão do tuning utiliza uma conexão segura (no lugar de login e senha). Já o parâmetro “-D” especifica o nome do banco de dados que desejamos realizar o tuning.

A opção “–if” especifica o nome e o caminho para um arquivo de workload e “-s” especifica um nome para a sua sessão de tuning.

Repare que algumas informações aqui são obrigatórias, como é o caso do nome da base de dados, o nosso workload, a conexão e o nome da sessão.

Por padrão, o utilitário se conecta à instância padrão do SQL Server no computador local. Caso queira acessar um banco de dados remotamente, é necessário utilizar a opção “-s” para especificá-lo, como no exemplo a seguir:

dta -S ServerName\Instance -D DatabaseName -it WorkloadTableName 
  -U LoginID -P Password -s SessionName -A TuningTimeInMinutes

O parâmetro “–S” especifica um nome do servidor remoto e sua instância, podendo também ser uma instância nomeada no servidor local. Já o parâmetro “-D” especifica o nome do banco de dados que desejamos realizar o tuning. A opção -it especifica o nome da tabela de workload, e o “-U” e o “-P” especificam o loginID e a senha para o banco de dados remoto especificamente.

O parâmetro “-s” especifica o nome da sessão de tuning e o “-A” especifica a duração da sessão de tuning em minutos.

Executando o Workload com o SQL Server 2014 Profiler

O SQL Server Profiler é uma interface gráfica utilizada pelo usuário para rastreamento e monitoramento de uma instância do Mecanismo de Banco de Dados SQL ou mesmo uma Analysis Services. Neste momento, podemos capturar e salvar os dados sobre cada evento ocorrido para um arquivo ou uma tabela que desejarmos para que possamos realizar a análise posteriormente, como por exemplo, podemos monitorar um ambiente de produção para ver quais stored procedures estão afetando o desempenho, tornando-a mais lenta.

O SQL Server Profiler possui vários templates que podemos utilizar como base para nossas aplicações, dentre eles, temos o template para Tuning. Este template foi desenvolvido especificamente para coletar as informações necessárias para a otimização do desempenho que desejamos. Para que possamos trabalhar com essa opção, voltemos ao SQL Server e selecionemos a SQL Server Profiler presente na aba Tools. Ao fazer isso, ele irá abrir uma nova instância do SQL Server Profile, onde precisaremos passar nossas credenciais de acesso. Feito isso, criaremos o nosso Trace (rastreio) definindo algumas propriedades, como a seleção do o nosso tuning Template, na guia “General”, dando ao nosso Trace um título, como podemos ver na Figura 9.

Figura 9. Propriedades de trace

Na guia de seleção de eventos (Events Selection), além das opções já marcadas, adicionaremos mais algumas através do botão “Column filters”, como mostra a Figura 10.

Figura 10. Acessando o Column filters.

Figura 11. Executando a aplicação.

Ao clicarmos em column filters, uma nova tela para edição dos filtros será apresentada, como mostra a Figura 11. Nesta, dentre as opções apresentadas, selecionaremos o DatabaseName e atribuiremos um nome para ela. Esta opção será a utilizada no momento de rastreamento em que a query de otimização será gerada. Depois de salvar as alterações clicamos em “Run” e após alguns instantes, clique em “stop selected trace” para salvarmos o arquivo de rastreamento. Temos várias opções que podemos utilizar para realizar este salvamento como, por exemplo, trace template, trace xml, dentre outras.

Neste momento, salvaremos apenas como um trace file (arquivo de rastreio) sob o nome de TesteDvmedia.trc. Agora que temos o nosso arquivo salvo, fechemos o SQL Server Profile e voltemos para o Database Engine Tuning Advisor.

Percebam que para a utilização do SQL Server Profiler na coleta e rastreio das consultas, ele tem uma sobrecarga e, devido a isso, é recomendado que seja feito quando realmente houver esta necessidade ou que seja realizado em ambientes de testes.

SQL Server Profiler com Database Engine Tuning Advisor

Uma vez que tenhamos nosso arquivo de rastreamento criado com a nossa workload, necessária para a realização da otimização, voltaremos a utilizar o Database Engine Tuning Advisor, o qual, selecionaremos através da aba ferramentas, presente no SQL Server Profile (usaremos o mesmo arquivo de workload gerado anteriormente). Após abrirmos o DETA e realizar as configurações necessárias, clicaremos no ícone “start Analysis”, presente na barra de ferramentas do DETA, para que seja iniciada a análise da carga de trabalho, a fim de fornecer recomendações para otimização, como apresentado anteriormente. A diferença que dessa vez utilizaremos um caminho diferente.

Com isso finalizamos este artigo, onde tratamos de uma pequena fração referente a gama de informações que teremos com relação ao Database Engine Tuning Advisor e como ele nos ajuda a obter recomendações com base nas cargas de trabalho que apresentamos para otimização de desempenho.

Esperamos que tenham gostado e até a próxima! =)

Links

Base de dados
https://msftdbprodsamples.codeplex.com/releases/view/93587

DTA
https://msdn.microsoft.com/en-us/library/ms162812.aspx

SQL Server Profile
https://msdn.microsoft.com/pt-br/library/ms181091.aspx