No cenário atual do desenvolvimento de software, sabemos que entre vários pontos, a performance de uma aplicação é uma das mais importantes, fazendo muitas vezes com que algumas empresas/times de desenvolvimento sacrifiquem algumas boas práticas para alcançar tal excelência.
É claro que o ideal é sempre tentar balancear as boas práticas, padrões e a performance necessária para sua aplicação, não esquecendo da integração com o seu banco de dados e as consultas que são realizadas nele.
Em grandes empresas, as tarefas do desenvolvedor e do DBA (Database Administrator) são separadas, cada um cuidando do seu interesse, porém em grande parte dos casos, o desenvolvedor tem que desempenhar e acumular várias funções, inclusive a de DBA.
Abordaremos aqui uma ferramenta que é muito útil na análise e auxílio da melhora da performance de consultas do banco de dados, ela é o SQL Server Database Engine Tuning Advisor.
O QUE É?
É uma ferramenta lançada na versão 2005 do SQL Server até então chamada de Database Tuning Advisor e permanece até a versão atual (SQL Server 2016), mas renomeada para Database Engine Tuning Advisor. Essa ferramenta tem por objetivo realizar uma análise de como as consultas são processadas no banco de dados e através disso é capaz de fazer recomendações para otimizar o desempenho das mesmas, através de modificações nas estruturas do banco de dados, tais como a criação de índices, views indexadas e particionamentos.
Essa análise é muito interessante para projetos que aplicam o conceito de Code First utilizando um ORM (Object Relational Mapping) por exemplo. Nesse caso específico não temos controle completo sobre as consultas que são executadas no banco de dados, sendo difícil dimensionar se a estrutura criada está o mais otimizada possivel.
ACESSANDO E UTILIZANDO A FERRAMENTA
A ferramenta pode ser acessada através do caminho na sua instalação, em: “Performance Tools > SQL Server Database Engine Tuning Advisor”
Ou através so menu Tools do seu SQL Server Management Studio
É necessário conectar-se ao seu servidor com suas credenciais
Aparecerá as opções para começarmos uma nova sessão.
Na barra lateral esquerda, temos a listagem de todas as seções criadas e a possibilidade de conectar-se a outro servidor ou instância. Ao lado direito temos uma tela com duas abas:
ABA: General
Tem informações para identificar a sessão e logo abaixo algumas opções de workload, ou seja a consulta que será analisada. Vamos abordar aqui as duas opções mais utilizadas de workload: File (Arquivo) e Table (Tabela).
File: Nesta opção é possível selecionar um arquivo .sql com a query para ser analisada.Há também a possibilidade de selecionar um arquivo .trc que é um formato do SQL Server Profile Trace.
Mas o que é o SQL Server Profile?
Brevemente falando, o SQL Server Profile é outra ferramenta associada ao SQL Server para monitorar as operações e consultas realizadas no banco de dados. Como mencionei no início, desenvolvedores que utilizam um ORM, que por sua vez geram as consultas e as abstraem do desenvolvedor, o SQL Server Profiler é a ferramenta indicada para obter a query resultante desse processo automático e a saída disso pode ser salva em um arquivo do formato .trc.
Para fazer tal procedimento, no seu SQL Server Management Studio, vá até o menu “Tools > SQL Server Profile”, conecte no seu servidor e logo em seguida aparecerá a tela para configurar a trace. Selecione o template “Tuning” (mais recomendado para esses casos), marque a opção “Save to file” e escolha o caminho ao lado.
OBS: É recomendável filtrar a captura das consultas, utilizando a opção “Column Filters” da aba “Events Selection”, afim de evitar que o seu arquivo armazene consultas provenientes de outras bases ou usuários.
Voltando ao SQL Server Database Engine Tuning Advisor, temos outra opção de workload, selecionando uma tabela.
Table: Nessa opção o que é requerido é selecionar uma tabela salva lá no SQL Server Profile, no momento da captura das consultas. A única diferença é que você deverá selecionar a opção “Save to table”.
Na opção “Database for workload analysis” marque o banco de dados a qual a query pertence.
Abaixo temos a opção de selecionar os banco de dados e/ou tabelas que serão analisadas para serem otimizadas. É possível selecionar um banco de dados, mais de um (caso sua query interaja entre diversas bases de dados) ou apenas as tabelas que você tem interesse em analisar e otimizar.
ABA: Tuning Options
Nessa aba temos como especificar as opções de otimização que queremos. Primeiramente é possível definir uma data/hora limite para o processamento dessa análise (importante definir para bases de dados muito grandes e com várias estruturas).
Abaixo temos 3 grupos:
- Physical Design Structures (PDS) To use in database: São as estruturas que poderão ser utilizadas para a otimização;
- Partitioning strategy to employ: A estratégia de particionamento que deverá ser aplicada;
- Physical Design Structures (PDS) To keep in database: São as estruturas que deverão permanecer no banco de dados. Essa opção serve para preservar as estruturas no caso de a otimização identificar que seria melhor excluir alguma delas.
Após essa configuração, clique no botão Start Analysis que encontra-se na barra superior.
A análise será iniciada, com os seguintes passos:
- Enviando as configurações realizadas;
- Consumindo o arquivo ou tabela que foi definida no workload;
- Analisando as consultas;
- Gerando os relatórios;
- Gerando as recomendações de otimização.
Após o processamento, duas abas serão liberadas, Recommendations e Reports
Recommendations: São as recomendações de otimização resultantes da análise das suas consultas em relação ao banco de dados e/ou tabelas selecionadas. Logo no topo ele estima o percentual de melhoria. e abaixo as recomendações de particionamento e de índices.
Você fica livre para escolher quais recomendações quer aplicar, selecionando a caixa na frente de cada uma. Para concretizar e criar as estruturas, clique no menu “Actions” e em seguida em “Apply Recommendations” ou se preferir, pode salvá-las em um arquivo .sql para executá-las quando bem entender.
Reports: São relatórios diversos referentes a situação atual e a otimização, como a taxa de utilização dos índices atuais, entre diversos outros.
OBS: Cuidado! Use com moderação. Não é aconselhável seguir isso cegamente. Lembre-se que tais recomendações são baseadas nas consultas que você informou pra ele, pode ser que para outros casos, o índice criado seja ruim
Qualquer sugestão, dúvida ou esclarecimento, utilize os comentários abaixo.
Valeu!
Referências
Tutorial: Database Engine Tuning Advisor
https://msdn.microsoft.com/en-us/library/ms166575.aspx
Criar um rastreamento (SQL Server Profiler)
https://msdn.microsoft.com/pt-br/library/ms175047(v=sql.120).aspx
Guia de criação de índice do SQL Server
https://msdn.microsoft.com/pt-br/library/jj835095(v=sql.120).aspx
