Right now we try to purge data but we find the process is very slow.
Our query is :
@NoOfMonthRetention INT,@BusinessDate DATETIME
DELETE tk FROM task tk
er FROM user_activity_log
WHERE action_time < DATEADD(MM, -@NoOfMonthRetention, @BusinessDate) )
The execution plan of this is (see attached)
what I see is it delete something where xxxx in a result set from a select statement, it should be a expensive operation, how to solve it?
use more temp table as we use less join? but more temp will create more loading on tempDB, right? This is another problem.