Slow DELETEs of LOB data in SQL Server
...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:


Avoid accessing LOB data under read uncommitted isolation
Avoid having triggers on tables with LOB data when performing deletes
Be aware of the side-effects of row versioning
Ensure the storage subsystem is adequate for logging and general I/O
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.