Stored procedure hangs seemingly without explanation

Run Adam Machanic's excellent sp_WhoIsActive stored proc while your query is running. It'll give you the wait information - meaning, what the stored proc is waiting on - plus things like the execution plan:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

If you want the outer command (like a calling stored procedure's full text), use the @get_outer_command = 1 parameter as well.

I think that this is related to parameter sniffing and the need to parameterize your input params to local params within the SP. Adding with recompile causes the execution plan to be recreated and eliminates much of the benefits of having a SP. We were using With Recompile on many reports in an attempt to eliminate this hanging issue and it occassionally resulted in hanging SP's that may have been related to other locks and/or transactions accessing the same tables simultaneously. See this link for more details Parameter Sniffing (or Spoofing) in SQL Server and change your SP's to the following to fix this:

CREATE PROCEDURE [dbo].[SPNAME] @p1 int, @p2 int AS

DECLARE @localp1 int, @localp2 int

SET @localp1=@p1 SET @localp2=@p2

First thing First.

Please check if there are any uncommitted transactions. A begin transaction without "COMMIT TRANSACTION"