首页 文章

存储过程通过SSIS（通过SQL代理作业）与通过SSMS手动执行相比，运行速度降低24,000％

提问于
浏览 次
3

我面临一个非常奇怪的问题，即如果我在SSMS中手动运行存储过程，则通过SSIS包（通过SQL代理作业运行）运行速度非常慢 .

通过这项工作，需要约2个小时，手动运行只需30秒！

完全相同的存储过程并在同一服务器上运行 .

这是SSIS包中流程的结构：

The SSIS control flow

有问题的存储过程的名称是 BR_SHP_Timekeeper_Costs .

具有相同名称的 Execute SQL Task 使用 ADO.NET connection manager 并运行：

EXEC BR_SHP_Timekeeper_Costs @p1, @p2

正如您所看到的，此任务由优先约束“链接”，以便它将自行运行，即不会与其他任务竞争 .

我注意到的是，在执行程序包（通过SQL代理）期间，当它遇到该任务时，我可以在Activity Monitor中看到大量的CXPACKET等待类型，CPU正在运行97-99％ .

仅供参考，服务器有8个vCPU，MAXDOP设置为0，并行度阈值设置为5

到目前为止，我已经尝试/调查/发现了以下内容：

  • 此存储过程只有1个缓存执行计划，SSIS和SSMS都使用它（手动运行存储过程）

  • 创建了一个运行T-SQL的伪SQL代理作业 - EXEC BR_SHP_Timekeeper_Costs . 这项工作在约30秒内完成 .

  • 创建了一个虚拟SSIS包，它只包含一个执行SQL任务，并使用ADO.NET连接管理器运行相同的存储过程 . 然后通过新的SQL代理作业运行它 . 在~30秒内完成 .

我还能在这里查看什么？

任何想法为什么会这样？我已经摸不着头脑了一个星期左右..

sql-server stored-procedures ssis sql-server-2014 database-performance

1 回答

  • 0

    也许您可以尝试将参数@ p1和@ p2分配给存储过程中定义的两个变量，然后使用这些变量而不是参数 . 例如：

    ALTER PROCEDURE BR_SHP_Timekeeper_Costs
@p1 int,
@p2 int

AS

declare @_p1 int, @_p2 int
set @_p1 = @p1
set @_p2 = @p2
....
....
select column1, column2 from table t where t.p1 = @_p1
....
....

    在某些情况下，此解决方法可以加快执行速度 .

    希望它能帮到你！

    回复于

相关问题