Que outras opções temos para ter?

Parece que seu principal problema é que a estimativa de cardinalidade ruim está fazendo com que o compilador reordene as junções. Isso parece estar acontecendo mais quando você usa OPTION (RECOMPILE), porque o servidor está fazendo estimativas diferentes.

Você deve ser capaz de simplificar sua consulta atual o suficiente para que o compilador ache mais fácil

Sem ver seu esquema, é difícil dizer exatamente, mas parece que você pode inverter a EXISTSlógica para NOT EXISTS.

• Seu original diz "garantir que a data máxima no grupo seja menor que a minha data"
• Você realmente quer que não haja linhas no grupo que sejam menores que a data, então você pode dizer "garantir que não haja linhas com uma data maior que a minha data".
• Agora você não precisa de um HAVINGpara encontrar a data máxima.

Sua consulta existente tem vários problemas:

• a b csão aliases bobos, considere usar outros mais significativos.
• Use []para citar nomes de coluna, não ''. Não cite a menos que você precise.
• Por que está NOLOCKespalhado em todos os lugares como confete, o que você espera alcançar com isso? Tem implicações muito sérias para a integridade dos dados. Considere usar TABLOCKdicas em vez disso, ou SNAPSHOTisolamento.
• As tabelas de junção à esquerda estão sendo filtradas para que você tenha uma junção interna implícita, você deve usar apenas associações internas.
• Uma EXISTSsubconsulta não precisa selecionar nada, na verdade ela é ignorada. Você pode simplesmente fazer EXISTS (SELECT 1.
• Por que três subconsultas, parece que você deve conseguir fazer isso em uma.
• where b.[MerchantLogId] = a.[MerchantLogId] group by b.[MerchantLogId]não faz sentido: você sempre terá exatamente um grupo, então não há necessidade de group by.
• Parece que você não precisa NTFM_MerchantLognas subconsultas, porque isso é unido pela chave primária.
• Não use aritmética em datas, não funciona bem. Em vez disso, useDATEADD

Também parece que você pode combinar as três subconsultas em uma, juntando todas as tabelas e usando OR(para isso, você ainda precisará de algumas associações à esquerda).

Espero que, depois de fazer todas essas melhorias, você veja um desempenho melhor

SELECT
  'NTFM_MerchantLog' AS [Process Master],
  COUNT(DISTINCT ml.MerchantLogId) AS [Registers Count]
FROM
  dbo.NTFM_MerchantLog ml
  JOIN dbo.v_ntfm_merchantlogstatus mls on mls.MerchantLogStatusId = ml.StatusId
WHERE
  ml.CreateDate < @Today - @lv_2484deletionCycle
  AND mls.Status IN ('Closed', 'Expired', 'Reconciled') 

  AND NOT EXISTS (SELECT 1
    FROM dbo.ntfm_merchantlogtransactions mlt
    JOIN dbo.accounttransaction at1 ON at1.accountTransactionId = mlt.accountTransactionId

    LEFT JOIN (
        dbo.ntfm_merchantlogtransactions mlt2
        JOIN dbo.ntfm_merchantlog ml2 ON ml2.MerchantLogId = mlt2.MerchantLogId

        LEFT JOIN (
            dbo.ntfm_merchantlogtransactions mlt3
            JOIN dbo.accounttransaction at2 on at2.accountTransactionId = mlt3.accountTransactionId
        ) ON mlt3.MerchantLogId = ml2.MerchantLogId
    ) ON mlt2.accountTransactionId = at1.accountTransactionId

     WHERE
       mlt.MerchantLogId = ml.MerchantLogId
     AND (
           at1.postingdt  >= DATEADD(day, -@lv_2484deletionCycle, @Today)
        OR ml2.createdate >= DATEADD(day, -@lv_2484deletionCycle, @Today)
        OR at2.postingdt  >= DATEADD(day, -@lv_2484deletionCycle, @Today)
     )
  );