sqldba_icon (6/6/2011)
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.
I had something like this come up just yesterday that I fixed. It was very strange. The SProc would hang sometimes. I would copy/paste the code from the stored procedure into SSMS and run it in steps and it would run just fine - each step ran in a second or two. This was SQL 2000 so I don't know if this is the same situation as yours.
The logic had a similar LEFT JOIN and a check for SomeColumn IS NULL in the WHERE clause - i.e. finding unmatched records from one table to the other. I changed the LEFT JOIN to a NOT EXISTS in the WHERE clause and it would still hang. Very mysterious. I was under a lot of pressure to just get the thing running so I couldn't fully investigate the phenomenon.
I changed the initial insert into the temp table to just dump everything on the matching criteria. Then I did an INNER JOIN on a DELETE to get rid of the ones that were matched when I wanted only the unmatched records. The procedure ran just fine then - no hang.
I've found that sometimes doing things in steps - divide and conquer - can work better than trying to do it all at once.
I'm still going back to the original LEFT JOIN/WHERE SomeColumn IS NULL and try to figure out why the heck it would hang like that sometimes.
Give it a try and see if it works for you.
Todd Fifield