Tune SQL Server query

Question

My goal is to purge data in chunks and was thinking to loop through ID_COl value after putting it into #temptable .

    DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
DELETE GP
  FROM dbo.Table_A N
 INNER JOIN dbo.Table_BL GP
    ON N.NOTIF_ID = GP.NOTIF_ID
 WHERE N.FCT_AUD_IND = 'Y'
   AND N.CRE_TMST    < @PD
   AND EXISTS (   SELECT 1 ID_COl
                    FROM dbo.Table_A AS N1
                   WHERE N1.NOTIF_APPL_ID        = N.NOTIF_APPL_ID
                     AND N1.SOR_NOTIF_APPL_GP_ID = N.SOR_NOTIF_APPL_GP_ID
                     AND N1.NOTIF_DLT_IND  = 'Y');

Answer 1

You are forcing SQL Server to do a nested loop to find the records in Table_A, but maybe SQL Server would find a better way when you let him decide and JOIN instead:

DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
WITH N1 AS (
  SELECT DISTINCT NOTIF_APPL_ID, SOR_NOTIF_APPL_GP_ID
  FROM dbo.Table_A
  WHERE NOTIF_DLT_IND  = 'Y'
)
DELETE GP
FROM dbo.Table_A N
  INNER JOIN N1 ON N.NOTIF_APPL_ID = N1.NOTIF_APPL_ID 
               AND N.SOR_NOTIF_APPL_GP_ID = N1.SOR_NOTIF_APPL_GP_ID
  INNER JOIN dbo.Table_BL GP ON N.NOTIF_ID = GP.NOTIF_ID
WHERE N.FCT_AUD_IND = 'Y'
   AND N.CRE_TMST < @PD

Also, defining indexes coud be helpful.