SQL Server Performance Forum – Threads Archive

Lazy Spool cost increase

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?

---

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

---

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.

---

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.

---

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.

---

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.

---

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

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

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.

---

]]>