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 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↳SQL64 conteúdosSQL Server – Como evitar SQL Injection?Data · mai 2019Azure SQL DB Managed InstanceData · abr 2019SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query)Data · abr 2019Ver tudo em Data → 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.