Statisticas e Plano de Execução

Inicial > SQL Server > Statisticas e Plano de Execução

Statisticas e Plano de Execução

4 de dezembro de 2007 Fabiano Amorim Deixe um comentário Go to comments

Semana passada me deparei com um problema de performance de uma procedure chamada pela minha aplicação Win32 exibindo uma mensagem de TimeOut para o usuário. Somente após as estatísticas das tabelas serem atualizadas é que o processo conseguiu rodar.

Vamos analisar um pouco o problema.

Primeira pergunta: O que são e para que servem as estatísticas das tabelas?

O SQL Server guarda informações em relação aos registros das tabelas para que o Otimizador possa decidir qual o melhor plano de execução para uma determinada consulta baseado nos dados que ele lê nas statistics das tabelas.

Vamos a um exemplo:

use

MASTER

create

database INSIDE

–Altera a propriedade Auto_Update_Statistics para OFF

alter

database INSIDE set AUTO_UPDATE_STATISTICS off

use

INSIDE

–Vamos criar uma tabela para teste

drop

table teste

create

table teste (id Int Identity(1,1), Nome VarChar(200))

set

nocount on

declare

@i Int

set

@i = 0

while

@i < 1000

begin

insert into teste (nome) values(‘A’)

insert into teste (nome) values(‘B’)

insert into teste (nome) values(‘C’)

insert into teste (nome) values(‘D’)

insert into teste (nome) values(‘E’)

set @i = @i + 1;

end

while

@i < 10000

begin

insert into teste (nome) values(‘A’)

set @i = @i + 1;

end

create

index ix_teste on teste(nome)

–Vamos ver as Statistics criadas pelo indice

DBCC

SHOW_Statistics(teste, ix_teste)

— Faz um Table Scan pois ele lê nas Statistics que é o melhor meio de acesso aos dados, a consulta irá retornar 1000 registros

— Repare que na propriedade Estimeted Number Rows do Execution Plan o SQL já sabe quantas linhas a consulta irá retornar.

select

* from teste

where

nome = ‘A’

–Vamos incluir uma massa de dados para o valor X

declare

@i Int

set

@i = 0

while

@i < 10000

begin

insert into teste (nome) values(‘X’)

set @i = @i + 1;

end

— Repare que o SQL criou um novo plano de execução para a consulta abaixo. Sendo que ele deveria gerar um table scan como no primeiro SQL.

— ele não gerou porque as statistics estão desatualizadas repare que atualizando as statistics e executando a consulta novamente o SQL escolhe o melhor plano

— Podemos concluir que o deixar as statistics desatualizadas pode levar o otimizador a um mal plano de execução.

— Verificar a propriedade Estimeted Number Rows do Execution Plan.

select

* from teste

where

nome = ‘X’

–Vamos atualizar as statistics

UPDATE

STATISTICS teste

–Faz um full Scan

select

* from teste

where

nome = ‘X’

Segunda pergunta: Como identificar o problema?

Identifiquei que o problema era um mal plano de execução gerado pelo SQL de uma consulta feita dentro da procedure que retornava os dados do processo.

Esta consulta fazia vários joins com tabelas com mais de 15 milhões de registros e o SQL Server estava fazendo um Index Scan em quase todas estas tabelas.

Rodei a procedure no SSMS com a opção para exibir o Plano de Execução (CTRL+M) ligada e tive o seguinte resultado.

Observando a imagem acima podemos identificar que as querys 5 e 6 são responsáveis pelo gargalo da procedure então me limitei em analisar estas consultas.

Após analisar as consultas constatei que o SQL Server não estava gerando um bom plano de execução para as consultas já que ele estava gerando vários Index Scan ao invés de Seeks.

Terceira pergunta: Como resolver o problema?

Após identificar que o problema era o plano de execução temos várias maneiras de forçar o SQL Server a gera determinado plano por exemplo com o uso de Hints, no meu caso achei melhor criar um cursor e dentro de cada linha do cursor eu executo a instrução SQL mas passando uma condição bem restritiva no WHERE baseado no valor do cursor fazendo o SQL Server gerar um plano de execução com vários Seeks o que foi bem melhor que apenas um Index Scan.

Fiz mais outras alterações como trocar os DataTypes das tabelas temporárias usadas na procedure DateTime para SmallDateTime, Int para SmallInt ou TyniInt, SET NOCOUNT ON dentre outros, mas o ideal é sempre melhorar o gargalo.

A melhora foi muito grande, vamos analisar os resultados no profiler.

Conclusão de 2:20 minutos para 0. J

Verificamos então como identificar o problema, fica a dica sobre Statistics e a analise dos planos de execução utilizando o CTRL-M, é claro que isso não funciona sempre mas ajuda.

__________________________________________________________________
Fabiano Neves Amorim (MCP – MCTS – SQL Server)
Análise – NewCon Enterprise
* fabiano@cnpm.com.br – http://fabianosqlserver.spaces.live.com/

( (55 – 14) 3404-3700