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].))))


       |--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]. as [t2].,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 (12)
  1. During the same project the next issue was found: temporary table reuse. Here is an example on “how to

  2. Shastry says:

    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

  3. Igor Kovalenko says:

    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.

  4. Nuno Coimbra says:

    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…

  5. Igor Kovalenko says:

    Even if workaround exists it is unknown for me.

  6. Nuno Coimbra says:

    Oh, ok… Seems we’ll have to do a lot of of digging in our databases. 🙂

  7. Nuno Coimbra says:

    Hi, it’s me again.

    Well…In our case, we DID find a workaround that seems to be working just fine… We used a query int in our faulty INNER JOINS. Now, we tell the optimizer to do a INNER LOOP JOIN and all the faulty queries seem to be working.

    Strangely enough, the odd thing is that they do work with any of the 3 (LOOP, HASH or MERGE) JOIN hints…

  8. John says:

    I am also experiencing this problem.  As a quick fix I found it useful to use the OPTION(USE PLAN N'[YOUR PLAN HERE]’) keyphrase to force the query to use an execution plan of your choosing.  Luckily for me we had a Test server with an optimized execution plan on it which I then imported into our faulty production environment.  It still doesn’t execute nearly as fast as it did on SQL 2000 but it’s much improved over the automatic plan SQL 2005 generated.

  9. John says:

    Here’s a good TechNet article on forcing the use of an execution plan: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

  10. namu says:

    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?

  11. Ray says:

    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.

  12. Bharath says:

    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

Comments are closed.

Skip to main content