May 16, 2002 at 2:04 pm
I am trying to tune a large query and am seeing several Index Spool/Eager Spool objects in showplan. What do these objects indicate?
May 16, 2002 at 4:35 pm
From BOL
quote:
The Eager Spool logical operator will consume the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.The Eager Spool operator will build its spool file eagerly. When the spool's parent operator asks for the first row, the spool operator will consume all rows from its input operator and store them in the spool.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 7:56 am
So, this operation occurs when a SQL statement is using the tempdb to process a result set?
May 20, 2002 at 8:19 am
Not really as there are dozens of items that use the tempDb for query support. An eager is an object built by the query execution manager to augment the speed to handle the query. And eager spool just takes the data inputs it will use to build this temporary object and as soon as the first input item is called into need it will eagerly plan for the others to be called by pulling them in immediately so they are ready for access. The Index spool you see in addition to that means that an index was applied to the object to make referencing each item in the sppol faster to find. Usually it is just building a clustered (sorted) index, thus increasing the efficency of the object when used in the query execution. Another item you may see is a Lazy Sppol whih works exactly the same as an Eager spool, but only calls data into the object the first time it is needed while retaining the original items in it's list. Generally you will see these items in regards to IN or EXISTS operations but you may see in some joins as well as other situations.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply