Prev: How to use CDO to send mail with attachment
Next: Query LDAP user group membership from SQL Server
From: Ian Boyd on 15 Feb 2005 11:55
i see a Table Spool/Lazy Spool operation that is turning 20,879 rows into
34,512,987. And then after a Nested Loop/Left Outer Join to 1,653 rows, it
is turned into 1,653 rows.

My question is, what is the Table Spool/Lazy Spool trying to do?

To help explain my question, i can give a similar example. i know what a
"Bookmark Lookup" is, and why the server is doing it (too many rows
scattered all over the table, and if there are a little more rows the server
would switch to a table scan rather than trying to perform a bookmark
lookup). i also know that a Bookmark Lookup can be fixed by reconsidering
the clustered index on the table, or giving a covering index sorted by the
lookup field.


So, now answer what a Table Spool/Lazy Spool is. What is the server
attempting to accomplish by using a "Table Spool/Lazy Spool." What is the
ideal alternative. i just can't imagine any reason why the server is turning
20k rows into 34 million to perform a join against 1.5k rows, only to have
it turn into 1.5k rows.

"Table Spool/Lazy Spool: Stores data from the input into a temporary table
in order to optimize rewinds."

What are rewinds? How would the rewinds not be optimized if you didn't store
the data from the input into a temporary table? How is having a 1653-fold
increase in temporary table size more optimal? I do see that the server is
taking 20,879 input rows, and multiplying it by 1,653 rows it is going to be
joining to, and getting the 34,512,987 rows to actually do the join. But how
is that more optimal?


From: Kalen Delaney on 16 Feb 2005 00:47
Hi Ian

Rewinds occur when SQL Server has to repeatedly access the rows of an inner
table in a join operation.
Because the inner table has be examined for each qualifying row in the outer
table, the data from the inner table can be spooled, which means saving it
to a location like a worktable to make it easier to access, over and over. A
lazy spool means the worktable is built only as needed.

You really gave us no information that can help troubleshoot this situation;
if indeed troubleshooting is required.
How many rows are in the tables, what is the join density, what is the query
you are running and how many rows do you expect back?
That, plus the DDL of the tables, and the complete showplan, would be a
start. Also, please let us know why you think this plan is not optimal?

My only guess with such limited information is that a (better) index on the
inner table might help.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Ian Boyd" <ian.msnews010(a)avatopia.com> wrote in message
news:u9cGm83EFHA.3244(a)TK2MSFTNGP15.phx.gbl...
>i see a Table Spool/Lazy Spool operation that is turning 20,879 rows into
>34,512,987. And then after a Nested Loop/Left Outer Join to 1,653 rows, it
>is turned into 1,653 rows.
>
> My question is, what is the Table Spool/Lazy Spool trying to do?
>
> To help explain my question, i can give a similar example. i know what a
> "Bookmark Lookup" is, and why the server is doing it (too many rows
> scattered all over the table, and if there are a little more rows the
> server would switch to a table scan rather than trying to perform a
> bookmark lookup). i also know that a Bookmark Lookup can be fixed by
> reconsidering the clustered index on the table, or giving a covering index
> sorted by the lookup field.
>
>
> So, now answer what a Table Spool/Lazy Spool is. What is the server
> attempting to accomplish by using a "Table Spool/Lazy Spool." What is the
> ideal alternative. i just can't imagine any reason why the server is
> turning 20k rows into 34 million to perform a join against 1.5k rows, only
> to have it turn into 1.5k rows.
>
> "Table Spool/Lazy Spool: Stores data from the input into a temporary table
> in order to optimize rewinds."
>
> What are rewinds? How would the rewinds not be optimized if you didn't
> store the data from the input into a temporary table? How is having a
> 1653-fold increase in temporary table size more optimal? I do see that the
> server is taking 20,879 input rows, and multiplying it by 1,653 rows it is
> going to be joining to, and getting the 34,512,987 rows to actually do the
> join. But how is that more optimal?
>


From: Ian Boyd on 18 Feb 2005 14:16
> How many rows are in the tables

How does how many rows in what tables affect the use of a Table Spool/Lazy
spool?
Does a Table Spool/Lazy Spool happen if there are many rows in the inner
table and few in the outer?
Does a Table Spool/Lazy Spool happen if there are few rows in the inner
table and many in the outer?
Does a Table Spool/Lazy Spool happen if there are few rows in the inner
table and few rows in the outer?
Does a Table Spool/Lazy Spool happen if there are many rows in the inner
table and many rows in the outer?

> what is the join density
i assume you mean index selectivity?
How does index selectivity affect SQL Server's decision to use a Table
Spool/Lazy spool?

>please let us know why you think this plan is not optimal?
SQL Server is turning 20k rows into 30 million. That seems excessive.


i can't include DDL or the query - at least i shouldn't bother. It's over
linked servers, with too many tables.

i could spend half a day trying to assemble similar tables, but the problem
i am too sure wouldn't happen again.

Besides, it don't want the query re-written, or DDL critiqued. i want to
understand what is going on.


From: Kalen Delaney on 18 Feb 2005 14:26
Ian

I don't know all the situations that cause the different kind of spooling
operations to occur. It would require analysis of the SQL Server source code
to foind out.

No, join density is not the same as index selectivity, but it is related.
Index selectivity occurs when you have an index, and you are looking for a
particular value. The index statistics can give you an estimate for the
particular value you are looking for, as to how many times it occurs.

Join density is not related to the presence or absence of indexes. When you
have a one-to-many relationship between 2 tables, the join density is the
'many' part. How many rows in table B on average will match each row in
table A? Is it 1-to-3, or 1-to-10 or 1-to100000. It's an average matches
between 2 tables; selectivity is the estimated occurrences for one
particular value using index statistics.

Without more details of your table structures and your queries, I really
give any more info, without writing a book on general tuning guidelines.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Ian Boyd" <ian.msnews010(a)avatopia.com> wrote in message
news:%23dxtL5eFFHA.1932(a)TK2MSFTNGP14.phx.gbl...
>> How many rows are in the tables
>
> How does how many rows in what tables affect the use of a Table Spool/Lazy
> spool?
> Does a Table Spool/Lazy Spool happen if there are many rows in the inner
> table and few in the outer?
> Does a Table Spool/Lazy Spool happen if there are few rows in the inner
> table and many in the outer?
> Does a Table Spool/Lazy Spool happen if there are few rows in the inner
> table and few rows in the outer?
> Does a Table Spool/Lazy Spool happen if there are many rows in the inner
> table and many rows in the outer?
>
>> what is the join density
> i assume you mean index selectivity?
> How does index selectivity affect SQL Server's decision to use a Table
> Spool/Lazy spool?
>
>>please let us know why you think this plan is not optimal?
> SQL Server is turning 20k rows into 30 million. That seems excessive.
>
>
> i can't include DDL or the query - at least i shouldn't bother. It's over
> linked servers, with too many tables.
>
> i could spend half a day trying to assemble similar tables, but the
> problem i am too sure wouldn't happen again.
>
> Besides, it don't want the query re-written, or DDL critiqued. i want to
> understand what is going on.
>


From: Ian Boyd on 18 Feb 2005 14:33
> Without more details of your table structures and your queries, I really
> give any more info, without writing a book on general tuning guidelines.

But you do see my point that "Bookmark Lookups" can be explained quite
easily as to why they happen, when they happen, why they are slow, and how
to fix them?

Is it simply that "Table Spool/Lazy Spool" is not as easy a problem to
explain as the problem of "Bookmark Lookups"?



 | 
Pages: 1
Prev: How to use CDO to send mail with attachment
Next: Query LDAP user group membership from SQL Server