Melhoria de desempenho utilizando estatísticas e índices

[rotulo-mentoring/]

[lead-mentoring]Este artigo descreve o comportamento das estatísticas no banco de dados e apresenta soluções que podem ser utilizadas para os problemas encontrados na elaboração do plano de execução, além de fornecer diretrizes para o uso eficiente de estatísticas de otimização de consultas com o auxílio de índices bem definidos.

O conhecimento apresentado neste artigo é útil para apoiar as atividades referentes à procura por um melhor desempenho do SGBD. Sem dúvidas, o uso correto de estatísticas e índices contribui muito para este fim.[/lead-mentoring]

Indexação se tornou um ponto chave no cenário de bancos de dados. Um problema existente em banco de dados refere-se à necessidade de não se preocupar muito com a forma com que as consultas SQL são executadas e informadas ao otimizador de consultas que verifica, em primeiro lugar, se existe um plano disponível para sua execução.

Não havendo, ele compila um plano de forma automática. Para executar de forma eficaz, é necessário ser capaz de estimar as linhas intermediárias que seriam geradas a partir das várias estratégias e alternativas para o retorno de um resultado.

O Database Engine mantém estatísticas sobre a distribuição dos valores das chaves em cada índice da tabela, e usa essas estatísticas para determinar qual índice(s) usar ao compilar o plano de consulta. Se, no entanto, há problemas com estas estatísticas, o desempenho das consultas será afetado.

Se não há estatísticas, o otimizador terá que estimar a contagem de linhas ao invés de estimá-las, e acreditem: isso não é o que se quer e pode estar aqui o problema da lentidão em executar certas consultas.

Existem várias formas de descobrir que ambos os planos de execução, o estimado e o real, possuem problemas quando o otimizador se depara com a falta de estatísticas.

Neste caso, existirá avisos no plano, como um ponto de exclamação ou um “x” indicando erro, no plano de execução e um aviso nas informações, assim como mostra a Figura 1.

Figura 1. Colunas sem estatísticas.

Havendo a necessidade de visualizar os planos de execução de consultas anteriores, pode-se solicitar esses planos pelo uso do comando sys.dm_exec_cached_plans DMV. Há várias ocasiões em que se precisa visualizar estatísticas no histórico, seja para saber de alguma alteração que foi realizada ou para ter um acompanhamento e monitoramento dos processos. Antes de ir para uma discussão profunda, é necessário ter ideia sobre alguns termos utilizados na indexação:

· Table Scan: Sempre que é invocada uma consulta em um banco de dados, o otimizador de consulta SQL está tentando buscar a melhor forma possível para executar a consulta a fim de se obter um melhor desempenho.

Neste processo, quando o otimizador de consultas determina que não há índices úteis disponíveis para produzir o conjunto de resultados de saída, então ele faz a verificação da tabela toda. Assim, o Table Scan precisa inspecionar toda a tabela, linha por linha, a fim de obter os resultados esperados.

Isso é muito lento e é recomendado evitar, pois há alguns casos em que os Table Scan são mais rápidos do que o Index Scan, por exemplo, em tabela com poucos dados;

· Index Scan: estrutura de dados auxiliar usada para acelerar o acesso a dados dentro do banco de dados. Aqui, não irá se fazer um rastreamento em toda a tabela, mas irá se buscar diretamente as linhas que qualificam a consulta do requisitante. Para tanto, a estrutura de dados é mantidaseparada para sustentar as informações sobre os valores da chave da tabela.

A principal propriedade do índice é ser ordenado. Por exemplo, no caso de um dicionário, todas as palavras estão em forma ordenada, de modo que quando se procura qualquer palavra, pula-se diretamente para a página correspondente relacionada com a palavra. Da mesma forma, o motor de banco de dados está em busca das linhas exigidas quando a(s) coluna(s) correspondente é indexada.

Bom, se isso ocorre de forma correta, então o que ocorre de forma errada, e o que pode ser feito para colocar as coisas no seu devido lugar? Vamos percorrer os problemas mais comuns e explicar como isso ocorre e de que forma pode ser resolvido. A ideia geral deste artigo é apresentar de forma sucinta alguns problemas que envolvem o uso de estatísticas e índices dentro do banco de dados SQL Server e como solucioná-los.

Setando auto create statistics OFF

Problema

Como as estatísticas aumentam o desempenho da execução de consulta do SQL Server?

Solução

Os histogramas estatísticos são usados pelo otimizador de consulta para escolher o plano de execução de consulta ideal. Se uma consulta contém uma coluna com as estatísticas, o otimizador tem como estimar com precisão o número de linhas afetadas por essa consulta. Assim, o otimizador tem informações suficientes para criar o plano de execução. O SQL Server cria estatísticas de diferentes maneiras:

· As estatísticas são criadas automaticamente para cada novo índice;

· Se a definição auto create statistics do banco de dados é setada como ON, então o SQL Server criará automaticamente as estatísticas para colunas não indexadas que são utilizadas em suas consultas.

Utilizando variáveis ​​de tabela

Problema

Para as variáveis de tabela do SQL Server, as estatísticas nunca serão mantidas. Tenha isso em mente. Quando se seleciona a partir de variáveis de tabela, a contagem de linhas estimada é sempre 1, a menos que um predicado seja avaliado como falso e não disponha de qualquer relação com a variável de tabela. "