Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.

sELECT Row_number() OVER ( PARTITION BY c.rid ORDER BY HEID ASC, CovStat DESC ) AS ident ,

t.rid ,

t.h_cdr AS cdr ,

c.id AS MCDI ,

t.id AS TempCoverageId ,

c.FPC AS C_FPC ,

pp_a.PYC AS C_PYC ,

t.h_cdr AS C_cdr

INTO #TempTable_2

FROM TMPCOV t

INNER JOIN COV c ON t.rid = c.rid

LEFT OUTER JOIN PPYS pp_a ON pp_a.FPC = c.FPC

LEFT OUTER JOIN PPYS pp_h ON pp_h.FPC = t.h_FPC

LEFT OUTER JOIN TMPCOV tempcovjoin ON c.id = tempcovjoin.MCDI

WHERE t.h_cdr = 2

AND tempcovjoin.MCDI IS NULL

AND ( ( t.h_SubC = c.SubC

AND pp_a.PYC IS NOT NULL

AND pp_a.PYC = pp_h.PYC

)

OR ( c.FPC = t.h_FPC )

OR ( pp_h.ptype = pp_a.ptype

AND pp_a.ptype = 'SLF'

)

)

AND NOT EXISTS ( SELECT *

FROM TMPCOV

WHERE MCDI = c.id )

Also when it hangs it is blocking other processes which use Tempdb. I am leaning more towards something related to tempdb. The indexes look fine from the source tables.