Is Indexes needed for Delete...

If you’re only indexing to make reads faster, you need to think again. Indexes can make some operation in the database faster, even deletes

Problem Is Deletes are Very Slow?

         When I received an email from a client asking why deletes are slow. I rattled off a few quick possibilities and promised that I’d look into it as soon as I was able to. It took me more time than I’d expected to dig into the problem, but I found something interesting that I should have come to mind right from the start.

         My initial thoughts were that there might be a cascading delete taking place in the hierarchy. After some initial checks into cascading deletes, fragmentation, statistics, and triggers, I ran an actual delete on a test system and found something very interesting – almost all of the time spent deleting the row was spent performing a clustered index scan on a different table.

What Happens During a Delete?

 

When you try to delete a row, SQL Server will check for dependent rows by examining all foreign keys. It will then check any related tables for data. If there is an index, SQL Server will use that index to check for related data. If there isn’t an index, though, SQL Server will have to scan the table for data.

Deletes and Table Scans

Don’t believe me? Try this out yourself. 

Make a new database as Adventures. Copy data in from the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in AdventureWorks. I used the queries below 

Select * into Timesheet.dbo.SalesOrderHeader from AdventureWorks2008R2.Sales.SalesOrderHeader

Select * into Timesheet.dbo.SalesOrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail 

Add the Primary Key and Foreign Key Constraints

ALTER TABLE dbo.SalesOrderHeader ADD CONSTRAINT PK_SalesOrderHeader PRIMARY KEY (SalesOrderID);
ALTER TABLE dbo.SalesOrderDetail ADD CONSTRAINT PK_SalesOrderDetail PRIMARY KEY (SalesOrderDetailID);

ALTER TABLE Sales.SalesOrderDetail ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader

FOREIGN KEY (SalesOrderID) REFERENCES dbo.SalesOrderHeader (SalesOrderID) ON DELETE CASCADE;

Apply delete on the respective table

DELETE FROM SalesOrderHeader WHERE SalesOrderID = 43660;

Making Deletes Faster 

 

How would we go about making deletes like this faster? By adding an index, of course. Intelligent readers will have noticed the missing index information in that execution plan I took a screenshot of. In this case, the missing index looks something like this

CREATE NONCLUSTERED INDEX [idx_Daniel] ON [dbo]. [SalesOrderDetail] ([SalesOrderID]) INCLUDE ([SalesOrderDetailID])

GO

          Before adding the index, the query had a cost of 2.35678. After adding the index, the delete has Sub tree cost of 0.0367756. To put it another way: adding one index made the delete operation 64 times faster. When you have a busy environment, even tiny changes like this one can make it faster to find and delete records in the database

 

 

 

 

 

 

 

 

 

 

  

 

 

Regards

Daniel J

Data Mining and Dataware House Architect…