title: Ways to avoid eager spool operations on SQL Server
body: <p>I have an ETL process that involves a stored procedure that makes heavy use of <code>SELECT INTO</code> statements (minimally logged and therefore faster as they generate less log traffic).  Of the batch of work that takes place in one particular stored the stored procedure several of the most expensive operations are eager spools that appear to just buffer the query results and then copy them into the table just being made.</p>
  
  <p>The MSDN documentation on <a href="http://msdn.microsoft.com/en-us/library/ms190435.aspx" rel="noreferrer">eager spools</a> is quite sparse. Does anyone have a deeper insight into whether these are really necessary (and under what circumstances)?  I have a few theories that may or may not make sense, but no success in eliminating these from the queries.  </p>
  
  <p>The .sqlplan files are quite large (160kb) so I guess it's probably not reasonable to post them directly to a forum.</p>
  
  <p>So, here are some theories that may be amenable to specific answers:</p>
  
  <ul>
  <li>The query uses some UDFs for data transformation, such as parsing formatted dates. Does this data transformation necessitate the use of eager spools to allocate sensible types (e.g. varchar lengths) to the table before it constructs it?</li>
  <li>As an extension of the question above, does anyone have a deeper view of what does or does not drive this operation in a query?</li>
  </ul>