Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Execution plan uses CONVERT_IMPLICLIT , But i have mention CAST in where clause Expand / Collapse
Posted Sunday, November 17, 2013 9:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 22, 2016 5:42 AM
Points: 298, Visits: 797
Hi Experts,

This is kind of wired behaviour on part of SQL Server.

1. I have a query whose execution plan initially showed the used of expensive CONVERT_IMPLICLIT operator
2.Then i change the where clause to accomodate the CAST operator so that CONVERT_IMPLICLIT would not be used
3.I run the query but still it was using CONVERT_IMPLICLIT operator.
4.I then clear the cache using DBCC command (in case it was using previous generated query plan)
5.I run again the same query but to my surprise ; execution plan still uses CONVERT_IMPLICLIT operator

Has anybody faced this situation. Could anybody would be help me with this

Post #1515062
Posted Sunday, November 17, 2013 10:11 PM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Post the plan.

Not seeing why you're concerned though. Convert implicit isn't expensive, it just prevents index seeks, and cast does exactly the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1515064
Posted Sunday, November 24, 2013 1:52 PM



Group: General Forum Members
Last Login: Thursday, December 1, 2016 6:49 AM
Points: 1,634, Visits: 1,621
I'd say that it depends on where the convert_implicit is. If it is converting a column used in the join or where clause, it could have a much bigger impact than explicitly converting the constant value on the other side of the criteria.

But as for adding the CAST and it still doing a convert_implicit, perhaps you put the cast in the wrong place. Or perhaps you didn't cast it to the correct data type definition and it has to do further conversion.

My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Post #1517121
Posted Monday, November 25, 2013 4:54 AM


Group: General Forum Members
Last Login: Thursday, November 17, 2016 3:07 AM
Points: 14, Visits: 162
Not sure if this is what you are referring to...

SQL Server apparently treats CAST as CONVERT:
Post #1517198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse