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.
1) sp_whoisactive can actually help you find the PFS/SGAM latching issue I mentioned if it is occuring.
2) Not only are you populating a temp table here, you are also doing a SORT for the ROW_NUMBER operation. Double hit. Could be HASHING on the joins for more tempdb hits.
3) ORs are horrible for the optimization process. NOT EXISTS too. Ugly stuff there.
4) Note that SSMS has some different SET values for ANSI crap that can cause significantly different plans from ADO.NET or other connections. You can view these values down in the guts of one of the query execution DMVs. I would actually get the plans for both executions and examine for differences. It is possible you could use a plan guide since there are no parameters to force the optimal plan for every execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service