For example, it returns WAITFOR DELAY '00:00:05' for query like: CREATE PROCEDURE spGetChangeNotifications 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.Īn answer of Brent Ozar might work, but it returns only active command text by default. When I removed that (replaced the call by the inline contents of the function, a CASE statement), it ran fine again. So I started stripping the SP to less and less contents until I encountered a UDF call to another database. Creating a new stored procedure with same contents did not solve the problem either. Using above hints, I found the SP execution plan and it showed nothing out of the ordinary (to me, at least). Parameters passed to SP and variables declared in window are the same.Stored procedure runs for over 6 hours without response.No locks, according to Activity Monitor SPID is doing SELECT.Hanging piece of query runs fine and quick (3 secs) in normal query window (hanging piece identified with sp_whoisactive).I failed to reproduce the problem before, but today I chanced upon another stored procedure with the same problem. I still haven't found the answer, but I will post the progress here. I still don't know what was wrong in this particular case but I came out of this battle seasoned and much better armed. WITH RECOMPILE, OPTION(RECOMPILE) for subqueries within the SP, and OPTION (OPTIMIZE FOR = 1)) in order to attack parameter sniffing. I found things wrong with the query plan thanks to the hint by Martin Smith. I used Adam Machanic's routine to find out which subquery was hanging. UPDATE: I'm guessing it had to do with parameter sniffing. Setting LOCK_TIMEOUT to 1 second does not help.Dropping and recreating the SP doesn't help. Running sp_recompile on the SP doesn't help.Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT.SP takes > 2.5 minutes until I cancel it.Copying the code into a query window yields the query result in 1 second.We have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it.
0 Comments
Leave a Reply. |