Por que eu devo ler este artigo:A otimização de consultas é utilizada para aumentar o desempenho dos SGBDs. Neste artigo serão mostradas algumas técnicas que vão auxiliar na codificação das consultas afim de obter o melhor tempo de resposta, comparando consultas não otimizadas com consultas otimizadas utilizando o Plano de Execução do SQL Server. Este artigo será útil para mostrar os benefícios da aplicação de técnicas de modelagem e programação para a otimizações de consultas utilizando o SQL server. As técnicas serão analisadas e remodeladas para obter um maior desempenho no banco de dados, conseguindo assim mostrar a sua eficácia.
Autores: Joziane Cristina Coutinho e Scarlat Pâmela Silva

As organizações estão recorrendo cada vez mais à Tecnologia da Informação e Comunicação para gerenciar e armazenar os dados que são processados e manipulados no dia a dia. Com isso, o volume de dados vem aumentando consideravelmente, evidenciando a necessidade de otimização dos Sistemas Gerenciadores de Banco de Dados (SGBDS).

Dessa maneira, é primordial que as operações realizadas no banco de dados sejam eficazes e processadas no tempo certo para a tomada de decisão. Para garantir o desempenho e a eficiência das informações acessadas no banco, a otimização deve ser almejada pelos Administradores de Banco de Dados (DBA), em especial a efetivação de consultas que gerem informações rápidas e precisas.

Existem diferentes estratégias de otimização de um sistema de banco de dados baseado no SQL Server, sendo possível aumentar a eficiência e eficácia dos processos. Muitos trabalhos citam técnicas de otimização de consultas em bancos de dados, mostrando sua aplicação e descrevendo os seus benefícios. Contudo até o presente momento nenhum fez um levantamento real do ganho em processamento e tempo na utilização destas técnicas focado diretamente no banco de dados SQL Server.

As técnicas de otimização de consultas que serão analisadas correspondem às de modelagem e programação das consultas no qual o desenvolvedor pode remodela-las para obter maior desempenho no banco de dados. No decorrer do artigo, buscaremos responder a seguinte pergunta: Quais os benefícios em se aplicar algumas técnicas de otimização de consultas em um banco de dados SQL Server? O objetivo central que orienta este artigo é mostrar os benefícios da aplicabilidade de algumas técnicas de otimizações de consultas utilizando o SQL server. Para isso, será feita uma análise das consultas com e sem a utilização das técnicas, comparando o tempo e desempenho de resposta de ambos conseguindo assim demostrar a sua vantagem.

Ferramentas

Neste artigo utilizamos o SQL Server por ser um dos SGBD mais utilizados nas empresas. A execução das consultas com o uso das técnicas de otimização foi realizada na versão 2016, utilizando o banco de dados AdventureWorks 2016CTP3, sendo analisado o plano de execução, estatísticas de tempo, e estatísticas de entrada e saída para obter os resultados. Em todas as consultas foram executados os comandos “DBCCDROPCLEANBUFFERS” e “DBCCFREEPROCCACHE” para limpar o cache do SQL Server, afim de obter um resultado mais preciso das estatísticas.

O plano de execução calcula o caminho mais eficiente para implementar uma requisição representada pelo T-SQL, possibilitando gerar um diagrama com o fluxo percorrido para obter os resultados solicitados, mostrando os índices utilizados, e informações que ajudam o gerenciador de banco no processo de otimização de consultas. Na Figura 1 é apresentado um exemplo de plano de execução.

Demonstração do fluxo do plano de execução
de uma consulta simplificada
Figura 1. Demonstração do fluxo do plano de execução de uma consulta simplificada

A estatística de tempo é visualizada ao executar o comando “SET STATISTICS TIME ON”. Ela mostra o tempo em milissegundos necessário para analisar, compilar e executar cada instrução. A estatística possui o tempo de CPU que é o tempo gasto pela CPU do servidor para processar a consulta e o tempo decorrido que se refere ao tempo de execução da consulta. O valor do tempo da CPU pode variar de acordo com a execução, mas a diferença é mínima. Já o tempo decorrido pode ter uma grande variação pois depende de muitos fatores, como largura de banda de rede entre servidor e cliente, carga no servidor e carga IO. Neste artigo vamos analisar apenas o tempo de execução das consultas. Na Figura 2 é demonstrado o resultado da estatística de tempo de uma consulta.

Resultado ao ativar o SET STATISTICS TIME após execução de uma
consulta
Figura 2. Resultado ao ativar o SET STATISTICS TIME após execução de uma consulta

A estatística de entrada e saída é visualizada ao executar o comando “SET STATISTICS OI ON”. Ela exibe os dados referentes à quantidade de atividades geradas em disco pelas consultas, onde temos:

  1. Tabela: nome da tabela envolvida na consulta;
  2. Número de verificações: número de buscas iniciadas para recuperar todos os valores para saída final;
  3. Leituras lógicas: número de páginas lidas do cache de dados do SQL Server;
  4. Leituras físicas: número de páginas lidas no disco do servidor;
  5. Leituras read-ahead: número de páginas colocadas no cache para a consulta;
  6. Leituras lógicas lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do cache;
  7. Leituras físicas lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do disco do servidor;
  8. Leituras read-ahead lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) adicionadas no cache de dados.

As leituras de lob não foram analisadas no artigo pois em todas as consultas os resultados foram 0 por não possuírem colunas com valor grande. Na Figura 3 é apresentado um exemplo.

Resultado ao ativar o SET STATISTICS OI após execução de uma
consulta
Figura 3. Resultado ao ativar o SET STATISTICS OI após execução de uma consulta

Banco de dados

Um banco de dados é definido como um conjunto de dados armazenados e relacionados para atender as necessidades dos usuários, possibilitando a consulta e manipulação dos dados, seja manual ou computadorizada. Com o passar do tempo e com o avanço tecnológico, os bancos de dados passaram a ser considerados o coração de muitos sistemas, pois oferecem segurança dos dados e ainda auxiliam no acesso rápido às informações para tomada de decisão.

Para gerenciar o ...

Quer ler esse conteúdo completo? Seja um assinante e descubra as vantagens.
  • 473 Cursos
  • 10K Artigos
  • 100 DevCasts
  • 30 Projetos
  • 80 Guias
Tenha acesso completo