SQL Server: Melhorando a performance através das estatísticas

Mostrar que o query optimizer, que é o responsável por criar os planos de execuções, precisa ler informações atualizadas, para optar sempre pelo melhor plano de execução, melhor algoritmo de resolução de query, e melhor índice.

por Thiago Pastorello Gervazoni

Ao procurarmos uma informação em uma lista telefônica por exemplo, primeiro identificamos alguma informação relevante como sobrenome, e depois tentamos encontrar dentre os do mesmo sobrenome o possível número que nos interessa. Isto na maioria das vezes realizamos automaticamente porquê queremos encontrar a informação em menor tempo e com assertividade.

Nos bancos de dados entretanto o que parece ser tão diferente do descrito acima ao buscar uma informação, na verdade, é bastante similar.

O SQL Server busca a informação de duas maneiras, table scan e através de índices. Porém mesmo se uma tabela não possua ou não índices, o SQL armazena estatísticas de cada campo, principalmente dos mais acessados, e para otimizar a busca da informação, para tal o query optimizer utiliza-se primeiro das estatísticas para montar seu plano de execução.

Existem alguns motivos de lentidão em queries como segue abaixo:

• Baixa velocidade de comunicação na rede
• Memória inadequada no servidor
• Falta de estatísticas
• Estatísticas desatualizadas
• Falta de índices
• Índices desatualizados

Nesta coluna cobriremos o assunto relacionado a estatísticas.

Antes do SQL optar por usar um índice ele lê as estatísticas dos campos a fim de encontrar o índice que será mais útil e rápido para chegar até a informação (exemplo da lista telefônica). É possível ter as tabelas com índices não desfragmentados e criados corretamente e ainda assim não ter total rendimento na busca da informação.

A explicação para isto é que o SQL está trabalhando perfeitamente com suas tabelas indexadas, até com índices compostos que são ótimos para a performance, porém com as estatísticas desatualizadas, fazendo com que o SQL opte pelo índice que não é a melhor escolha, por default quando instalamos o SQL existe a opção de criação e atualização automática de estatísticas defaul (Figura1), porém o SQL espera o acúmulo de algumas modificações para realizar a atualização automática, levando também em consideração o tamanho da tabela, até 8mb as estatísticas são atualizadas com mais freqüência, com mais de 8mb este intervalo aumenta consideravelmente. Podendo então ser atualizadas manualmente.

Figura 1 . Parâmetros default na instalação do SQL, auto create e update statistics

Identificando se as estatísticas estão desatualizadas

Ao inserir muitos registros as estatísticas acabam ficando desatualizadas, como teste eu executei uma stored procedure de consulta com o Show Execution Plan ligado, esta é uma ferramenta muito útil do query Analyzer, principalmente para a manutenção de índices e estatísticas. No nosso caso veremos as estatísticas.

Figura 2 . Execução de uma stored procedure com o Show Execution Plan ligado.

CREATE PROCEDURE nort_cust_s(@ContactName varchar(30))
AS
BEGIN
  SELECT customerID,CompanyName,ContactName FROM Customers WHERE ContactName=@ContactNAme
END

Podemos ver na figura 3, que teve um alto custo para a execução da query (Cost 100%), ou seja muito processamento para a busca da informação, uma porcentagem tolerável seria até no máximo 10%, isto é devido à falta de índices ou estatísticas desatualizadas.

Atente agora para o medidor (Estimated row count 999), isto significa que o SQL esperava desta tabela o retorno de 999 registros, na sentença SQL que passei para ele, informação esta que as estatísticas são responsáveis por armazenar, porém na realidade está retornando muito mais registros do que o esperado (Row count 2.499) 2.499 registros.

Figura 3 . Detalhes do plano de execução.

Precisamos neste caso atualizar as estatísticas, existem alguns motivos que levam a atualizar as estatísticas, como segue:

• Muitas alterações em key values de índices
• Adição de muitos registros
• Remoção de muitos registros
• Quando a tabela é truncada (truncate table)

Atualizando as estatísticas

É possível atualizar estas estatísticas com os comandos.

 UPDATE STATISTICS table | view 
    [ 
        index 
        | ( statistics_name [ ,...n ] ) 
    ]  WITH FULLSCAN

Atualiza as estatísticas em uma tabela isoladamente, a opção FULLSCAN lê todos os campos da tabela para atualizar as estatísticas da tabela.

SP_UPDATESTATS "resample"

Atualiza as estatísticas em todas as tabelas do banco de dados,

Figura 4 . Atualização das estatísticas.

Figura 5 . Executando a store procedure depois da atualização, com o execution plan ligado.

Figura 6 . Detalhes do plano de execução.

Agora com o plano de execução após a atualização vemos claramente os benefícios das estatísticas estarem atualizadas, o custo (CPU Cost) foi para 0%, e o query optimizer que é o responsável por criar os plano de execuções está lendo informações que estão atualizadas, optando assim sempre pelo melhor plano de execução, melhor algoritmo de resolução de query e melhor índice.

Até Mais.