Using "statistics io on", it seems that the second pass at selecting from a #temp table is hanging the query. Is this the result of too many concurrent locks in tempdb? I'm not the sysadmin so I'm just guessing here. I should also note that I've created the #temp table outside of the dynamic SQL block which is calling this outer table. The first callshows Table '#countries__________________________________________________________________________________________________________000000000277'. Scan count 8, logical reads 8251, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
but the second pass of and c.country in (select country from #countries)
is not showing. There are five tables that the first pass draws on, and the second pass shows four, the fifth being the #temp table in question. (This is production code).
tempdb locks hanging query?
Posted - 2010-11-01 : 13:53:03
