Share via

SQL 2000 to 2005 migration: execution plan change issue.

  • Article

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.

Comments

  • Anonymous
    April 28, 2007
    During the same project the next issue was found: temporary table reuse. Here is an example on “how to

  • Anonymous
    July 10, 2007
    Hi Igor, We are looking for a Project Plan for Migration to Sql Server 2005. Can you share a sample or a template if you have one? Greatly appreciate your help. Regards, Shastry

  • Anonymous
    July 10, 2007
    We are using the standard (template) plan from MCS (to long, detailed and complicated). This is an ownership of MCS, as a result i am not sure i will be able to share it. Please try to contact PSS. Sorry.

  • Anonymous
    July 16, 2007
    Hi Igor! Well we've run into the same problem in one of our procdures. Is there any way to "fix" this? We sincerely do not know where else it may appear and a solution, even if it is a simply work around would be nice...

  • Anonymous
    July 16, 2007
    Even if workaround exists it is unknown for me.

  • Anonymous
    July 16, 2007
    Oh, ok... Seems we'll have to do a lot of of digging in our databases. :)

  • Anonymous
    July 17, 2007
    The comment has been removed

  • Anonymous
    August 24, 2007
    The comment has been removed

  • Anonymous
    August 24, 2007
    The comment has been removed

  • Anonymous
    December 21, 2008
    hai i m doing a project in tht i need to convert sql 2000 to sql 2005 .can u send  me sample codes if u hve any?

  • Anonymous
    March 21, 2009
    Looks like this is not as easy as I had earlier anticipated. I don't know we are going to do with all our projects when we need to convert.

  • Anonymous
    July 09, 2014
    My project is in SQL 2000..I need it in SQL 2005..Is this make sense?? if possible,kindly let me know how?? share this info to bharathdgl92@gmail.com