.png)
Fala pessoal, chegamos ao último post da série sobre performance tuning, vou tratar sobre tuning query, como melhorar uma query.
Nos posts anteriores vimos que o tuning são ações que o DBA toma para manter o desempenho do banco de dados (#1) e que para esse trabalho ele utiliza algumas ferramentas para coletar informações(#2). Quando estamos avaliando dentro do SQL Server, devo verificar quais as querys que estão lentas, avaliar o plano de execução delas e entender o caminho que o SQL esta levando para chegar na informação(#3) para só então poder realizar um tuning na query.
Quando estamos olhando para um plano de execução e entendemos o que esta sendo feito, temos geralmente duas possibilidades para mudar o plano de execução para deixá-lo melhor: ajustar estatísticas e índices ou alterar a estrutura da query.
Não são raros os casos onde eu já identifiquei possibilidade de reescrever querys de aplicações e relatórios, um problema comum de se encontrar, pois quando as querys são desenvolvidas na sua maioria são testadas e validadas em um ambiente que não é de produção, que contem poucos dados e isso pode causar a falsa ilusão de que esta tudo bem. Quando essa query vai pra produção e com o crescimento do volume de dados a lentidão começa a surgir, em alguns casos um pequeno ajuste na estrutura já faz uma grande diferença no desempenho. Quem cria tem a melhor das intenções, retornar o que o usuário precisa, porém existem N formas de se fazer isso.
Quando olhamos para estatísticas, que são fundamentais para o bom desempenho das querys, é preciso conferir se estão atualizadas e não estão causando uma estimativa errada que influencie no plano de execução final. O otimizador de consultas precisa de informações estatísticas de quantas linhas retornarão no comando, por exemplo, dependendo da diferença entre a informação disponibilizada pela estatística e o que existe na realidade o tempo da query pode ser muito afetado. Na grande maioria dos casos se você já tem uma boa rotina de manutenção preventiva, é a criação de índices que vai auxiliar na melhora do plano de execução e consequentemente no desempenho das querys.
O que é um índice?
Imagine que você acabou de comprar um livro novo sobre tuning no sql server, você deseja encontrar o capitulo que fala sobre estatísticas, você abre na pagina do índice do livro, que é logo a primeira, ali existe o nome de todos os capítulos e qual a pagina eles começam, você verifica que o capitulo que deseja ver esta na pagina 150, vai rapidamente até ela e encontra o capitulo que queria. Se o índice no inicio do livro não existisse você poderia achar o capitulo também claro, mas precisaria folhar o livro todo até encontrar, com certeza iria demorar muito mais tempo.
No SQL Server funciona da mesma forma, o índice em uma tabela ajuda o SQL a achar os dados mais rapidamente.
Ele pode ser criado para uma coluna ou para um conjunto de colunas e existem dois tipos de índices o clustered e o non-clustered.
Você só pode ter um clustered por tabela e ele é responsável por ordenar, classificar e armazenar as linhas de dados da tabela.
Já os índices non-clustered você pode ter vários em uma mesma tabela, são criados para colunas que não fazem parte do índice clustered mas que são usados também para joins e filtros.
Os índices contribuem e muito para melhorar o desempenho de uma query para realizar leituras, porém gera um custo maior para inserção e atualização de dados. Se você tem 5 índices em uma tabela, ao inserir um dado novo nessa tabela, o SQL terá o custo de inserir o registro na tabela oficial e inserir também o conteúdo na estrutura dos índices.
Otimizando uma query
Case: Select que retorna informações dos clientes que realizaram pedidos entre 20/12/2020 e 23/12/2020. Retorna também algumas informações dos itens dos pedidos, como a soma do peso dos produtos, soma das quantidades e a soma dos valores unitários.
As informações dos itens dos pedidos são retornadas através de subselects.
Com esse formato usando subselects, para cada linha retornada do select principal (imagem abaixo) serão executados mais 3 selects (sendo um cada subselect).
Esse filtro utilizado faz com que o select principal retorne 299.999 linhas (tempo de execução total de 29 segundos)
Plano de execução gerado
Avaliando mais detalhadamente os principais pontos do plano de execução é possível observar os pontos que correspondem aos subselects. Cada leitura na tabela PEDIDO_ITEM esta sendo realizado por um Clustered index scan.
Para cada um dos subselects podemos observar o número estimado de linhas por execução e o tamanho estimado dos dados.
A primeira ação que poderíamos tomar é criar um índice pelo COD_PEDIDO na tabela PEDIDO_ITEM para que o otimizador de consultas mude o plano usando outros operadores ao invés de um Clustered index scan com nested loop.
Índice criado conforme esperado.
Porém o plano de execução continua igual. Isso ocorre porque como o índice contém apenas a coluna COD_PEDIDO o otimizador de consultas continua achando que é melhor usar a PK (realizar um clustered index scan) para realizar a busca pois precisa retornar as colunas PESO, QUANTIDADE e VALOR_UNI.
Se forçarmos a utilização do índice podemos observar o operador key lookup que é um passo a mais que o otimizador precisa dar para usar o índice que criamos e ir no índice clustered da tabela e buscar as demais colunas que são necessárias (PESO, QUANTIDADE e VALOR_UNI).
Para melhorar esse cenário, podemos incluir as colunas PESO, QUANTIDADE e VALOR_UNI no índice, porém não em sua estrutura principal e sim como complemento, podemos fazer isso utilizando a clausula INCLUDE
Agora o plano passa a usar o índice e muda o caminho completamente. Veja o novo plano de execução.
Ao executar a query vemos a diferença no tempo de execução (passou a executar em 3 segundos enquanto antes retornava em 29 segundos).
Essa é uma das atividades que mais geram visibilidade no trabalho do DBA, backups, alta disponibilidade, segurança são extremamente importantes, mas isso não é visível para os usuários finais. O que mais gera impacto pra ele é o desempenho do sistema, o relatório que esta demorando, a ação no sistema que passou a levar mais tempo que antes, quando você otimiza uma query e ela passa a rodar muito mais rápido, o usuário final sente na hora a diferença e então o DBA é mencionado.
Vale lembrar que esse é um trabalho constante, o DBA deve realizar o tuning de forma cíclica, uma vez por semana, uma a cada 15 dias ou no máximo um a cada 30 dias. Coletar as querys mais lentas e trabalhar nelas entendendo o que esta ocorrendo, se é necessário ajuste de hardware, ajuste de alguma configuração de recurso no SQL Server ou se de fato é a query que precisa de ajuste mesmo.
Chegamos ao final da série sobre performance tuning, uma das atividades mais importantes do DBA SQL Server. Fique ligado no blog, nas próximas semanas trarei mais conteúdos importantes como esse.
Nos acompanhe em nossas redes sociais!
Youtube(vídeos novos todas as quartas): https://www.youtube.com/channel/UChFeqc-m7HZNdkoP0CshMGQ
Face & Instagram(conteúdo diário): dba on boarding
Até a próxima, tchau!