Search code examples
sqlsql-serversqlperformancesql-delete

SQL Delete Request spend significant time in "Index Delete" even if it affects 0 rows

I use MSSQL Server 2008 and I have SQL request with simple condition which periodically deletes old records from table (~3 mil records in the table).

This request executed significant time (~ 10 second) even if it affects 0 rows.

This table has some indexes and in Actual Execution Plan I see that "Index Delete" operations consume all execution time.

Why SQL Server does a lot work on indexes if there no any rows affected by delete operation?

Update:

Request: 

delete t
from Entity t
where t.Revision <= x
AND exists (
    select 1
    from Entity tt
    where tt.Id=t.Id
    and tt.Revision > t.Revision
)

Actual execution plan XML: pastebin.com/up2E3iP1

Solution

  • The work is all doing the hash join. All the other costs are bogus.

    The actual number of rows coming out of that is 0 but it estimates more.

    PLAN

    The costs shown in the rest of the plan are based on the (incorrect) estimates.

    You might find this performs better.

    WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Id 
                              ORDER BY Revision DESC) AS RN
FROM Entity
)
DELETE FROM T
WHERE RN > 1
AND Revision <= 12586705