Database Tuning Advisor (DTA)

Hoje vamos falar de uma Feature bastante importante quando se fala em Tuning.

O DTA (Database Tuning Advisor) é uma ferramenta muito útil e torna fácil e prática a análise de um script ou análise geral do Banco de Dados, com base em determinadas tabelas, ou em seu Plan Cache. A ferramenta pode poupar um bom tempo de análise em cima de um script ou de um Banco de Dados.

O DTA analisa a forma como determinada consulta é executada no Banco de Dados e retorna recomendações de melhoria do desempenho. São retornadas recomendações de criações de índices, Estatísticas, Views Indexadas e Particionamento.

Com essa ferramenta, você pode rodar uma análise em cima de um script, de uma chamada de Stored Procedure, Function, etc. Pode também analisar uma tabela e o Plan Cache do BD.

 

Agora vamos a um exemplo prático, usual e útil dessa ferramenta:

O DTA fica no contexto do SQL Server, no Menu Iniciar.

1

 

Quando você abre ele, é solicitado que conecte em uma instância, para abrir uma sessão.

2

 

A tela inicial da ferramenta tem a interface abaixo. Note que há as opções mencionadas acima. Neste caso vamos testar com a opção “File“, onde selecionaremos a Query que desejamos que o DTA faça a análise (a Query deve ser salva em arquivo anteriormente à execução do DTA).

3

 

Preencheremos os campos da seguinte forma:

Session Name: Um nome qualquer para gravar os dados de configuração e análise da sessão.

Workload: Qual o tipo de análise que deseja. Como escolheremos “File”, deve-se colocar o caminho do arquivo que contém o script que desejamos prover melhoria.

Database for Workload Analisys: Banco de Dados em que ocorrerá o Workload.

Select Databases and tables to tune: Nesta parte deve-se selecionar os objetos que devem ser incluídos na análise. No exemplo que estamos usando, estou selecionando o Banco de Dados “LanzariniDBA”, com todas suas 6 tabelas envolvidas na análise.

4

 

A query que estamos utilizando para a análise está fazendo um Scan em tabela de entrada de Log, com JOIN para tabela de Detalhes dos logs, com filtro por data.

Como podemos ver, inicialmente, a Query está levando mais de 1 minuto para executar e fazendo muita leitura em disco.

(581808 row(s) affected)
> Table ‘LogInformacoes’. Scan count 4, logical reads 360, physical reads 0.
> Table ‘Worktable’. Scan count 4, logical reads 2580256, physical reads 0.
> Table ‘Log’. Scan count 5, logical reads 5919, physical reads 0.
> Table ‘Worktable’. Scan count 0, logical reads 0.

Se ativar o Execution Plan (nesse caso não é o foco, apenas estou usando para provar a eficácia do DTA), podemos ver que há uma indicação de índice que melhoraria a performance dessa Query em 90% (estimado no cálculo interno do SQL Server).

5

 

Na aba “Tuning Options“, temos as configurações para execução do DTA (eu particularmente costumo deixar os valores padrões, pois me interessa mais verificar sugestões de índices e estatísticas).

6

 

O processo será executado e concluirá por etapas. Nesse caso foi muito rápido pois envolvia poucos dados e era somente uma análise de script.

7

 

Na aba “Reports“, você poderá verificar os dados da análise e da sessão do DTA.

8

 

Na aba “Recomendations” é que está o grande foco, pois aqui são mostradas as sugestões e a estimativa de melhoria.

Note abaixo que foi sugerido 1 Índice e 2 Estatísticas, e que isso terá um ganho previsto de 98%. Veja também que para Índices, há uma estimativa do tamanho do índice.

9

 

Para ver os detalhes e criar as sugestões, clique nas informações existentes na coluna “Definition” referente à sugestão que deseja. Nesse caso, vou criar todas. Basta copiar o conteúdo e executar no Banco de Dados.

1011

 

Você pode também salvar todas as sugestões ou já aplicar todas, nas opções da imagem abaixo (“Apply Recommendations” e “Save Recommendations“).

77

 

Após criar o Índice e as Estatísticas, podemos ver uma melhora significativa, tanto com Logical Reads, quanto com tempo de execução e de uso do CPU.

Note também que o Execution Plan já não sugeriu mais nada de melhoria após criar as melhorias, o que combina com a análise do DTA.

(581808 row(s) affected)
Table 'LogInformacoes'. Scan count 27336, logical reads 62696, physical reads 0.
Table 'Log'. Scan count 1, logical reads 5919, physical reads 0.

Logical Reads antes: 2.586.000
Logical Reads depois: 69.000

Tempo antes: 64 segundos
Tempo depois: 25 segundos

13

 

Podemos executar também uma análise em cima do Plan Cache do Banco de Dados, onde o DTA capturará informações mais recentes de uso, e irá sugerir melhorias. A diferença é que nesse caso não será uma análise específica, mas sim uma análise geral, baseada no Plan Cache, que armazenam os dados recentes das transações, dentre outras informações.

Para isso, deve-se criar uma nova sessão no DTA, e selecionar a opção destacada abaixo:

14

 

Note que agora, ele sugeriu também um índice para a tabela [dbo].[Log].

15

 

Com a criação desse índice, agora temos mais uma melhoria em tempo de execução (que agora caiu para 17 segundos), e Logical Reads não obteve ganho. Essas melhorias são comparadas aos dados que foram coletados após aplicar a primeira melhoria.

Note também o Execution Plan, que ficou bem mais simples, onde perdeu o gasto que tinha com ORDER BY e o TABLE SCAN agora virou CLUSTERED INDEX SCAN, que na teoria é muito mais performático.

(581808 row(s) affected)
Table ‘LogInformacoes’. Scan count 27336, logical reads 62696, physical reads 0.
Table ‘Log’. Scan count 1, logical reads 6067, physical reads 0.

16

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s