Why would a procedure sometimes hang executing SELECT...INTO #Temp2 FROM #Temp1 JOIN LocalTable...?

We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, reads, and writes indefinitely until killed. The pattern is:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE #RemoteTemp...;
INSERT INTO #RemoteTemp EXEC MyLinkedServer.DbName.SchemaName.ProcName;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT...INTO #MixedTemp FROM #RemoteTemp JOIN LocalTable...;

We then make use of #MixedTemp. However, that last statement is the one that sometimes runs in less than one second and sometimes hangs even though the row counts are the same at every execution. What might cause that?

Additional info:

• 1117 and 1118 are enabled.

• 8 tempdb files for this 8 core instance.

• No tempdb autogrowth is occurring.

During the hang:

• Sch-S locks on the local tables.

-
On the temp table being read:

Lock resource_type="HOBT.BULK_OPERATION" request_mode="S"
request_status="GRANT" request_count="1" Lock resource_type="OBJECT"
request_mode="X" request_status="GRANT" request_count="1"

-
On the temp table being inserted into:

Lock resource_type="OBJECT"
 request_mode="X" request_status="GRANT" request_count="1"

From sp_lock (6 and 7 are databases being joined to the temp table, 2 is tempdb):

```
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
96 6 0 0 DB S GRANT
96 7 0 0 DB S GRANT
96 6 702625546 0 TAB Sch-S GRANT
96 2 -1219105587 0 HBT [BULK_OPERATION] S GRANT
96 7 98099390 0 TAB Sch-S GRANT
96 2

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 & 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:

StackExchange Database Administrators Q#130933, answer score: 6