Stored procedure hangs seemingly without explanation

Question

we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it.

Observations:

• Copying the code into a query window yields the query result in 1 second
• SP takes > 2.5 minutes until I cancel it
• Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT.
• Running sp_recompile on the SP doesn't help
• Dropping and recreating the SP doesn't help
• Setting LOCK_TIMEOUT to 1 second does not help

What else can be going on?

---

UPDATE: I'm guessing it had to do with parameter sniffing. I used Adam Machanic's routine to find out which subquery was hanging. I found things wrong with the query plan thanks to the hint by Martin Smith. I learned about EXEC ... WITH RECOMPILE, OPTION(RECOMPILE) for subqueries within the SP, and OPTION (OPTIMIZE FOR (@parameter = 1)) in order to attack parameter sniffing. I still don't know what was wrong in this particular case but I came out of this battle seasoned and much better armed. I know what to do next time. So here's the points!

Answer 1

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

Answer 2

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.