Coisas do Otimizador: quando diferenças entre Estimated Number of Rows x Actual Number Rows acontecem mesmo com estatísticas atualizadas

Quando você analisa um plano de execução de alguma query e percebe que existe divergência entre o número estimado de linhas e o número atual de linhas, qual a sua conclusão inicial? Normalmente a conclusão é: a estatística deve estar desatualizada. Entretanto, essa afirmação nem sempre é verdadeira, conforme estaremos demonstrando neste artigo.

No decorrer do exemplo a seguir, pontuarei algumas questões importantes sobre Otimizador, Estatísticas e o Plan Cache. Divirtam-se!

use tempdb

go

— Cria uma tabela que guardará dados de pedidos de venda

create table dbo.stsPedidos

( IdPedido int not null primary key,

DtPedido Datetime not null,

DtEnvio Datetime not null,

Status tinyint not null

);

— Insere 5 mil linhas

insert dbo.stsPedidos

select top 5000 h.SalesOrderID, h.OrderDate, h.ShipDate, h.Status

from AdventureWorks2012.Sales.SalesOrderHeader h;

— Cria estatística para a coluna DtPedido

create statistics ST_Pedidos_Data on dbo.stsPedidos (DtPedido);

— Exibe o histograma

dbcc show_statistics(‘stsPedidos’, ST_Pedidos_data) with histogram;

go

image

 

Atente para as linhas 3 e 9:

· 2005-07-09 tem 6 valores idênticos;

· 2005-08-01 tem 80 valores idênticos;

Vejamos o que acontece quando exibimos o plano de execução estimado para as duas consultas abaixo. Lembre-se: para exibir o plano estimado basta selecionar a query e usar o atalho CTRL+L.

SELECT * FROM dbo.stsPedidos s WHERE s.DtPedido = ‘2005-07-09T00:00:00.000’;

SELECT * FROM dbo.stsPedidos s WHERE s.DtPedido = ‘2005-08-01T00:00:00.000’;

clip_image002[4]

clip_image004

 

Como essas queries nunca foram efetivamente executadas (acabamos de criar as tabelas), então não existe plano de execução armazenado no plan cache do SQL Server que seja compatível com as consultas. Nessa situação o SQL produz e exibe um plano estimado individual para cada query. Esses planos estimados não são guardados em cache.

Vamos consultar o Plan Cache para comprovar que os planos estimados, gerados conforme o exemplo anterior, não foram guardados em memória.

select * from

(

select t.text, p.plan_handle

from sys.dm_exec_cached_plans p

cross apply sys.dm_exec_sql_text(p.plan_handle) t

) a

where a.text like ‘SELECT * FROM dbo.stsPedidos%’;

image

 

Vamos agora executar de fato as consultas, usando o atalho F5, e incluir o plano atual das mesmas. Não vou anexar as figuras contendo o resultado das consultas, mas elas apresentaram o seguintes totais de linhas:

· ‘2005-07-09T00:00:00.000’: retornou 6 linhas

· ‘2005-08-01T00:00:00.000’: retornou 80 linhas

clip_image002[6]

clip_image004[4]

 

Note que ao colocar o mouse sobre o operador SELECT, de ambas as queries, o número estimado de linhas é 6. Opa! Mas se a segunda query (‘2005-08-01T00:00:00.000’) trouxe a quantidade certa de linhas (80) então por que, mesmo trazendo corretamente o result set, o número estimado é divergente?

Vejamos detalhes do operador Clustered Index Scan dessa segunda query.

image

 

Atente para a divergência entre Actual Number of Rows e Estimated Number of Rows. Essa divergência não é causada por estatística desatualizada, pois nós criamos a estatística para a coluna e desde então não fizemos nenhuma alteração de dados. Como não existe índice para essa coluna, a única opção que sobra ao otimizador é sempre fazer uma scan na tabela (neste caso um scan no índice clustered da tabela). Para o Query Optimizer (QO), nessa situação não fará diferença (em termos de escolha e geração de Execution Plan) se o número estimado de linhas é 6, 60, 500, etc, pois a operação a ser desempenhada sempre será um scan na coluna da tabela.

Há uma informação interessantíssima que vale à pena comentar. Para tanto, vou exibir as propriedades do operador SELECT referente ao plano de execução da segunda query (‘2005-08-01T00:00:00.000’).

image

image

 

Veja que o otimizador automaticamente substituiu o literal ‘2005-08-01T00:00:00.000’ da query pelo parâmetro @1 (opção conhecida como PARAMETERIZATION), conforme primeira figura. Na segunda figura podemos verificar que o plano de execução foi compilado usando como valor ‘2005-07-09T00:00:00.000’, conforme a propriedade Parameter Compiled Value.

Vejamos o que acontece quando utilizamos um índice para a coluna DtPedido.

— Elimina a estatística criada anteriormente

drop statistics dbo.stsPedidos.ST_Pedidos_Data;

— cria o índice

create nonclustered index AK_Pedidos_DtPedido on dbo.stsPedidos (DtPedido);

Atenção: não mostrarei o histograma da estatística implicitamente criada para o índice, porque ele é exatamente igual ao histograma exibido no início deste artigo.

— Executa e inclui o plano atual das duas queries abaixo

SELECT * FROM dbo.stsPedidos s

WHERE s.DtPedido = ‘2005-07-09 00:00:00.000’;

SELECT * FROM dbo.stsPedidos s

WHERE s.DtPedido = ‘2005-08-01 00:00:00.000’;

 

clip_image002[8]

clip_image004[6]

 

Note que os planos de cada query são diferentes. Para a primeira query temos um Index Seek, enquanto que na segunda continuamos com um Clustered Index Scan.

Vamos executar mais duas queries e comparar o Actual Execution Plan de ambas.

clip_image002[10]

clip_image004[8]

Os planos são os mesmos e, o melhor de tudo, não há divergência entre o número atual de linhas e o número estimado de linhas.

clip_image002[12]

clip_image004[10]

 

Conclusão

Conforme o que foi discorrido, é possível existir divergência entre Actual Number of Rows e Estimated Number of Rows, as quais não são causadas por estatísticas desatualizadas. Entender quando e como isso acontece é importante, sobretudo, durante trabalhos de tuning.



Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s