Explanation requested for slow DELETE with SQL Server

Question

I would like to get some extra insights/reasoning for SQL Server delete behavior. We have a fairly large database of over 1800 GB.

In there are some very shallow tables (only a few integer columns) with many millions of rows. When we delete 10,000s of rows from these shallow tables the delete queries are generally quite fast (at most a handful of seconds).

We also have a table with a field of type image storing images averaging 100 KB. When we delete only a few thousand rows from this table, it takes well over a minute.

Although the difference is clear (much more data size-wise is deleted), I'm am eager to learn more about what happens inside SQL Server. So that I can better understand the latter deletes to be so much slower.

Can anyone please shed some light?

Answer 1

much more data size-wise is deleted

Deleting a 100kb image blob is actually not a size-of-data operation. The blob is deallocated, not deleted, and there is no full-image logging. You can easily test this:

create database blob
go

use blob
go

create table t (id int not null identity(1,1), blob image)
go

insert into t (blob) values (
  replicate(
    cast(0x000102030405060708090a0b0c0d0e0f as varbinary(max)), 
    100*1024/16))
go 10

alter database blob set recovery full
go

backup database blob to disk='nul:'
go

delete from t where id = 3
go

select * from fn_dblog(null, null)
go

The log records you'll see will be something along the lines of:

00000026:0000008e:0001  LOP_BEGIN_XACT  LCX_NULL    0000:00000304   0x0000  76  124
00000026:0000008e:0002  LOP_LOCK_XACT   LCX_NULL    0000:00000304   0x0000  24  56
00000026:0000008e:0003  LOP_MODIFY_ROW  LCX_PFS     0000:00000304   0x0000  62  92
00000026:0000008e:0004  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
00000026:0000008e:0005  LOP_MODIFY_ROW  LCX_PFS     0000:00000304   0x0000  62  92
00000026:0000008e:0006  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
00000026:0000008e:0007  LOP_MODIFY_ROW  LCX_PFS     0000:00000304   0x0000  62  92
00000026:0000008e:0008  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
00000026:0000008e:0009  LOP_MODIFY_ROW  LCX_PFS     0000:00000304   0x0000  62  92
00000026:0000008e:000a  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
00000026:0000008e:000b  LOP_MODIFY_ROW  LCX_PFS     0000:00000304   0x0000  62  92
00000026:0000008e:000c  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
...    
00000026:0000008e:0022  LOP_HOBT_DELTA  LCX_NULL    0000:00000304   0x0000  64  64
00000026:0000008e:0023  LOP_DELETE_ROWS LCX_TEXT_MIX    0000:00000304   0x0000  62  172
00000026:0000008e:0024  LOP_DELETE_ROWS LCX_HEAP    0000:00000304   0x0000  62  120
00000026:0000008e:0025  LOP_COMMIT_XACT LCX_NULL    0000:00000304   0x0000  80  84

As you can see there is no 'DELETE' record with +102400 bytes of data for the row containing the image column. There are a bunch of deallocations (the PFS/IAM/GAM operation) and a simple row deletion (heap in my case, would look very similar for B-Tree had I remembered to declare ID as PK...). For more details see How to read and interpret the SQL Server log.

Which leaves open the original question: why is one delete slower than the other? I recommend you read How to analyse SQL Server performance. Follow the methodology described to capture the waits for a specific statement and see what the cause is. See Analyzing individual query execution, specially the part about Analyzing individual query execution wait times. Only after you measured we'll be able to answer the riddle. there could be many factors: more blocking due to concurrent reads on the blob table, missing indexes to locate the DELETE candidate rows on one table, triggers running etc etc. The methodology linked will help you pinpoint the cause.