Can a residual predicate be turned into a seek at run time

GokhanVarol 2013-07-08 02:24:09

The attached file shows a seek on the UnvPclID table:

Seek Keys[1]: Prefix: [UnvPclID].CntyCD = [#CntyCd].[CntyCd]

With a residual predicate:

[@FakeVarRealValueIs_0]=(1) OR
[Edition] = [tempdb].[dbo].[#CntyCd].[Edition]

The table has a clustered index on (CntyCd, Edition, …).

The query uses OPTION (RECOMPILE) so I would expect the value of the variable to be sniffed, the OR removed, and the Edition predicate to be used in a seek, not as a residual predicate. Why is this optimization not performed in this case?

alt text

Analysis files

SQLkiwi 2013-07-08 03:10:13
Ordinarily, using RECOMPILE would result in a complete seek. The value of the variable would be sniffed, resulting in predicates:

up.CntyCd = ds.CntyCd AND
(0 = 1 OR up.Edition = ds.Edition)

This simplifies down to:

up.CntyCd = ds.CntyCd AND
up.Edition = ds.Edition

That is now fully seekable using an index on (CntyCD, Edition…)

The reason it doesn't work in this query is a side-effect of assigning results to variables that means the value of the variable is not sniffed even though RECOMPILE is used. Without the variable sniffing, the expression cannot be simplified, so the seek remains on CntyCD alone, with the expensive residual including the variable test.

The seek will work as you expect with RECOMPILE if you do not assign the results to variables.

The red text above is a link to a Connect item where Microsoft confirm this behaviour with RECOMPILE.

Note that a pre-execution ('estimated') plan will not show any optimizations only possible at runtime due to the use of RECOMPILE.

GokhanVarol 2013-07-08 03:18:38
Please explain "The seek will work as you expect if you do not assign results to variables." Assigning to variables at select has to do with this? I commented little above that I realized without optimize for the table io was a lot lower compared to with optimize for (it seems like it could not seek with CntyCd, Edition instead it seeked up to CntyCd)
SQLkiwi 2013-07-08 03:46:29
Updated the answer (and question!) to help you understand this.
Jonathan Kehayias 2013-07-08 02:58:42
Not sure of the context of the question from what is posted here, but any sargable predicate can result in a seek at optimization. The number of Seeks in the plan makes it hard to determine what specifically you might be looking at here though. If you have a Scan occurring I'd be looking at what caused the predicate to be non-sargable, thereby inducing the scan personally.
GokhanVarol 2013-07-08 03:02:01
The estimations were low and the hash is spilling, by hiding the secondary key column under a variable using optimize for I was able to get the estimations higher and memory grant higher, I was wondering if at run time that predicate can be a seek since the real value variable used in the or is 0 and it will be false.
Jonathan Kehayias 2013-07-08 03:04:57
From the information given I have no context to review what you are talking about so I can only guess. Anymore specifics as to what in the plan you are specifically looking at? Also is the plan you provided the actual plan for your optimize for or the actual plan without, and for either scenario what is the other plan?
GokhanVarol 2013-07-08 03:11:14
The query is formed as below (it's longer than that but I pasted the sections in relation). The execution plan is actual and "(@FakeVarRealValueIs_0 = 1 OR up.Edition = ds.Edition)" shows in predicate section whereas @FakeVarRealValueIs_0 has a real value of 0 but optimized for 1. I was wondering if optimizer can convert that predicate to seek at run time ?

SELECT
………….
FROM #CntyCd ds
OUTER APPLY ( SELECT k.*
, up.[UnvPclId]
FROM ( SELECT *
FROM [tCommon].[UnvPclId] up
WHERE up.CntyCd = ds.CntyCd
AND (@FakeVarRealValueIs_0 = 1 OR up.Edition = ds.Edition)
) up
RIGHT OUTER HASH JOIN
………….
OPTION ( RECOMPILE, QUERYTRACEON 8649, OPTIMIZE FOR (@FakeVarRealValueIs_0 = 1) )

Jonathan Kehayias 2013-07-08 03:18:13
That looks like a catch-all query to me, so it has to do the scan to evaluate the (@FakeVarRealValueIs_0 = 1 OR up.Edition = ds.Edition) for the value of 1.