Sql-server – Avoid PLE drops while performing DELETE operations

sql serversql-server-2012

If my quesion is not specific enough please tell what details are missing.
We are runing daily archive operations on the whole database where we keep data from the last 30 days. We are deleting data in 100 records batches. For one particular table Which has the biggest(in terms of size) records it take around 3-5 hours to delete all 2 millions of old records even just after index maintenance operations. Also deletions of that particular table causes PLE to drop. My question is how to accelerate that proces and avoid PLE drops?

Best Answer

For one particular table Which has the biggest(in terms of size) records it take around 3-5 hours to delete all 2 millions of old records

we need more detail like table structure along with data type and index details.

Try opposite method.

Keep data of last 30 days and truncate old table.

For example (Step 1),

declare @CurDate datetime=getdate()

select * 
into NewTable
from ExistingTable 
where datecol >= @CurDate and datecol <= dateadd(day,-30,@CurDate)
  1. Rename old table to anything unique or drop table as per requirement. In fact drop table only after testing and approval from other.
  2. Rename new table to existing table
Related Question