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.

Answer 3

First thing First.

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

An answer of Brent Ozar might work, but it returns only active command text by default. For example, it returns WAITFOR DELAY '00:00:05' for query like:

CREATE PROCEDURE spGetChangeNotifications
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @actionType TINYINT;
    WHILE @actionType IS NULL
    BEGIN
        WAITFOR DELAY '00:00:05';
        SELECT TOP 1
            @actionType = [ActionType]
        FROM
            TableChangeNotifications;
    END;
    SELECT
        TOP 1000 [RecordID], [Component], [TableName], [ActionType], [Key1], [Key2], [Key3]
    FROM
        TableChangeNotifications;
END;

How it looks like:

Thus, check the parameter @get_outer_command as described here.

Also, try this one instead(slightly modified procedure from MS Docs):

DECLARE
    @sessions TABLE
(
    [SPID]        INT,
    STATUS        VARCHAR(MAX),
    [Login]       VARCHAR(MAX),
    [HostName]    VARCHAR(MAX),
    [BlkBy]       VARCHAR(MAX),
    [DBName]      VARCHAR(MAX),
    [Command]     VARCHAR(MAX),
    [CPUTime]     INT,
    [DiskIO]      INT,
    [LastBatch]   VARCHAR(MAX),
    [ProgramName] VARCHAR(MAX),
    [SPID_1]      INT,
    [REQUESTID]   INT
);

INSERT INTO @sessions
EXEC sp_who2;

SELECT
    [req].[session_id],
    [A].[Login] AS 'login',
    [A].[HostName] AS 'hostname',
    [req].[start_time],
    [cpu_time] AS 'cpu_time_ms',
    OBJECT_NAME([st].[objectid], [st].[dbid]) AS 'object_name',
    SUBSTRING(REPLACE(REPLACE(SUBSTRING([ST].text, ([req].[statement_start_offset] / 2) + 1, ((CASE [statement_end_offset]
                                                                                                   WHEN -1
                                                                                                       THEN DATALENGTH([ST].text)
                                                                                                       ELSE [req].[statement_end_offset]
                                                                                               END - [req].[statement_start_offset]) / 2) + 1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS [statement_text],
    [ST].text AS 'full_query_text'
FROM
    sys.dm_exec_requests AS req
        CROSS APPLY
    sys.dm_exec_sql_text(req.sql_handle) AS ST
        LEFT JOIN @sessions AS A
            ON A.SPID = req.session_id
ORDER BY
    [cpu_time] DESC;

How it looks like:

Of course, it's possible to modify code from Brent Ozar answer so it would select a full query text, too, though. Nearly same technique is chosen there(link of code of 18.07.2020 so might change after time):

Answer 8

I had the same problem today and I don't know what causes it but I found a solution. I took the input parameter and saved it into a new parameter, i.e.

declare @parameter2 as x = @parameter

Then i changed the references to the parameter in the queries from @parameter to @parameter2.