Search code examples
sql-serversql-server-2017

DELETE operation in SQL Server extremely slow

I have a DB on an SQL server 2017. A simple table with some foreign keys and cascading deletes. The structure is real simple.

The main tblAS0002 has a clustered index with the field ID (int). Also all child tables tblAS0002_xxx have an foreign key field IdAddress refereing to the Id field in tblAS0002. 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.

Als child tables haven an index upon IdAddress (the foreignkey to the tblAS0002.Id field)

The main table (tblAS0002) 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.

The statement I execute is a direct DELETE FROM tblAS0002 WHERE Id=2218801

See execution plan below in a public link.

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?

  • DBCC CHECKDB was executed.
  • All indexes were recreated.
  • The tblAS0002_History_Links has 0 records. (the table with the long execution time)
  • 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)

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?

Here the Create Script for the tables (simplified as far as possible)

Paste of the execution plan

Solution

  • Nice execution plan!!! There are a series of cascade deletes (which in turn fire other cascade deletes). every delete-->spool is yet another cascade deletion: the deleted rows are put into a spool/temp structure and the spool is used for finding the referencing rows from other tables.

    eg. delete tblAS0002 -> spool[tblAS0002]
delete history{join}spool[tblAS0002] -> spool[history]
delete history_links{join}spool[history]

    Looking into the execution plan, I can see only one operation takeing over 1 minute, and it is a scan of the primary key index of the tblAS0002. But why?

    this is the operation at the bottom of the plan.

    There is a self-reference in tblAS0002: tblAS0002.IdParent --> references tblAS0002.Id

    There is no index for IdParent, deleting a single Id requires a table scan of tblAS0002 in order to verify a not exists== assert:!(left semi join) , that the deleted id is not referenced by any IdParent.