Stored procedure hangs seemingly without explanati

问题:

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!

回答1:

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/

回答2:

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

回答3:

When we add new data sometimes the execution plan becomes invalid or out of date then the stored procedure starts going into this limbo phase. Run the following commands on your database

DBCC DROPCLEANBUFFERS 

DBCC FREEPROCCACHE

It will flush the cache memory and rebuild the execution plan next time you will run the stored proc.

msdn.microsoft.com

回答4:

Thanks for all comments.

I still haven't found the answer, but I will post the progress here.

I failed to reproduce the problem before, but today I chanced upon another stored procedure with the same problem. Again the same symptoms appeared:

• Hanging piece of query runs fine and quick (3 secs) in normal query window (hanging piece identified with sp_whoisactive)
• No locks, according to Activity Monitor SPID is doing SELECT
• Stored procedure runs for over 6 hours without response
• Parameters passed to SP and variables declared in window are the same

Using above hints, I found the SP execution plan and it showed nothing out of the ordinary (to me, at least). Creating a new stored procedure with same contents did not solve the problem either. So I started stripping the SP to less and less contents until I encountered a UDF call to another database. When I removed that (replaced the call by the inline contents of the function, a CASE statement), it ran fine again.

So this COULD have been the problem, but I am not very certain, as last time the problem disappeared by itself and I also changed a lot of other things while stripping this SP.

回答5:

I think I had the same problem. I removed my parameters from the subqueries. It ran fine after that. Not sure if this is possible in your script but that is what solved it for me.

回答6:

First thing First.

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