Slow DELETEs of LOB data in SQL Server

Question

I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE statements to perform reasonably well on LOB data. Alternatively, if there is an acknowledgement from Microsoft of the problem -- something like "we have fixed this with SQL server version x", or even "we see this performs poorly, but it is not a priority" -- that would work also.

This is running on Microsoft SQL Server 2012 (SP3). The below is substantially my actual table and code, just slightly simplified:

CREATE TABLE [LOG_VALUE](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [VALUE] [varchar](max) NOT NULL,
 [CHECKSUM] [int] NOT NULL,
 [VALUE_LEN] [int] NOT NULL,
 CONSTRAINT [PK_LOG_REQUEST] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The deletes in question are done with this:

WHILE (@@ROWCOUNT > 0)
  DELETE TOP (100) [LOG_VALUE]
  OUTPUT DELETED.[VALUE_LEN] INTO @DELETED_ROWS
  WHERE [ID] IN (SELECT [ID] FROM @DELETE_IDS);

The basic stored procedure process is:

1. remove the values that reference the rows to be purged
2. select the [ID] values from the LOB table where NOT EXISTS in the above log table, into table variable @DELETE_IDS
3. delete top 100 rows at a time (to reduce/prevent contention/locking)

It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted. Support: 1) when I insert 12 thousand rows into the table, it can be nearly instantaneous 2) retrieving 12 thousand rows is nearly instantaneous 3) once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

The most related question I could find is at: https://serverfault.com/questions/241893/delete-performance-for-lob-data-in-sql-server from 2011, but sadly has no satisfactory answer. I'm just hoping that asking this on dba.stackexchange.com will find a more knowledgeable audience :). That question's author definitely knew more about this than me, but as I read it the central points are:

1. when deleting rows, SQL server normally just marks them as deleted, and they are actually cleaned up by a "Ghost Cleanup Task" later on
2. when deleting LOB data, eXclusive locks are placed on all of the LOB data pages during the delete, and the pages are deallocated
3. when the pages are not in the buffer cache, these eXclusive locks wait for the pages to be loaded into memory
4. these pages being deallocated happens "up-front", not in a cleanup task. This means that the delete operation always waits on the data to be loaded before the statement finishes, unlocking the relevant rows

It seems like this deallocation should happen in a clean-up task, and not up-front. And since the LOB data is not referenced in the predicate (or OUTPUT), there should be no need to load it into the cache at all.

More information which may be relevant, but I don't think it is:

• the values are often over 100KB, but vary significantly in size
• the other table that points to the [LOG_VALUE].[ID] is not enforced with Referential Integrity
• the @DELETED_ROWS table is accumulated to note, at the end of the stored procedure, how many rows and bytes were deleted
• there are currently 1.1 million rows, and by the value of the IDENTITY, there were only ever 1.6 million
• There are no triggers
• Currently, Ghost Record Count = 0
• READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are both OFF.

The whole database (and others on the same server) perform as I would expect in all cases except deleting/updating LOB data.

Answer 1

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds.

This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types.

It definitely appears that the LOB data must be loaded into memory on the server in order to be deleted.

As Paul Randal is reported to have said (update 2, in the question you linked to), SQL Server must traverse the LOB tree in order to locate the pages that must be removed. To do this, SQL Server needs to bring these LOB tree pages into memory. There really is no way around this; the information is just not available in any other way.

It seems like this deallocation should happen in a clean-up task, and not up-front.

The way this works is not very well documented.

In my tests, SQL Server deletes the LOB data immediately if there is no special reason to keep the row around. This seems to be an optimization: after all, if we are already touching the LOB data, we might as well delete it while we are there. Deferring it to ghost cleanup (GC) would only add overhead, since GC would also need to traverse the chain. In addition, logging is reduced, since SQL Server only has to record the deallocation, not the complete LOB content.

The LOB delete operation is deferred to GC if (for example) there is a trigger on the table, or if a row-versioning isolation level is enabled. Both these features use row versions, which must be maintained on the LOB rows. In these cases, the LOB delete is fully logged. When GC runs later on, it performs the deallocation of the LOB pages. GC can be temporarily disabled with (documented) global trace flag 661, to see these ghosted LOB records if you want to test it yourself.

Given all that, it is hard to see how (fully) logging the delete and deferring a repeat of much of the same work already done to the GC would help you. It is likely faster to log less and remove the LOB data during the delete.

---

There's not enough in the question to speculate much about the precise cause of your poor delete performance, beyond the possibility of an overwhelmed/underspecified storage system, but I can say that in general it is best to:

1. Avoid accessing LOB data under read uncommitted isolation
2. Avoid having triggers on tables with LOB data when performing deletes
3. Be aware of the side-effects of row versioning
4. Ensure the storage subsystem is adequate for logging and general I/O
5. Run the latest build of SQL Server (currently 11.00.6523 for SQL Server 2012)

For more information about point 1, see Performance bug: NOLOCK scans involving off-row LOB data by Paul Randal. For more on points 2 and 3, see Deletes that Split Pages and Forwarded Ghosts (by me).

It could equally be that the simplified example misses a crucial detail, or the table variables produce an inefficient execution plan, or your table has billions of ghost LOB records already. A detailed analysis would probably require access to the system itself.