SQL Server 2005: Why would a delete from a temp table hang forever?

Question

DELETE FROM #tItem_ID
WHERE #tItem_ID.Item_ID NOT IN (SELECT DISTINCT Item_ID
                                  FROM Item_Keyword 
                                  JOIN Keyword ON Item_Keyword.Keyword_ID = Keyword.Record_ID
                                 WHERE Keyword LIKE @tmpKW)

The Keyword is %macaroni%. Both temp tables are 3300 items long. The inner select executes in under a second. All strings are nvarchar(249). All IDs are int.

Any ideas? I executed it (it's in a stored proc) for over 12 minutes without it finishing.

Answer 1

The classic case where SQL Server "hangs" is when you open a transaction but don't commit or rollback. Don't get so wrapped up in your actual delete (unless you are working with a truly huge dataset) that you fail to consider this possibility.

Answer 2

Sounds like you might want to read up on deadlocking...

** Ignore my answer - I'm leaving it up so that Dr. Zim's comment is preserved, but it is incorrect **

Answer 3

Anything that has a lock on the table could prevent the query from proceeding. Do you have any other sessions open in SSMS where you have done something to one of the tables?

You can also use the system sproc sp_who2 to see if there are any locks open. look in the column BlkBy and see if anything is hanging on a lock held by another process.

Answer 4

When i run into issues like this i fire up SQL Heartbeat and it can show me what is causing the conflict. This also shows deadlocks related to transactions that were not closed correctly as Mark states above.