Different Plans for the same parameter values after Implicit Conversion

Martin Cairns 2013-07-05 23:55:07

During a test involving the effect of implicit conversions of parameters on query performance, I hit a strange case envolving the rows estimates between two queries reading the same data getting a 12x over estimate for the string vs integer parameter values.

/storage/attachments/573-Plan.pesession
GokhanVarol 2013-07-06 00:04:24
Put an option recompile to see if that changes anything
Martin Cairns 2013-07-06 00:16:46
An option recompile on the string parameter version gives the same plan as the integer parameter version.

Here is the test script for the queries just in case anybody is interested http://bit.ly/124g1Uy

GokhanVarol 2013-07-06 00:24:18
At plan creation unless variables are part of stored procedure parameter or part of sp_executesql and did not change after the call it does not read the actual values, for plan compilation it's a string in this sample, and then it will save the first plan in cache for the same parametered query. With option recompile it will read the contents of the variables. If recompilation won't be a concern option recompile can help there.
Let me ask, what is the need to filter via a character or this was a test?
SQLkiwi 2013-07-06 00:29:23
Just a test, yes. It came up during a discussion between Martin and I on Twitter.
SQLkiwi 2013-07-06 00:19:59
The difference in cardinality estimation is expected because:

Seek Keys[1]:
Start: [tempdb].[dbo].[NumbersA].NUMBER >= 
Scalar Operator(CONVERT_IMPLICIT(int,[@A],0)),
End: [tempdb].[dbo].[NumbersA].NUMBER <= 
Scalar Operator(CONVERT_IMPLICIT(int,[@B],0))

uses different cardinality estimation compared to:

Seek Keys[1]:
Start: [tempdb].[dbo].[NumbersA].NUMBER >= Scalar Operator((1000000)), 
End: [tempdb].[dbo].[NumbersA].NUMBER <= Scalar Operator((1100000))

What was unexpected was the choice of hash versus merge join. On my machine, both queries produce a serial merge join plan (with differing input estimates) but very similar runtime performance (the actual number of rows is the same, of course).

The explanation for that is that your machine has 12 processors available whereas mine only has 8. If I run the queries after pretending I have 12 processors with:

DBCC WHAT_IF(1, 12)

I get the same parallel plan for query 1 as you do. This is just a consequence of the way CPU costs are decreased for higher DOP. It turns out that DOP 12 is enough to make the parallel hash join plan appear cheaper, whereas at DOP 8 the serial merge join appears cheaper.

Using OPTION (RECOMPILE) on the first query results in the same plan as for the second query because the parameter values are sniffed and constant folded.

Plans for DOP 8 run

/storage/attachments/574-mc.pesession
Martin Cairns 2013-07-06 00:55:01
I've removed some CPUs and I now get the same plan shape for both queries in Plan Explorer but it's still the Hash Plan in SSMS.
SQLkiwi 2013-07-06 01:04:58
Costing has some curious behaviours with the various parallelism options set differently. You should see the serial plan with OPTION MAXDOP (4) though.
Martin Cairns 2013-07-06 01:13:35
Ah I had altered the query getting rid of that change combined with DBCC OPTIMIZER_WHATIF(CPUs, 9) gives a serial merge and 10 gives parallel hash.
July 5, 2013
No Comments