Very slow DELETE query
Add a Primary key to your table variables and watch them scream
DECLARE @IdList1 TABLE(Id INT primary Key not null)DECLARE @IdList2 TABLE(Id INT primary Key not null)because there's no index on these table variables, any joins or subqueries must examine on the order of 10,000 times 10,000 = 100,000,000 pairs of values.
SQL Server compiles the plan when the table variable is empty and does not recompile it when rows are added. Try
DELETE FROM @IdList1WHERE Id IN (SELECT Id FROM @IdList2)OPTION (RECOMPILE)This will take account of the actual number of rows contained in the table variable and get rid of the nested loops plan
Of course creating an index on Id via a constraint may well be beneficial for other queries using the table variable too.
The tables in table variables can have primary keys, so if your data supports uniqueness for these Ids, you may be able to improve performance by going for
DECLARE @IdList1 TABLE(Id INT PRIMARY KEY)DECLARE @IdList2 TABLE(Id INT PRIMARY KEY)