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 estatística deve estar desatualizada. Entretanto, essa afirmação nem sempre é verdadeira, conforme demonstraremos 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

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'

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%'

Vamos agora executar de fato as consultas, usando o atalho F5, e incluir o plano atual delas. 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

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.
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’).

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'

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.

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.Conclusão

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