SQL 2000 to 2005 migration: execution plan change issue.
Last week I was engaged on SQL 2000 to SQL 2005 migration project, and we had identified some issues related to significant changes in execution plans. Before migration we tested the application with using Upgrade Advisor (test passed without any caution), but hopefully we also had made an additional “trace replace” test. During this stage it was found that some stored procedures works fine in SQL 2000 but always failed in SQL 2005. Here is a script on “how to reproduce” :
declare @xmlDoc int
exec sp_xml_preparedocument @xmlDoc output, '<?xml version=''1.0'' encoding=''windows-1251'' ?><filter><first-name></first-name><mid-name></mid-name><last-name>John</last-name><table-number></table-number><login></login><active>1</active></filter>'
select * into #temp_xml from openxml(@xmlDoc, '/filter')
exec sp_xml_removedocument @xmlDoc
select a.attr_id, b.attr_val_id
from
(
select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id
from #temp_xml t1
join #temp_xml t2 on t1.id = t2.parentid
where t1.localname = 'AttributeID'
and t2.localname = '#text'
) a
left outer join
(
select t1.parentid parentid, cast(cast(t2.text as varchar(10)) as int) attr_val_id
from #temp_xml t1
join #temp_xml t2 on t1.id = t2.parentid
where t1.localname = 'AttrValueID'
and t2.localname = '#text'
) b on a.id = b.parentid
where a.attr_id is not null
order by a.attr_id
drop table #temp_xml
As mentioned above, SQL 2000 gives us an empty resultset, but SQL 2005 failed with
Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'John' to data type int.
We compared execution plans, and it was found, that the following part has different execution rule for different SQL version.
select t1.id id, cast(cast(t2.text as varchar(10)) as int) attr_id
from #temp_xml t1
join #temp_xml t2 on t1.id = t2.parentid
where t1.localname = 'AttributeID'
and t2.localname = '#text'
SQL 2000 gives us the following:
|--Compute Scalar(DEFINE:([Expr1002]=Convert(Convert([t2].[text]))))
|--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([t2].[parentid]=[t1].[id]))
|--Filter(WHERE:([t1].[localname]='AttributeID'))
| |--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]))
|--Filter(WHERE:([t2].[localname]='#text'))
|--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]))
SQL 2005’s version:
|--Hash Match(Inner Join, HASH:([t1].[id])=([t2].[parentid]), RESIDUAL:([tempdb].[dbo].[aaa].[parentid] as [t2].[parentid]=[tempdb].[dbo].[aaa].[id] as [t1].[id]))
|--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t1]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t1].[localname]=N'AttributeID'))
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT(int,CONVERT(varchar(10),[tempdb].[dbo].[aaa].[text] as [t2].[text],0),0)))
|--Table Scan(OBJECT:([tempdb].[dbo].[aaa] AS [t2]), WHERE:([tempdb].[dbo].[aaa].[localname] as [t2].[localname]=N'#text'))
As we can see SQL 2000 include two additional steps, but absolutely correct. SQL 2005 is much shorter, but absolutely incorrect and far from optimal (because of CONVERT applied to much more records than necessary). Tested with public 9.0.3159 version.
Summary: Upgrade advisor will never give you a 100% guaranty on safe migration. If possible please always collect some traces and replace it during testing phase of migration project.