October 3, 2025 at 3:51 am
I've noticed several instances of what looks like a recursive insert with the format:
insert [dbo].[MyTable] select * from [dbo].[MyTable]
I've been able to show that it isn't in fact inserting rows reclusively, but the most likely thing it seems to be associated with is a Create Clustered Index operation. Unfortunately I can't correlate every incident of this back to an index create statement.
Does anyone know what SQL is doing here and why?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
October 3, 2025 at 3:11 pm
Hey Leo,
your assumption is correct. This is an indicator of an Clustered Index Creation or Clustered Index REBUILD. This pattern (INSERT INTO MyTable SELECT * FROM MyTable) is an internal mechanism SQL Server may use during clustered index creation or rebuilds on heaps.
It is a metadata-driven copy operation to transition the table structure.
You can check it in the [command] in sys.dm_exec_requests
SELECT r.session_id,
r.command,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%INSERT%MyTable%SELECT%MyTable%'
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply