Mystery: Slow delete in SQL table

Question

I have a SQL table with 29 columns and 3000-4000 rows. It has a few spatial fields (2 geometry and 1 geography) but otherwise nothing special.

Deleting a single row from this table now takes about 1000 ms, both in production and development environments.

Delete from AdminDivisions 
where AdminDivisionID=(Select top 1 AdminDivisionID from AdminDivisions)

screenshot 1

Here's what I've tried (in a dev environment):

I've made a clone of the table and painstakingly recreated all of its triggers, foreign keys, constraints, and indexes. Deleting a row from the clone is instantaneous.
```
 Delete from _ad
 where AdminDivisionID=(Select top 1 AdminDivisionID from AdminDivisions)
```

screenshot 2

I've deleted all triggers, foreign keys, constraints, and indexes from the original table. Deleting a row from the stripped-down table still takes around 1000 ms on average.

What could I try next?

Update 1

Here's the execution plan (full size here). It is the FKs referencing this table - and the table's own PK index. Still not sure where to go from here.

Update 2

@Martin: Here is the output with SET STATISTICS IO ON:

Table 'Instances'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Locations'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AwardsAdminDivisionsXtab'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IndicatorResultComments'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IndicatorResults'. Scan count 1, logical reads 35958, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transactions'. Scan count 1, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogicCheckViolations'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdminDivisions'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AspNet_SqlCacheTablesForChangeNotification'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What does top 1 mean without an order by? Also what is it actually waiting on (e.g. see wait_type in sys.dm_exec_requests)? If you're going to do that why not just say `DELETE TOP (1) dbo._ad;`? Also note that this will likely cause a table scan when you go to drastic measures like dropping all indexes (including the clustered). — Aaron Bertrand, Dec 15 '13 at 20:07
Show the execution plan. http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Tim Schmelter, Dec 15 '13 at 20:08
And in addition to the requests above it would also be interesting to see the output of `SET STATISTICS IO ON;` after deleting a row. — Martin Smith, Dec 15 '13 at 20:28
Do you have an index (possibly a clustered index) on `AdminDivisionID`? Also: have you had a look at updating the table's statistics? — marc_s, Dec 15 '13 at 20:36
Do you have FKs referencing a PK/UQ (unique key) within `AdminDivisions` ? Maybe those FKs aren't indexed thus causing scans. — Bogdan Sahlean, Dec 15 '13 at 20:38
You need to test with a copy of whole database but not only one table. May be you have cascading deletion set and this results in delay. — Serg, Dec 15 '13 at 20:53
@Aaron The `Select top 1 AdminDivisionID from AdminDivisions` is just to isolate a single row - didn't know about `Delete Top (1)`. At any rate the result is the same if I give it a constant AdminDivisionID to delete. — Herb Caudill, Dec 15 '13 at 21:02
How long does "Select top 1 AdminDivisionID from AdminDivisions" take? Are you sure it's the "delete" that's taking the time, and not the select statement? — Neville Kuyt, Dec 15 '13 at 21:16
How many rows in `Transactions` and `IndicatorResults`? They both have scans not seeks. Also output of `SET STATISTICS IO ON;` please? — Martin Smith, Dec 15 '13 at 21:18
@Neville - it take same amount of time if I give it a constant PK to delete. — Herb Caudill, Dec 15 '13 at 21:39
So looks like you need to add an index to `IndicatorResults` then to allow the FK validation to be more efficient. Also probably wouldn't hurt to add the missing index on `Transactions` — Martin Smith, Dec 15 '13 at 21:45

Bogdan Sahlean · Accepted Answer · 2013-12-15T21:09:03.327

Just a guess because I don't have enough information:

Maybe the target table (AdminDivisions) is a parent table and there is a FK referencing the PK or a UQ (unique key) of / within AdminDivisions and
This FK has a ON DELETE CASCADE option and
This FK isn't indexed.

Example:

CREATE TABLE dbo.ParentTable(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL
);
GO
CREATE TABLE dbo.ChildTable(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ParentID INT NOT NULL 
        REFERENCES dbo.ParentTable(ID) ON DELETE CASCADE
);
GO
DELETE dbo.ParentTable
WHERE ID = 1;

The execution plan for DELETE dbo.ParentTable ... statement before creating an index on dbo.ChildTable(ParentID) : enter image description here

The execution plan for DELETE dbo.ParentTable ... statement after creating an index on dbo.ChildTable(ParentID) (CREATE INDEX IX1 ON dbo.ChildTable(ParentID);) : enter image description here

Thanks Bogdan and all others who commented. In your list above #1 and #3 are true (there's no cascading delete) but it was the foreign key constraint (with no index) that was slowing things down dramatically. — Herb Caudill, Dec 16 '13 at 17:51

Mystery: Slow delete in SQL table

Update 1

Update 2

1 Answers1