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?
Best Answer
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:The log records you'll see will be something along the lines of:
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.