Hi everyone,
I've been using Brent Ozar's tools for a while, especially the sp_Blitz procedure.
However, there is one recommendation about implicit conversion that I don't get, no matter what I try.
Please help me to understand how to eliminate the implicit conversion in the execution plan.
Thank you!
This is the query in question:
(@dealStatus tinyint,@dealType tinyint,@takeCity nvarchar(20),@firstRow int,@lastRow int)
SELECT id,title,details,userId,condition,regDate,updateDate,image,imageVer,city,recordCount FROM (
SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city,
COUNT(0) OVER () AS recordCount,CAST(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC) AS int) AS rowNum
FROM deals (NOLOCK)
INNER JOIN subcategories (NOLOCK) ON subcategory=subcategories.id
INNER JOIN cities (NOLOCK) ON city=cities.name
WHERE dealStatus=@dealStatus AND dealType=@dealType AND userId>0 AND livingArea=@takeCity AND (middleCategory NOT IN (801,805) OR subcategory=80012)
) a WHERE rowNum BETWEEN @firstRow AND @lastRow
I put the execution plan XML in a reply to this post. Sorry I had to trim most of it because I couldn't find a way to attach a file.
<RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2723.97" EstimateIO="0" EstimateCPU="0.0113862" AvgRowSize="1108" EstimatedTotalSubtreeCost="0.400222" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="id" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="title" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="details" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="subcategory" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="condition" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="regDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="updateDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="image" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="imageVer" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="sortOrder" />
</OutputList>
<NestedLoops Optimized="0" WithUnorderedPrefetch="1">
<OuterReferences>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Column="name" />
<ColumnReference Column="Expr1013" />
</OuterReferences>
<RelOp NodeId="11" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="133" EstimateIO="0.003125" EstimateCPU="0.0003033" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0034283" TableCardinality="1238" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Column="name" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Column="name" />
</DefinedValue>
</DefinedValues>
<Object Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Index="[livingArea]" TableReferenceId="-1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Column="livingArea" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[@takeCity],0)">
<Identifier>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator>
<Convert DataType="nvarchar" Length="40" Style="0" Implicit="1">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@takeCity" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp NodeId="12" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="20.481" EstimateIO="0.003125" EstimateCPU="0.000179571" AvgRowSize="1108" EstimatedTotalSubtreeCost="0.384098" TableCardinality="75043" Parallel="0" EstimateRebinds="132" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="id" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="title" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="details" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="subcategory" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="condition" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="regDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="updateDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="image" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="imageVer" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="sortOrder" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="details" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="subcategory" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="condition" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="regDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="updateDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="image" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="imageVer" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="sortOrder" />
</DefinedValue>
</DefinedValues>
<Object Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Index="[dealStatus,dealType,city,id,subcategory]" TableReferenceId="-1" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="dealStatus" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="dealType" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@dealStatus]">
<Identifier>
<ColumnReference Column="@dealStatus" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[@dealType]">
<Identifier>
<ColumnReference Column="@dealType" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tsurtau_agora].[dbo].[cities].[name]">
<Identifier>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[cities]" Column="name" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[tsurtau_agora].[dbo].[deals].[userId]>(0)">
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Hash>
</RelOp>
</Segment>
</RelOp>
</Spool>
</RelOp>
<RelOp NodeId="14" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.000567457" AvgRowSize="1104" EstimatedTotalSubtreeCost="0.00113491" Parallel="0" EstimateRebinds="2026.63" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="id" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="title" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="details" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="condition" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="regDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="updateDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="image" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="imageVer" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="sortOrder" />
<ColumnReference Column="Expr1009" />
</OutputList>
<NestedLoops Optimized="0">
<Predicate>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</Predicate>
<RelOp NodeId="15" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="5.67457e-005" AvgRowSize="1104" EstimatedTotalSubtreeCost="0.000624203" Parallel="0" EstimateRebinds="2026.63" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1009" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1015],0)">
<Convert DataType="int" Style="0" Implicit="1">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="16" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.000567457" AvgRowSize="1104" EstimatedTotalSubtreeCost="0.000567457" Parallel="0" EstimateRebinds="2026.63" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Expr1015" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="0" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp NodeId="17" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="1104" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="2026.63" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="id" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="title" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="details" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="userId" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="condition" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="regDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="updateDate" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="image" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="imageVer" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="city" />
<ColumnReference Database="[tsurtau_agora]" Schema="[dbo]" Table="[deals]" Column="sortOrder" />
</OutputList>
<Spool PrimaryNodeId="5" />
</RelOp>
</StreamAggregate>
</RelOp>
</ComputeScalar>
</RelOp>
I see 2 implicit converts...
The first one is CONVERT_IMPLICIT(nvarchar(20),[@takeCity],0) - for this one, what is the data type for the variable and what is the data type for the column?
The second one is CONVERT_IMPLICIT(int,[Expr1015],0) - it looks like this one is the COUNT(*) OVER() which returns by default a bigint. This will be reported as a possible cardinality issue but generally should not be a problem.
Hi Jeff,
Thanks for your answer.
Both the variable @takeCity and the column livingArea defined as nvarchar(20). That's why this was so annoying and not clear, but I just managed to solve it right now. The solution was to add COLLATE Hebrew_100_BIN after the variable in the WHERE clause, because this is the collation of the column.
- livingArea=@takeCity COLLATE Hebrew_100_BIN
.
I still haven't found a solution for the COUNT function though. I tried to use CAST in different ways, but no matter where I put it I always get an implicit conversion in the execution plan. This is what I tried:
-
SELECT id,title,details,userId,condition,regDate,updateDate,image,imageVer,city,recordCount FROM (
SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city,
CAST(COUNT(0) OVER () AS int) AS recordCount,CAST(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC) AS int) AS rowNum
FROM deals (NOLOCK)
-
SELECT id,title,details,userId,condition,regDate,updateDate,image,imageVer,city,CAST(recordCount AS int) AS recordCount FROM (
SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city,
CAST(COUNT(0) OVER () AS int) AS recordCount,CAST(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC) AS int) AS rowNum
FROM deals (NOLOCK)
-
SELECT id,title,details,userId,condition,regDate,updateDate,image,imageVer,city,CAST(recordCount AS int) AS recordCount FROM (
SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city,
COUNT(0) OVER () AS recordCount,CAST(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC) AS int) AS rowNum
FROM deals (NOLOCK)
Not sure you can get rid of that implicit conversion - or that it is actually causing a problem with the query (even though it is reported as such in the execution plan).
You could try this:
SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city,
CHECKSUM(COUNT(0) OVER ()) AS recordCount,CHECKSUM(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC)) AS rowNum
Unfortunately the CHECKSUM function didn't help.
I wish Brent Ozar would say something about these cases, or at least remove them from detection if either they don't have a real impact on performance or it's technically impossible to prevent them 
It isn't their tool - it is SQL Server's execution plan identifying the implicit convert as a 'possible' issue in the cardinality estimator. And unfortunately, because the return values of the functions being called are different we get into some issues.
ROW_NUMBER returns a bigint - the CHECKSUM around that should eliminate the implicit conversion and return an int. I was hoping it would do the same with COUNT() OVER()...
Have you tried moving the COUNT outside the derived table? I don't see anything in the actual query that would require the implicit conversion - is it used somewhere else?
If I took out the COUNT from the derived table and put it in the SELECT above, it wouldn't give me the right number since this SELECT is limited by rowNum in its WHERE clause.
Well, at least I'm happy I learnt something new about the COLLATE clause, and that comparison of two string columns with different collations is another cause of implicit conversions in execution plans.