sql服务器-sql在作为SP执行时挂起，但与sql一样正常

Question

问候，我一直在分析一个删除存储过程的问题。该过程只是对某个实体执行级联删除。

当我在查询编辑器中将SP分解为SQL时，它会在大约7秒内运行，但是，当通过EXEC SP执行SP时，它需要1分钟以上的时间才能执行。

我尝试了以下没有运气：

• 已删除SP，然后使用WITH RECOMPILE重新创建

• 增加了设置事务隔离级别读取未委托

• SQL在编辑器中运行，有许多并发连接，没有问题。

• EXEC过程在有或没有并发连接的情况下挂起

程序类似于：

 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

Answer 1

确保您的统计数据是最新的。假设DELETE语句对传递的参数有一定的引用，如果使用的是SQL 2008，则可以尝试OPTIMIZE FOR UNKNOWN选项。

Answer 2

与任何性能问题一样，您需要衡量它"挂起"的原因。猜测不会让你很快得到任何东西。使用方法论方法，如Waits和Queues。最简单的方法是查看sys.dm_exec_requests中的wait_type、wait_time和wait_resource，用于执行exec-sp的请求。根据实际导致阻塞的原因，可以采取适当的操作。

Answer 3

这更像是一个参数嗅探(或Spoofing(问题。这是一个很少使用的SP。对一个相当大的表使用参数的语句使用OPTION(OPTIMIZE FOR UNKNOWN(显然解决了问题。感谢SqlACID的提示。

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