CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
Ross Bush
Ross Bush

Reputation: 15175

SQL hangs when executed as SP but is fine as SQL

Greetings, I have been analyzing a problem with a delete stored procedure. The procedure simply performs a cascading delete of a certain entity.

When I break the SP out into SQL in the query editor it runs in approx. 7 seconds, however, when the SP is executed via EXEC SP it takes over 1 minute to execute.

I have tried the following with no luck:

The procedure is similar to:

 ALTER PROCEDURE [dbo].[DELETE_Something]
    (
        @SomethingID INT,
        @Result INT OUT,
        @ResultMessage NVARCHAR(1000) OUT
    )--WITH RECOMPILE--!!!DEBUGGING
    AS

    --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED--!!!DEBUGGING

    SET @Result=1
    BEGIN TRANSACTION
    BEGIN TRY

        DELETE FROM XXXXX --APPROX. 34 Records
        DELETE FROM XXXX  --APPROX. 227 Records         
        DELETE FROM XXX   --APPROX. 58 Records  
        DELETE FROM XX    --APPROX. 24 Records      
        DELETE FROM X     --APPROX. 14 Records  

        DELETE FROM A -- 1 Record
        DELETE FROM B -- 1 Record
        DELETE FROM C -- 1 Record

        DELETE FROM D --APROX. 3400 Records !!!HANGS FOR OVER ONE MINUTE TRACING THROUGH SP BUT NOT SQL

        GOTO COMMIT_TRANS   

    END TRY
    BEGIN CATCH 
        GOTO ROLLBACK_TRANS
    END CATCH       

    COMMIT_TRANS:
        SET @Result=1   
        COMMIT TRANSACTION
        RETURN  

    ROLLBACK_TRANS: 
        SET @Result=0
        SET @ResultMessage=CAST(ERROR_MESSAGE() AS NVARCHAR(1000))
        ROLLBACK TRANSACTION
        RETURN

Upvotes: 2

Views: 414

Answers (3)

SqlACID
SqlACID

Reputation: 4014

Make sure your statistics are up to date. Assuming the DELETE statements have some reference to the parameters getting passed, you might try the OPTIMIZE FOR UNKNOWN option, if you are using SQL 2008.

Upvotes: 2

Ross Bush
Ross Bush

Reputation: 15175

This was more of a Parameter Sniffing (or Spoofing) issue. This is a rarely used SP. Using the OPTION (OPTIMIZE FOR UNKNOWN) for a statement using the parameter against a rather large table apparently solved the problem. Thank you SqlACID for the tip.

DELETE FROM 
   ProblemTableWithManyIndexes
WHERE                                   
   TableID=@TableID 
OPTION (OPTIMIZE FOR UNKNOWN)

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294307

As with any performance problem, you need to measure why it 'hangs'. Guessing will get you nowhere fast. Use a methodological approach, like Waits and Queues. The simplest thing to do is look at wait_type, wait_time and wait_resource in sys.dm_exec_requests, for the request doing the exec sp, while it executes the sp. Based on what is actually causing the blockage, you can take appropriate action.

Upvotes: 1

Related Questions