Case Study: The 13-Hour Delete — A Real SQL Server Performance Diagnosis Walkthrough

The problem: a nightly cleanup job deletes old records from a large partitioned table. It used to take 20 minutes. This week it took 13 hours, and blocked other processes the entire time. Let’s diagnose it exactly the way you would in production — using the Evidence-First workflow from the start of this course.

-- What did "normal" look like? Check historical job duration logs first.
-- Then measure the current run's I/O and duration directly:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- (run a scoped-down version of the delete against a copy/test environment)

CREATE EVENT SESSION DeleteDiagnosis ON SERVER
ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.sql_text))
ADD EVENT sqlos.wait_info (WHERE wait_type LIKE 'PAGEIOLATCH%' OR wait_type LIKE 'LCK%')
ADD TARGET package0.event_file (SET filename = N'DeleteDiagnosis');
GO
ALTER EVENT SESSION DeleteDiagnosis ON SERVER STATE = START;
-- Let the job run, then inspect captured wait types

-- Capture the actual execution plan for the DELETE's WHERE clause
SELECT * FROM dbo.LargeAuditTable
WHERE customer_id IN (SELECT customer_id FROM dbo.DeactivatedCustomer);
-- Plan shows: Clustered Index Scan on LargeAuditTable — no usable index on customer_id (a foreign key with no supporting index)

CREATE NONCLUSTERED INDEX IX_LargeAuditTable_CustomerId
ON dbo.LargeAuditTable (customer_id)
WITH (ONLINE = ON, MAXDOP = 4);  -- ONLINE to avoid blocking production during creation

-- Re-run the same scoped test, compare against baseline
SET STATISTICS IO ON;
-- Expect: Index Seek instead of Clustered Index Scan, dramatically fewer logical reads

The Lesson, Beyond This One Incident

Notice how this walkthrough used every earlier lesson in this module: the missing foreign-key index is what Ch.109’s evidence-based indexing lesson calls a hypothesis to verify; the clustered index scan is exactly the SARGability/plan-reading pattern from this module’s first lesson; and the whole five-step shape is the Evidence-First workflow from the very start of this course, applied for real.

Foreign keys don’t get an index automatically in SQL Server — unlike the primary key side of the relationship. Any DELETE, UPDATE, or JOIN filtering on a foreign key column without a supporting index is a latent “13-hour delete” waiting for the table to grow large enough to matter.