SQL 2000 to 2005 migration: execution plan change issue.

SQL 2000 to SQL 2005 migration: temporary tables reuse issue.

SQL, Analysis Services & related stories. — Sat, 28 Apr 2007 19:57:51 GMT

During the same project the next issue was found: temporary table reuse. Here is an example on “how to

re: SQL 2000 to 2005 migration: execution plan change issue.

Shastry — Tue, 10 Jul 2007 22:26:37 GMT

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

re: SQL 2000 to 2005 migration: execution plan change issue.

Igor Kovalenko — Tue, 10 Jul 2007 22:39:08 GMT

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.

re: SQL 2000 to 2005 migration: execution plan change issue.

Nuno Coimbra — Mon, 16 Jul 2007 19:42:31 GMT

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

re: SQL 2000 to 2005 migration: execution plan change issue.

Igor Kovalenko — Mon, 16 Jul 2007 22:20:03 GMT

Even if workaround exists it is unknown for me.

re: SQL 2000 to 2005 migration: execution plan change issue.

Nuno Coimbra — Tue, 17 Jul 2007 12:07:38 GMT

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

re: SQL 2000 to 2005 migration: execution plan change issue.

Nuno Coimbra — Tue, 17 Jul 2007 20:14:22 GMT

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

re: SQL 2000 to 2005 migration: execution plan change issue.

John — Fri, 24 Aug 2007 20:51:10 GMT

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.

re: SQL 2000 to 2005 migration: execution plan change issue.

John — Fri, 24 Aug 2007 20:52:04 GMT

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

re: SQL 2000 to 2005 migration: execution plan change issue.

namu — Mon, 22 Dec 2008 13:24:35 GMT

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?

re: SQL 2000 to 2005 migration: execution plan change issue.

Ray — Sun, 22 Mar 2009 13:24:44 GMT

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.