This question is related to this forum thread.
Running SQL Server 2008 Developer Edition on my workstation and an Enterprise Edition two-node virtual machine cluster where I refer to "alpha cluster".
The time it takes to delete rows with a varbinary(max) column is directly related to the length of the data in that column. That may sound intuitive at first, but after investigation, it clashes with my understanding of how SQL Server actually deletes rows in general and deals with this kind of data.
The problem stems from a delete timeout (> 30 seconds) issue we are seeing in our .NET web application, but I have simplified it for the sake of this discussion.
When a record is deleted, SQL Server marks it as a ghost to be cleaned up by a Ghost Cleanup Task at a later time after the transaction commits (see Paul Randal's blog). In a test deleting three rows with 16 KB, 4 MB, and 50 MB data in a varbinary(max) column, respectively, I see this happening on the page with the in-row portion of the data, as well as in the transaction log.
What seems odd to me is that X locks are placed on all of the LOB data pages during the delete, and the pages are deallocated in the PFS. I see this in the transaction log, as well as with
sp_lock and the results of the
dm_db_index_operational_stats DMV (
This creates an I/O bottleneck on my workstation and our alpha cluster if those pages are not already in the buffer cache. In fact, the
page_io_latch_wait_in_ms from the same DMV is practically the entire duration of the delete, and the
page_io_latch_wait_count corresponds with the number of locked pages. For the 50 MB file on my workstation, this translates to over 3 seconds when starting with an empty buffer cache (
dbcc dropcleanbuffers), and I have no doubt it would be longer for heavy fragmentation and under load.
I tried to make sure that it wasn't just allocating space in the cache taking up that time. I read in 2 GB of data from other rows before executing the delete instead of the
checkpoint method, which is more than is allotted to the SQL Server process. Not sure if that is a valid test or not, since I don't know how SQL Server shuffles the data around. I assumed it would always push out the old in favor of the new.
Further, it doesn't even modify the pages. This I can see with
dm_os_buffer_descriptors. The pages are clean after the delete, while the number of modified pages is less than 20 for all three small, medium, and large deletes. I also compared output of
DBCC PAGE for a sampling of the looked up pages, and there were no changes (only the
ALLOCATED bit was removed from PFS). It just deallocates them.
To further prove that the page lookups/deallocations are causing the issue, I tried the same test using a filestream column instead of vanilla varbinary(max). The deletes were constant time, regardless of LOB size.
So, first my academic questions:
- Why does SQL Server need to lookup all of the LOB data pages in order to X lock them? Is that just a detail of how locks are represented in memory (stored with the page somehow)? This makes the I/O impact depend strongly on data size if not completely cached.
- Why the X locks at all, just to deallocate them? Isn't it sufficient to lock just the index leaf with the in-row portion, since the deallocation does not need to modify the pages themselves? Is there some other way to get at the LOB data that the lock protects against?
- Why deallocate the pages up front at all, given that there is already a background task dedicated to this type of work?
And maybe more important, my practical question:
- Is there any way to make deletes operate differently? My goal is constant time deletes regardless of size, similar to filestream, where any cleanup happens in the background after the fact. Is it a configuration thing? Am I storing things strangely?
Here is how to reproduce the described test (executed through SSMS query window):
CREATE TABLE [T] ( [ID] [uniqueidentifier] NOT NULL PRIMARY KEY, [Data] [varbinary](max) NULL ) DECLARE @SmallID uniqueidentifier DECLARE @MediumID uniqueidentifier DECLARE @LargeID uniqueidentifier SELECT @SmallID = NEWID(), @MediumID = NEWID(), @LargeID = NEWID() -- May want to keep these IDs somewhere so you can use them in the deletes without var declaration INSERT INTO [T] VALUES (@SmallID, CAST(REPLICATE(CAST('a' AS varchar(max)), 16 * 1024) AS varbinary(max))) INSERT INTO [T] VALUES (@MediumID, CAST(REPLICATE(CAST('a' AS varchar(max)), 4 * 1024 * 1024) AS varbinary(max))) INSERT INTO [T] VALUES (@LargeID, CAST(REPLICATE(CAST('a' AS varchar(max)), 50 * 1024 * 1024) AS varbinary(max))) -- Do this before test CHECKPOINT DBCC DROPCLEANBUFFERS BEGIN TRAN -- Do one of these deletes to measure results or profile DELETE FROM [T] WHERE ID = @SmallID DELETE FROM [T] WHERE ID = @MediumID DELETE FROM [T] WHERE ID = @LargeID -- Do this after test ROLLBACK
Here are some results from profiling the deletes on my workstation:
| Column Type | Delete Size | Duration (ms) | Reads | Writes | CPU | -------------------------------------------------------------------- | VarBinary | 16 KB | 40 | 13 | 2 | 0 | | VarBinary | 4 MB | 952 | 2318 | 2 | 0 | | VarBinary | 50 MB | 2976 | 28594 | 1 | 62 | -------------------------------------------------------------------- | FileStream | 16 KB | 1 | 12 | 1 | 0 | | FileStream | 4 MB | 0 | 9 | 0 | 0 | | FileStream | 50 MB | 1 | 9 | 0 | 0 |
We can't necessarily just use filestream instead because:
- Our data size distribution doesn't warrant it.
- In practice, we add data in many chunks, and filestream doesn't support partial updates. We would need to design around this.
Tested a theory that the data is being written to the transaction log as part of the delete, and this does not seem to be the case. Am I testing for this incorrectly? See below.
SELECT MAX([Current LSN]) FROM fn_dblog(NULL, NULL) --0000002f:000001d9:0001 BEGIN TRAN DELETE FROM [T] WHERE ID = @ID SELECT SUM( DATALENGTH([RowLog Contents 0]) + DATALENGTH([RowLog Contents 1]) + DATALENGTH([RowLog Contents 3]) + DATALENGTH([RowLog Contents 4]) ) [RowLog Contents Total], SUM( DATALENGTH([Log Record]) ) [Log Record Total] FROM fn_dblog(NULL, NULL) WHERE [Current LSN] > '0000002f:000001d9:0001'
For a file over 5 MB in size, this returned
1651 | 171860.
Further, I would expect the pages themselves to be dirty if data were written to the log. Only the deallocations seem to be logged, which matches what is dirty after the delete.
I did get a response back from Paul Randal. He affirmed the fact that it has to read all of the pages in order to traverse the tree and find which pages to deallocate, and stated that there is no other way to look up which pages. This is a half answer to 1 & 2 (though doesn't explain the need for locks on out-of-row data, but that is small potatoes).
Question 3 is still open: Why deallocate the pages up front if there is already a background task to do cleanup for deletes?
And of course, the all important question: Is there a way to directly mitigate (i.e. not work around) this size-dependent delete behavior? I would think this would be a more common issue, unless we're really the only ones storing and deleting 50 MB rows in SQL Server? Does everyone else out there work around this with some form of a garbage collection job?