CODEWITHSUNDEEP
sql-serversql-server-2012
Legion
Legion

Reputation: 3427

How do I find the implicit conversions present in a SQL Server query?

Normally I just toggle Include Actual Execution Plan and in the plan there will be warning symbols I can review which will tell me what the problem is. However, for some reason, with this one query the implicit conversions aren't highlighted in the plan (they do come up just fine for other queries). But I can find them if I select Show Execution Plan XML. In the XML I can find lines with CONVERT_IMPLICIT. I was able to find and remove a large number of implicit conversions this way but there are some I just can't seem to track down. This is because the query is large and the XML plan refers to intermediary expressions with generic names like Expr1026. This makes it nearly impossible to find in the query.

Here's some example lines from the XML plan:

<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1426],0)">
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(max),[Expr1025],0)">
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(max),[Expr1062],0)">

Unfortuantely the column references wrapping these statements are equally unhelpful:

<ColumnReference Column="Expr1069" />

Is there some other way to surface these implicit conversions with actual line references?

Upvotes: 0

Views: 750

Answers (2)

lptr
lptr

Reputation: 6788

the following could give you an impression of the scalar expressions:

declare @x xml = '<?xml version="1.0" ?>
....xml plan goes here...replace a single quote with two ones first...
'

declare @e table
(
id int identity,
expr nvarchar(4000),
def nvarchar(max),
defxpd nvarchar(max),
unique(expr, id)
);

WITH xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
insert into @e(expr, def)
select 
    t.c.value('./ColumnReference[1]/@Column', 'nvarchar(max)') as expr, 
    t.c.query('for $cr in ./ColumnReference[position()>=2] return concat("{",$cr/@Schema,".",$cr/@Table,".",$cr/@Column,"}")').value('.', 'nvarchar(max)')
    +
    t.c.query('data(.//*/@ScalarString)').value('.', 'nvarchar(max)') as def
from @x.nodes('//DefinedValue[ColumnReference[contains(./@Column, "Expr")]]') as t(c);

update @e
set defxpd = def;

while 1=1
begin
    update e1
    set e1.defxpd = replace(e1.defxpd, e2.expr, e2.def)
    from @e as e1
    join @e as e2 on e1.id <> e2.id and e1.defxpd like '%'+e2.expr+'%'
    where len(e2.expr) >= 4;

    if @@rowcount = 0
    begin
        break;
    end
end

select *
from @e;

Upvotes: 0

Preben Huybrechts
Preben Huybrechts

Reputation: 6111

You can search for the name of the expression in the execution plan and bubble your way up. Example

  • expr1069 Will lead to expr1000
  • expr1000 will lead to ...
  • ... will lead to something that’s actually in your query

Based on the examples you are showing, I’m guessing you have some isnull Or coalesce functions in your query or aggregates.

Implicit conversions aren’t always a performance bottle neck see this question

Upvotes: 1

Related Questions