SQL Server Performance

Lazy Spool cost increase

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by mpex04, Dec 13, 2005.

  1. mpex04 New Member

    Recently we upgraded our developement box from SQL 2000 to 2005. We are now experiencing a large increase in query cost when the Lazy Spool is used. When comparing the execution plans between 2000 and 2005, the only difference is the cost of the Lazy Spool operation. For example:<br /><br /><b>Table def (same for both):</b><br />OrdNo varchar8<br />SubOrdNovarchar4<br />EntryDt char8<br />ActionDtchar8<br />Who char8<br />MessageCdchar6<br />ActionCdchar40<br />Seq char4<br />DivCd char2<br /><br />CRMMACSOrderActionsHistory has 1 million records<br />CRMOrderActionsHistory has 12 million records<br /><br /><b>Query</b><br />SELECT<br />OrdNo,<br />SubOrdNo,<br />EntryDt,<br />ActionDt,<br />Who,<br />MessageCd,<br />ActionCd,<br />Seq,<br />DivCd<br />FROM CRMOrderActionsHistory<br />WHERE OrdNo NOT IN (SELECT OrdNo FROM CRMMACSOrderActionsHistory)<br /><br /><b>Execution plan</b><br /> |--Hash Match(Right Anti Semi Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMMACSOrderActionsHistory].[OrdNo])=([CRMOrderActionsHistory].[OrdNo]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMOrderActionsHistory].[OrdNo]=[CRMMACSOrderActionsHistory].[OrdNo]))<br /> |--Hash Match(Aggregate, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMMACSOrderActionsHistory].[OrdNo]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMMACSOrderActionsHistory].[OrdNo]=[CRMMACSOrderActionsHistory].[OrdNo]))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PRODDW].[dbo].[CRMMACSOrderActionsHistory]))<br /> |--Nested Loops(Left Anti Semi Join)<br /> |--Nested Loops(Left Anti Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMOrderActionsHistory].[OrdNo]=NULL))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PRODDW].[dbo].[CRMOrderActionsHistory]))<br /> | |--Row Count Spool<br /> | |--Top(1)<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PRODDW].[dbo].[CRMMACSOrderActionsHistory]))<br /><font color="red"> |--Row Count Spool<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PRODDW].[dbo].[CRMMACSOrderActionsHistory]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CRMMACSOrderActionsHistory].[OrdNo]=NULL))</font id="red"><br /><br />The part highlighted in red has a cost of 0 in SQL Server 2000, but 1162 in SQL Server 2005. Can any give insight into why this changed dramatically?
  2. joechang New Member

    1. the formulas are different,
    2. the statistics for estimating rows are different.

    i am also going to guess that in this case, S2K estimates 0 because of the Top 1, but 2005 is estimating better knowing that not every case is going to have a cheap Top 1.

    i would not worry about differences in plan cost between 2000 & 2005.
    but rather, just use SET STATISTICS TIME ON and compare the difference in time
  3. mpex04 New Member

    It now takes roughly 7 hours to execute that statement in SQL Server 2005 compared to 15 minutes in 2000. That is why I'm worried about the cost difference.
  4. Adriaan New Member

    Does the plan change if you use ...

    FROM CRMOrderActionsHistory A
    LEFT JOIN CRMMACSOrderActionsHistory B
    ON A.OrdNo = B.OrdNo
    WHERE B.OrdNo IS NULL

    ... or ...

    FROM CRMOrderActionsHistory A
    WHERE NOT EXISTS
    (SELECT OrdNo FROM CRMMACSOrderActionsHistory B
    WHERE B.OrdNo = A.OrdNo)

    Does CRMMACSOrderActionsHistory have any sort of index on OrdNo? Did the number of rows increase since the upgrade?

    [edit:]
    Did you forget to create an optimization job on the database, one that you had on the old server?
  5. mpex04 New Member

    quote:Originally posted by Adriaan

    Does the plan change if you use ...

    FROM CRMOrderActionsHistory A
    LEFT JOIN CRMMACSOrderActionsHistory B
    ON A.OrdNo = B.OrdNo
    WHERE B.OrdNo IS NULL

    ... or ...

    FROM CRMOrderActionsHistory A
    WHERE NOT EXISTS
    (SELECT OrdNo FROM CRMMACSOrderActionsHistory B
    WHERE B.OrdNo = A.OrdNo)

    Does CRMMACSOrderActionsHistory have any sort of index on OrdNo? Did the number of rows increase since the upgrade?

    [edit:]
    Did you forget to create an optimization job on the database, one that you had on the old server?

    The database was exactly the same before and after the upgrade and CRMMACSOrderActionsHistory did and does not have indexes.

    Using the NOT EXISTS clause fixes the performance issues, but I'm seeing an increase in almost all procedures that use the NOT IN clause. In SQL Server 2000, the execution plans have close to the same cost between NOT EXISTS and NOT IN, but in 2005 there is a huge difference.

    What optimization job are you referring to? I feel like I missed something when upgrading because a query like this should not be less effecient to this degree.
  6. Luis Martin Moderator

    I suggest to run Tuning Advisor. I find better indexes with 2005 than 2000.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. Adriaan New Member

    On the old server, did you have the "Auto update statistics" and "Auto create statistics" options activated? Or did you have a job that called the DBCC optimization commands?

    What's the status on that on the new server?

    By the way, you say there are NO indexes on the table against which you compare - then what kind of response time do you expect to get? Does it even have a primary key?

    IIRC, the NOT IN syntax is identified somewhere in BOL as "cannot be optimized". Perhaps SQL 2005 has silently dropped routines that SQL 2000 used to get reasonable performance for NOT IN?

    Anyway, the EXISTS and NOT EXISTS syntax seems to have helped you out already.
  8. mpex04 New Member

    quote:Originally posted by Adriaan

    On the old server, did you have the "Auto update statistics" and "Auto create statistics" options activated? Or did you have a job that called the DBCC optimization commands?

    What's the status on that on the new server?

    The database has auto create and update enable just like before the upgrade. It is also running in simple recovery mode.


    quote:Originally posted by Adriaan
    By the way, you say there are NO indexes on the table against which you compare - then what kind of response time do you expect to get? Does it even have a primary key?

    I expect to have similiar response time between SQL Server 2000 and 2005, but going from 15 minutes to 7 hours seems off.


    quote:Originally posted by Adriaan
    IIRC, the NOT IN syntax is identified somewhere in BOL as "cannot be optimized". Perhaps SQL 2005 has silently dropped routines that SQL 2000 used to get reasonable performance for NOT IN?

    Anyway, the EXISTS and NOT EXISTS syntax seems to have helped you out already.

    I guess the problem is that all NOT IN queries are not working the same after the upgrade. We can work on converting out many stored procedures to use the EXISTS syntax, but I was wanting to understand why the query engine changed. Like you said, maybe Microsoft has changed how it handles the NOT IN clause.

Share This Page