August 2, 2011 at 1:04 pm
I've got a situation in a mixed (OLAP/OLTP) environment where our live system constantly read/writes/updates data against records that are real-time and/or within the past 2-3 months.
Because of the sheer volume of some of our tables (and primarily because the application doesn't need data beyond 6 months) I've built some pruning routines that extracts data from several specific tables and inserts it into an archiving DB. It's fairly simple code (snippet):
DELETE TOP (@BatchSize) FROM dbo.MyTable
WHERE PostTime < @ArchiveDate
OPTION (MAXDOP 2) -->> Added because of performance gains
This code works flawlessly when we take our environment down for maintenance and we see no locking/blocking. The above code runs in batches of 25,000 records and then performs a CHECKPOINT ever 2 iterations.
Happy Boss.
HOWEVER, we're wanting to avoid taking our application down (which requires a maintenance window) so in attempting to run it while the app is running, I've seen some locking/blocking going on...
While the DELETE operation above was running it held a PAGEIOLATCH, and some of the application code was running and UPDATE (code below), which caused and objectlock (which I assume escalated to a table lock), then blocked another SELECT with a keylock...
(@P1 varchar(max),@P2 varchar(max),@P3 decimal(19, 0))
Update dbo.MyTable set PostingRef=@P1,ARTxnType=@P2 where TranId=@P3
Being no expert on lock escalation...I guess my question is should I add a query hint to the update statement to hold only a ROWLOCK and disable lock escalation for that table while these pruning scripts run, or am I simply SOL???
I would think that deleting data from a table that's 8 months old, wouldn't interfere with the data currently being queried/updated by the application.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 2, 2011 at 1:24 pm
Make sure the where clause if indexed.
Put it in smaller batches (untill that wait stops).
Put a waitfor x seconds so the app can recover its breath.
If possible, put the archive db on its on set of disks so that the reads / writes are not on the same spindles.
August 2, 2011 at 1:30 pm
Thanks so much for these tips!
I've already made sure the index was there but about putting a WAITFOR in there or placing them on separate spindles (makes perfect sense now that you mentioned it!).
Regarding the UPDATE statement, is there any tweaking which could be employed there or is it just something that "is"?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 2, 2011 at 1:34 pm
That's where the smaller batch comes in. If the whole table is locked, the only improvement is to shorten that lock.
Also since this is archiving it doesn't really matter wether it takes 15 minutes or 17 (as long as prod is still online). So that's why you throttle that one back a little bit and make room for the real important stuff.
You might want to make sure that the stats are updated after this is done running. It very likely to auto-update but you never know.
August 2, 2011 at 1:36 pm
... and assuming tranid is the PK or already index, then no there's nothing you can do about it.
No hint can bypass the delete table lock for the update operation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply