Why would a procedure sometimes hang executing SELECT...INTO #Temp2 FROM #Temp1 JOIN LocalTable...?
The bad plan XML showplan provided shows that two tables in the query have an indicated zero rows (other objects in the plan are fine):



captured from SQL Sentry Plan Explorer's Plan Tree view

This may be due to a problem automatically updating statistics, or perhaps due to some quirk of running under snapshot isolation. There really isn't enough information yet to say. In any case, this is something you should investigate. 

If the table &amp; index statistics really are missing (or empty), manually building these will will almost certainly resolve the problem you are seeing.

Given incorrect information, the query optimizer generates a plan featuring nested loop joins:



This plan has the potential to execute for a long time when the runtime row counts greatly exceed those planned for by the QO. The query isn't blocked; it is simply continuing to execute using a flawed strategy.

The 'good plan', generated when reasonable statistics are available, produces an execution plan that uses hash joins and parallelism: