CODEWITHSUNDEEP
sql-serveroptimization
cindi
cindi

Reputation: 4801

Table Spool/Eager Spool

I have a query 

select * into NewTab from OpenQuery(rmtServer, 'select c1, c2 from rmtTab')

When I look at the execution plan, it tells me that it performs a 'Table Spool/Eager Spool' that 'stores the data in a temporary table to optimize rewinds'

Now I don't anticipate any rewinds. If there is a crash of some sort, I can just drop newTab and start over.

Is there any way I can stop it from storing the data in a temporary table?

Upvotes: 3

Views: 3864

Answers (2)

gbn
gbn

Reputation: 432471

It's probably the openquery causing it.

There is no information on how many rows, no statistics, nothing so SQL Server will simply spool the results to allow it to evaluate the later bits I suspect. That's the basic idea.

I'd suggest separating the creation and fill of newtab.

By the way, rewind is not rollback. Rewind has nothing to do with the transaction safety. It's SQL Server anticipating reuse of the rows. Which is correct, because the openquery is a black box.

Look near the bottom of this Simple Talk article for rewinds. You have a "Remote query".

Edit

Based one something I found last week only, look at sp_tableoption.

When used with the OPENROWSET bulk rowset provider to import data into a table without indexes, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking

Try TABLOCK on your fill. We had some fun with a client developer using .net SQLBulkCopy giving very bad performance.

Also this from, Kalen Delaney

It's not intuitive.

Upvotes: 4

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

create the NewTab first and then do insert into... from openquery.

Upvotes: 1

Related Questions