I Executed SQL on database that looked like this:
SET IDENTITY_INSERT table1 ON GO BEGIN TRANSACTION GO INSERT INTO table1 SELECT ... FROM table2 GO DELETE FROM table2 ... GO COMMIT TRANSACTION GO SET IDENTITY_INSERT table1 OFF GO
Problem is my table was locked on last statement
SET IDENTITY_INSERT table1 OFF, all previous statements took around 6 hours, but my table is locked on this last statement for 1 day now.
In Activity Monitor it has connection that dont execute any command but is head blocker of locking chain, i killed it but nothing changed only that is in rollback state but it's still eating all I/O.
My question is what this session is doing and how to avoid it? It looks like IDENTITY_INSERT is almost costly like full table recreation for some reason, i don't know how this could cause any downsides on performance side.
Additional info: there was not identity collision during insert, bcs i insert operated on lower identity values than initial set on table1
CREATE TABLE [dbo].[table1] ( [id_table1] BIGINT IDENTITY NOT NULL, [id_table3] INT NOT NULL, [some_id] BIGINT NOT NULL, [some_time2] DATETIME2 NOT NULL, [some_time] DATETIME2 NOT NULL, [some_value] FLOAT (53) NOT NULL, [some_value2] FLOAT (53) NOT NULL, [some_value3] TINYINT NULL, CONSTRAINT [FK_table1_table3] FOREIGN KEY ([id_table3]) REFERENCES [dbo].[table3] ([id_table3]), INDEX [IX_table1] CLUSTERED ([id_table3] ASC, [some_time] ASC, [id_table1] ASC) WITH (DATA_COMPRESSION = PAGE) ) WITH (DATA_COMPRESSION = PAGE); GO CREATE INDEX [IX_table1_some_time] ON [dbo].[table1] ([some_time] ASC, [id_table1] ASC) INCLUDE ([id_table3], [some_value], [some_value2], [some_value3]) WITH (DATA_COMPRESSION = PAGE) GO