Non-foldable expressions e o QO

Post rápido.
Estou executando um pequeno benchmark para um cliente, avaliando se uma mudança na modelagem vai trazer benefícios para a aplicação, quando me deparo com um problema interessante. Conversando com o Fabiano ele citou non-foldable expressions, a causa raiz da questão.

Resumindo, quando o QO encontra non-foldable expressions ele não utiliza a densidade ou o histograma para estimar o número de registros, então ele chuta que um percentual dos registros será retornado (10% no meu caso).

Qual o problema disso? Mesmo com um índice em uma coluna com boa seletividade, acabo por ver cluster index scans e planos bem ruins.

Me parece que seria mais interessante o QO usar a densidade no caso de igualdade com non-foldable expression, mas com certeza devem haver cenários onde isso acaba por trazer resultados ruins.

Quer testar? Abaixo está o código que eu gerei para simular o problema utilizando o AdventureWorks2012.
Aproveite e teste o código no SQL Server 2014 e veja a diferença…

E não deixe de testar a última consulta do script e analisar a estimativa do QO. #fun


/****************************************************************************************
*****************************************************************************************

Autor: Luciano Caixeta Moreira
E-mail: luciano.moreira@srnimbus.com.br
LinkedIn: http://www.linkedin.com/in/luticm
Blog: http://luticm.blogspot.com
Twitter: @luticm

Título: non-foldable expression and bad plans
Descrição: 

Histórico de atualização (yyyy-mm-dd):
- 2015-07-30: Criação do script


*   Copyright (C) 2015 Sr. Nimbus Prestação de Serviços em Tecnologia LTDA
*   http://www.srnimbus.com.br

*****************************************************************************************
****************************************************************************************/

USE tempdb
GO

SELECT *
INTO tempdb.dbo.TestePredicado
FROM AdventureWorks2012.Sales.SalesOrderDetail

CREATE UNIQUE CLUSTERED INDEX idx01
ON dbo.TestePredicado (SalesOrderDetailID)
go

CREATE NONCLUSTERED INDEX idx02
ON dbo.TestePredicado (SalesOrderID)
GO

EXEC sp_helpindex 'TestePredicado'

DBCC SHOW_STATISTICS('dbo.TestePredicado', idx02)
GO

-- Usa o histograma, conforme esperado, e estima 1 registro.
SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = 46040
GO

-- Vetor de densidade
-- 3.178134435086604e-5 * 121317 = 3.855617352614016
DECLARE @X INT
SET @X = 46040

SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = @X
GO

-- Utiliza a densidade
SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = CAST(RAND() * 10000 AS INT)
GO

-- Estimated number of rows: 12131.7 => 10%
-- Plano ruim, scan (10% estimate) + filter
SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = CAST(CAST(NEWID() AS binary(6)) % 20000000 AS INT)
GO

-- Ok, densidade
SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = DATEPART(YEAR,GETDATE()) 
GO

-- Para resolver o problema...
DECLARE @X INT
SET @X = CAST(CAST(NEWID() AS binary(6)) % 20000000 AS INT)

SELECT *
FROM dbo.TestePredicado
WHERE salesorderid = @X
GO

/*
In a larger and more complex query on a larger data set, this type of error can result 
in bad plan selection. If this is a problem for your application, consider using a 
technique like the one illustrated above. Use sp_executesql or a stored procedure 
containing the problem query, and pass in the precomputed result of the non-foldable 
expression as a parameter. 
This will allow you to work around the problem and get good cardinality estimates.

https://technet.microsoft.com/en-us/library/cc966419.aspx
*/

-- Estimativa OK
WITH C AS (
SELECT TOP 100
CAST(RAND() * 10000 AS INT) AS IDVenda
FROM sys.columns
)
SELECT *
FROM dbo.TestePredicado AS T
INNER JOIN C
ON T.SalesOrderID = C.IDVenda

-- Estimativa ficou ótima (!!!??!!!?!!), considerou um cross join...
WITH C AS (
SELECT TOP 100
CAST(CAST(NEWID() AS binary(6)) % 20000 AS INT) AS IDVenda
FROM sys.columns
)
SELECT *
FROM dbo.TestePredicado AS T
INNER JOIN C

ON T.SalesOrderID = C.IDVenda


Abraços

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br