title: Very slow DELETE query
body: <p>I have problems with SQL performance. For sudden reason the following queries are very slow:</p>
  
  <p>I have two lists which contains Id's of a certain table. I need to delete all records from the first list if the Id's already exists in the second list:</p>
  
  <pre><code>DECLARE @IdList1 TABLE(Id INT)
  DECLARE @IdList2 TABLE(Id INT)
  
  -- Approach 1
  DELETE list1
  FROM @IdList1 list1
  INNER JOIN @IdList2 list2 ON list1.Id = list2.Id
  
  -- Approach 2
  DELETE FROM @IdList1
  WHERE Id IN (SELECT Id FROM @IdList2)
  </code></pre>
  
  <p>It is possible the two lists contains more than 10.000 records. In that case both queries takes each more than 20 seconds to execute.</p>
  
  <p>The execution plan also showed something I don't understand. Maybe that explains why it is so slow:
  <img src="https://i.stack.imgur.com/FsJjT.png" alt="Queryplan of both queries"></p>
  
  <p>I Filled both lists with 10.000 sequential integers so both list contained value 1-10.000 as starting point.</p>
  
  <p>As you can see both queries shows for @IdList2 <em>Actual Number of Rows</em> is 50.005.000!!. @IdList1 is correct (<em>Actual Number of Rows</em> is 10.000)</p>
  
  <p>I know there are other solutions how to solve this. Like filling a third list instaed of removing from first list. But my question is:</p>
  
  <p><strong>Why are these delete queries so slow and why do I see these strange query plans?</strong></p>