SP 导致死锁的问题

Question

 select的下半部分是        UNION
       SELECT V_MSP_TASKS_Orig.[PROJ_ID], [TASK_UID], [TASK_ID],
                  [TASK_OUTLINE_LEVEL], [TASK_OUTLINE_NUM], [TASK_NAME], '',
                  [TASK_IS_SUMMARY], '', CASE WHEN LOWER([IsSubproject]) = 'ja' THEN 1
                                                           ELSE 0
                                                  END AS TASK_IS_SubProject, '', '', -1, -1, '',
                  0, 0, '', -1
       FROM   [V_MSP_TASKS_Orig] WITH(nolock)
                  LEFT OUTER JOIN [V_EPF_IsSubproject] WITH(nolock)
                        ON [V_MSP_TASKS_Orig].[PROJ_ID] = [V_EPF_IsSubproject].[PROJ_ID] 
       WHERE  V_MSP_TASKS_Orig.[PROJ_ID] = @ProjectID
                  AND EXISTS ( SELECT [TASK_UID]
                                    FROM   V_LineCapacityExt_AssignmentInfos WITH(nolock)
                                               LEFT OUTER JOIN V_CS_LineCapacity_Resource_FlagFields WITH(nolock)
                                                     ON V_CS_LineCapacity_Resource_FlagFields.RES_EUID = V_LineCapacityExt_AssignmentInfos.RES_EUID
                                    WHERE  V_LineCapacityExt_AssignmentInfos.[PROJ_ID] = @ProjectID AND TASK_DUR > 0
                                               AND (V_LineCapacityExt_AssignmentInfos.[RBS_FULL_NAME] LIKE @UsersRBS
                                                      + '.%'
                                                      OR (@OnlySubLevels = 0
                                                            AND V_LineCapacityExt_AssignmentInfos.[RBS_FULL_NAME] LIKE @UsersRBS
                                                           )
                                                      OR (@OnlySubLevels = 1
                                                            AND V_LineCapacityExt_AssignmentInfos.[RES_EUID] = @UsersRes_EUID
                                                           )
                                                     )
                                               AND (V_CS_LineCapacity_Resource_FlagFields.FLAG_VALUE = 1)
                                               AND (V_CS_LineCapacity_Resource_FlagFields.FLAG_FIELD_ID = @ResourceFlag_FlagFieldID)
                                               AND ((V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] < @StartDate
                                                       AND V_LineCapacityExt_AssignmentInfos.[ASSN_FINISH_DATE] >= @StartDate
                                                      )
                                                      OR (V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] >= @StartDate
                                                            AND V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] < @EndDate
                                                           )
                                                     )
                                               AND V_LineCapacityExt_AssignmentInfos.TASK_OUTLINE_NUM LIKE V_MSP_TASKS_Orig.[TASK_OUTLINE_NUM]
                                               + '.%' )
                  AND NOT EXISTS ( SELECT [TASK_UID]
                                          FROM   V_LineCapacityExt_AssignmentInfos WITH(nolock)
                                                     LEFT OUTER JOIN V_CS_LineCapacity_Resource_FlagFields WITH(nolock)
                                                           ON V_CS_LineCapacity_Resource_FlagFields.RES_EUID = V_LineCapacityExt_AssignmentInfos.RES_EUID
                                          WHERE  V_LineCapacityExt_AssignmentInfos.[PROJ_ID] = @ProjectID AND TASK_DUR > 0
                                                     AND (V_LineCapacityExt_AssignmentInfos.[RBS_FULL_NAME] LIKE @UsersRBS
                                                            + '.%'
                                                            OR (@OnlySubLevels = 0
                                                                  AND V_LineCapacityExt_AssignmentInfos.[RBS_FULL_NAME] LIKE @UsersRBS
                                                                 )
                                                            OR (@OnlySubLevels = 1
                                                                  AND V_LineCapacityExt_AssignmentInfos.[RES_EUID] = @UsersRes_EUID
                                                                 )
                                                           )
                                                     AND (V_CS_LineCapacity_Resource_FlagFields.FLAG_VALUE = 1)
                                                     AND (V_CS_LineCapacity_Resource_FlagFields.FLAG_FIELD_ID = @ResourceFlag_FlagFieldID)
                                                     AND ((V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] < @StartDate
                                                             AND V_LineCapacityExt_AssignmentInfos.[ASSN_FINISH_DATE] >= @StartDate
                                                            )
                                                            OR (V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] >= @StartDate
                                                                  AND V_LineCapacityExt_AssignmentInfos.[ASSN_START_DATE] < @EndDate
                                                                 )
                                                           )
                                                     AND V_LineCapacityExt_AssignmentInfos.TASK_UID = V_MSP_TASKS_Orig.[TASK_UID] )因为内容太长，我只能分2次贴了。

Answer 1

但是在客户的机器上，调用此存储过程依然非常慢，我在SQL SERVER PROFILER中直接调用此存储过程，都有可能需要10分钟以上的时间
>>>在profiler中找出耗时比较多的语句吧，在客户环境上录profiler

Answer 2

耗时比较多的语句 就是sp中的select, 我不理解的是 select如何会在客户环境需要那么长的时间执行。

Answer 3

在公司内部的测试环境上，profiler中显示 该SP的reads为几千到上万不等，但是在客户环境，有时候（不是每次）该SP的reads可以达到几十亿。

Answer 4

reads可以达到几十亿?汗可以考虑建立合适的索引了, 这个要靠你慢慢去调了。

Answer 5

公司测试环境和客户环境的事据库是一样的，所以我怀疑是不是类似死锁的问题，read被不停的suspend然后active.

Answer 6

Haiwer，谢谢你的回复。这个是肯定的。我的意思是，客户环境执行速度这么慢，肯定不是因为数据量的问题。 我想知道的是，此SP还有没有改进的空间。

Answer 7

硬件环境，客户的比我们自己的测试环境好很多。 现在我考虑的不是如何提高效率，而是为什么会引起超常时间的操作。 哪里是潜在的风险。
昨天客户那边profiler录到hash warnings. 我把SP重新写了下，今天早上测试，客户环境中快了10倍. 再看看后面客户的反馈吧。