Let’s start with the Stack Overflow database (any size will work), drop all the indexes on the Users table, and run a delete:
SET STATISTICS IO ON; GO BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N’Brent Ozar’;
I’m using SET STATISTICS IO ON like we talk about in How to Think Like the Engine to illustrate how much data we read, and I’m doing it in a transaction so I can repeatedly roll it back, showing the effects each time. Here’s the actual execution plan:
We read that from right to left. The first thing SQL Server has to do