Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jul 21 at 11:19

This question came from our site for professional and enthusiast programmers.

    
There's a book on SQL Server internals if you're interested in that kind of thing and want to hear from those close to the source. –  stakx Jul 21 at 7:59
    
My suspicion would be that the image deletes happen to generate lots of random IO, or they block on something. Deleting a few 1000 rows will not result in a minute of full CPU usage in any way. –  usr Jul 21 at 12:52

2 Answers 2

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.

share|improve this answer

A simple delete operation of huge records does not physically deletes records unless you use TABLOCK option in heap deletion which deleted records and dealloctaed pages so that space can be used. In normal case pages are marked as deallocated in PFS( Page free space) pages and futher a background task called as ghost cleanup physically removes the record( actually slot is removed). This is done to give prformance boost so that if you rollback the transaction SQL Server would not have to allocate pages deleted it just changes allocation status of pages in PFS pages. This is the reason why delete on columns with interger type data is quick

Coming to delete of records from Image type data column, please note that IMAGE tye data is not supported instead you should use nvarchar(max). Delete requires pages to be moved from disk to memory( if it is not present in memory) and since image data types are big obviously memory requirement would be big and its quite possible SQL server would not find continuous memory location to hold such pages and will wait till its available so delaying the process. Is there a index on column which is specified in where clause. I am assuming you have proper index in place otherwise the point of discussion is useless.

You should look at wait types during delete operation below DMV will help you getting wait type Sys.dm_os_waiting_tasks look at the query in example and find out during delete what is slowing it down.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.