Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 = dbo.fnRemoveNonLetter(Table1.StepFamilyName)
    AND Alias2.StepFamilyNameChild1 = dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2)
    AND NOT Alias2.StepFamilyName IS NULL
    AND NOT Alias2.StepFamilyNameChild1 IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyName) IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2) IS NULL
   )  
 ) AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )

SQL-2005


SQL2008R2

:: EDIT :: Executed the query from a different SQL2005 instance, pretty much the same execution plan as the "good one". Still not sure how the two 2005 versions are running better to the 2008R2 linked server, than the 2008R2 instances to the 2008R2 instances.

While I don't deny that the code could use some work, if it was the code being the problem, wouldn't I see the same'ish exec plans across all of my trials? Regardless of SQL version?

:: EDIT :: I have applied SP1 and CU3 to both of the 2008R2 instances, still no dice. I have specifically set the collocation in the linked server, no dice. I have specifically set permissions of my user acct to be sysadmin on both instances, no dice. I have also remembered my sql server 2008 internals and troubleshooting, we'll see if I can track this down some how.

Thanks everyone for the help and the tips.

:: EDIT :: I have done various permission changes to the linked server. I've used SQL logins, domain logins, I have impersonated users, I have used the "be made using this security context" option. I have created users on both sides of the linked server that have sysadmin rights on the server. I am out of ideas.

I would still like to know why SQL2005 is executing the query so dramatically different from SQL2008R2. If it was the query that was bad, I would be seeing the 4+hrs run time on both SQL2005 and SQL2008R2.

share|improve this question
3  
Same data? rebuilt indexes? Statistics up to date? –  gbn Nov 2 '11 at 15:49
3  
You were not kidding when you described it as "ugly"! Can you post the two XML plans to Gist. It's probably down to statistics (poor estimates) but will be easier to tell if we can paste into Plan Explorer. –  Mark Storey-Smith Nov 2 '11 at 15:52
    
Yes Yes and Yes. Pulled a copy of prod down before we ran it. So at least that part is apples to apples. –  RateControl Nov 2 '11 at 15:52
add comment

5 Answers

up vote 4 down vote accepted

I'd like you to rework the query.

You have sargability issues, and are even using scalar function calls in there, which will be hurting the query too. You may want to make a FullName computed column on Table2 and put an index on that, making sure your index INCLUDEs FirstName and LastName. You should also add indexes that help the other

Also, make an inline table-valued function to do your "RemoveNonLetter" functionality, and rework your query to use that, probably using APPLY as I've done here.

And definitely check that bug that Paul's answer refers to.

INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FullName)) AS fn (FullName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FamilyName)) AS famn (FamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.Child1Name)) AS c1n (Child1Name)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyName)) AS sfn (StepFamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyNameChild2)) AS sfnc2 (StepFamilyNameChild2)
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FullName = fn.FullName
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FamilyName = famn.FamilyName AND Alias2.Child1Name = c1n.Child1Name
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.StepFamilyName = sfn.StepFamilyName AND Alias2.StepFamilyNameChild1 = sfnc2.StepFamilyNameChild2
 ) 
 AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )
;
share|improve this answer
    
I have showed your proposed changes to the query to our programmer. Thanks for that! –  RateControl Nov 4 '11 at 12:46
    
the programmer finally had the time to rewrite the query. It performs in dev/test/prod as expected. –  RateControl Nov 22 '11 at 19:26
add comment

I'd suggest that that the remote data is spooled locally because one of

  1. Linked Server settings (such as collation) are not the same
  2. Local collation is not the same as remote, despite linked server settings
  3. Query can not run correctly remotely because of permissions

For point 1, see sp_serveroption
And for point 2, but also check server/db collations.

For point 3, see these from Linchi Shea:

You are asking SQL Server to process all data locally,a s per my answer here: Performance implications of using OPENQUERY in a view

Edit

On second look, I see 2 remote calls on the "good" plan rather than one. This confirms what I say here

share|improve this answer
    
Thanks, I'll check out the linked server issue. –  RateControl Nov 2 '11 at 19:24
    
Sorry for the long delay. 1: I checked the linked server settings, they are the same 2: I also checked and implicitly set the collation of the linked server properties to the remote server. 3: The security context I am still working on doing A/B testing. I am still stubmling on the logic behind the drastically different exec plans. From the R2 server to the R2 server it is only running the select (pulling in well over 150K rows), then doing the joins. Where as the 2005 to R2 it is doing the selects, and joins on the remote server. The security context for both scenarios are the same. –  RateControl Nov 10 '11 at 13:48
add comment

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.

share|improve this answer
    
Thanks for the link to the bug. I'll definitely check it out. –  RateControl Nov 3 '11 at 16:35
add comment

+1 on the Try Rewriting your query comment from datagod.

I am also wondering if you are bumping into a permissions problem on the linked server side leading to this slowdown. I blogged about this linked server slowdown awhile ago. May be worth verifying the perms (is it a SQL linked server? Or is it another DBMS? If the latter, then you won't get great statistics across anyway)

Do you have SQL Server 2005 on the test environment still to try this query on and rule out the environment?

Have you rebuilt statistics since upgrading?

share|improve this answer
    
Thanks for the help. Per your and gbn's suggestion I am taking a look at the properties of the linked server. –  RateControl Nov 2 '11 at 20:11
add comment

There are so many problems with this comparison...I just dont' know where to start.

  1. Get the exact specs for your production and test machines.

  2. Determine the network links between the various linked severs in both environments. Are they the same speed? Are the servers located beside each other in both environments?

  3. Is there any way at all that you could re-write the query to NOT use linked servers? Joining tables across servers leaves you vulnerable to topology changes, and his horribly slow in most cases.

  4. Use of NOT and OR's typically leads to full table scans. Try rewriting the query.

share|improve this answer
    
1: Tell me about it. That is why I need more help looking at it! 2: Same environment. All 3 are virtualized. 3: That is the way it is for now, until I can move all of the databases to one server. –  RateControl Nov 2 '11 at 15:53
2  
Do you have the same amount of data in each environment? If the databases are recent copies, then I say the difference in SQL is enough to warrant rewriting your query. It should be re-written anyway. Sorry, it's the truth. –  datagod Nov 2 '11 at 18:44
1  
oh no offense taken from your comments. Yes, I have pulled down a copy of the production db and restored it to the test server –  RateControl Nov 2 '11 at 19:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.