Make a temp table, switch it in and out, and copy the last 30 days data into it.
#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
In your off hours, drop the old table
DROP TABLE NOTIFICATION_OLD;
Here are the Advantages to doing DELETEs like this
NOTIFICATION is emptied fast by means switching in an empty table.NOTIFICATION is immediately available for new INSERTs- The remaining 30 days are added back into
NOTIFICATION while new INSERTs can take place.
- Dropping the old version of
NOTIFICATION does not interfere with new INSERTs
- NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)
Give it a Try !!!
...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.
Best Answer
Few Quick Possibilities:
Bad Statistics. Seems unlikely on a table under millions of rows, but technically possible. Try running UPDATE STATISTICS . If this does work look into why your statistics are getting out of whack and what you can do to mitigate the problem.
Lack of a good Clustered Index. This can be related to #1, if your CI is bad it can slow down UPDATEs/DELETEs as the CI needs to be searched and adjusted during such changes, but an INSERT may just be adding to the end of the CI.
Locks are being held too long over some sections of the table via other processes. Other transactions may be holding a lock on the table for excessive amounts of time, this may also be visible as deadlocking if you have the appropriate flags turned on for the SQL Logs.
Manually grabbing the query plan and taking a look at that may also help you figure out what is going on.