By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

Table Spool in simple INSERT...SELECT causing performance problems

P: n/a

I have a table which has a few million records. It has an IDENTITY
column that serves as the primary key. In a part of our application
here, a previous record may need to be copied as a new row. Within a
stored procedure exists SQL like:

INSERT INTO [My_DB].[dbo].[My_Table] (col1, col2, col3, ...)
SELECT @var1, col2, col3, ...
FROM My_Table
WHERE my_id = @my_id --This column is the IDENTITY column PK

For some reason, this query is using a Table Spool, and I can't figure
out why. I know the definition of a Table Spool (records saved in
tempdb so that rewinds can use the temp table), but I don't understand
why it is being used here or how to prevent it.

I've tried rebuilding all of the indexes on the table and that did not

I tried creating a scaled-down example that reproduces the problem,
but I could not and it's not practical to post the table definition
and data to reproduce the problem here. If there is further
information that would help someone determine the reason for the Table
Spool or something that you would like me to check or test, please let
me know.

Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.