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

[Thomas R. Hummel]

Hello,

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
help.

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.

Thanks!
-Tom.