title: DELETE operation in SQL Server extremely slow
body: <p>I have a DB on an SQL server 2017. A simple table with some foreign keys and cascading deletes. The structure is real simple. </p>
  
  <p>The main <code>tblAS0002</code> has a clustered index with the field ID (int). Also all child tables <code>tblAS0002_xxx</code> have an foreign key field <code>IdAddress</code> refereing to the Id field in <code>tblAS0002</code>. The child tables are nearly empty for this sample: less than 20000 records, no more than 2 records per main record, mostly all main records have no associated records in the child tables.</p>
  
  <p>Als child tables haven an index upon <code>IdAddress</code> (the foreignkey to the <code>tblAS0002.Id</code> field)</p>
  
  <p>The main table (<code>tblAS0002</code>) has about 2.5 million records. For this test I choose a record that has no records in the child tables. When I execute now a delete of 1 record, the execution time is above 60secs.</p>
  
  <p>The statement I execute is a direct <code>DELETE FROM tblAS0002 WHERE Id=2218801</code></p>
  
  <p>See execution plan below in a public link.</p>
  
  <p>Looking into the execution plan, I can see only one operation taking over 1 minute, and it is a scan of the primary key index of the tblAS0002. But why?</p>
  
  <ul>
  <li>DBCC CHECKDB was executed. </li>
  <li>All indexes were recreated.</li>
  <li>The tblAS0002_History_Links has 0 records. (the table with the long execution time)</li>
  <li>The record I deleted for test purposes has no associated record in any child table! The primary Id does not occur in any other table as IdAddress (foreign key)</li>
  </ul>
  
  <p>What happens here with the slow delete? I can't see a missing index anywhere. What can I do find the problem behind the scene?</p>
  
  <p>Here the <a href="https://pastebin.com/hUTRY1r2" rel="nofollow noreferrer">Create Script for the tables</a> (simplified as far as possible)</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=S1eqT9KgU" rel="nofollow noreferrer">Paste of the execution plan</a></p>