Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: Monday, March 23, 2015 8:49 PM
Points: 289, Visits: 724
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: Today @ 10:54 AM
Points: 41,280, Visits: 37,875
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 2008, MVP
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: Sunday, January 4, 2015 8:33 PM
Points: 1,618, Visits: 1,556
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1517121
Posted Monday, November 25, 2013 4:54 AM


Group: General Forum Members
Last Login: Tuesday, March 3, 2015 10:32 AM
Points: 14, Visits: 60
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