Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes over 4hrs. SQL2005(+latest patches) is running in production and development. SQL2008R2 is running in testing.

I took a look at the Query Plan, and it shows that SQL2008R2 is using TempDB, by way of a Table Spool (lazy spool) to store the returned rows from the linked-server. The next step is showing Nested Loops (left anti semi join) as eating up 96.3% of the query. The line between the two operators is at 5,398MB!

The query plan for the SQL 2005 shows no use of tempdb and no use of a Left Anti Semi Join.

Below is the sanitized code and the execution plans the 2005 plan in on top, the 2008R2 on bottom.

What is causing the drastic slow down and change? I was expecting to see a different execution plan, so that doesn't bother me. The dramatic slow down in query time is what troubles me.

Do I have to look at the underlying hardware, since the 2008R2 version is using tempdb I have to take a look at how to optimize usage of that?

Is there a better way to write the query?

Thanks for the help.

```
INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT
Table1.iGroupID,
GETDATE()
FROM Table1
WHERE
NOT EXISTS (
SELECT 1
FROM LinkedServer.Database.Table2 Alias2
WHERE
(
Alias2.FirstName + Alias2.LastName = dbo.fnRemoveNonLetter(Table1.FullName)
AND NOT dbo.fnRemoveNonLetter(Table1.FullName) IS NULL
AND NOT Alias2.FirstName IS NULL
AND NOT Alias2.LastName IS NULL
) OR (
Alias2.FamilyName = dbo.fnRemoveNonLetter(Table1.FamilyName)
AND Alias2.Child1Name = dbo.fnRemoveNonLetter(Table1.Child1Name)
AND NOT dbo.fnRemoveNonLetter(Table1.FamilyName) IS NULL
AND NOT dbo.fnRemoveNonLetter(Table1.Child1Name) IS NULL
AND NOT Alias2.Familyname IS NULL
AND NOT Alias2.Child1Name IS NULL
) OR (
Alias2.StepFamilyName =

Adding to the previous answers, the reason for the plan regression might be due to a known cardinality estimation bug when the plan includes an Anti Semi Join. See KB 2222998

Assuming the 2005 plan produced acceptable performance, you may find bringing the server up to a version that includes that fix (and enabling TF4199 to activate it) will return you to the 'good' plan.

That said, there are many other opportunities to improve that query, so this might be a good time to concentrate on doing that instead.

StackExchange Database Administrators Q#7515, answer score: 6