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:
- remove the values that reference the rows to be purged
- select the [ID] values from the LOB table where NOT EXISTS in the above log table, into table variable @DELETE_IDS
- 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:
- when deleting rows, SQL server normally just marks them as deleted, and they are actually cleaned up by a "Ghost Cleanup Task" later on
- when deleting LOB data, eXclusive locks are placed on all of the LOB data pages during the delete, and the pages are deallocated
- when the pages are not in the buffer cache, these eXclusive locks wait for the pages to be loaded into memory
- 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
ALLOW_SNAPSHOT_ISOLATIONare both OFF.
The whole database (and others on the same server) perform as I would expect in all cases except deleting/updating LOB data.