How to delete many records with minimal locks and IO?
I have a larg db (160GB) that used both as OLTP and DW. there are several large tables that normally experience 10k-30k transactions per hour. most of them are INSERT and part are UPDATE. Tables have cluster PK of 5-8 fields with datetime one of them. tables
have several indexes.
normally we dont delete records. BUT, end-user may quit service once in a while and in this case we have a job to delete all his raw data.
we have a job that tries to do it in small chunks to reduce impact on other processing jobs, but actually it slows down all other important transactions with long locks.
I would like to know what is the best way to delete records from a large table with minimum impact on other transactions (insert and update).
The delete task is a background job that is executed during night and may take many hours.
here is my code:
-- @itemID = -1 for all items
View Complete Post