Stored procedure runs 24,000% slower via SSIS (via SQL Agent job) vs Manual execution via SSMS
I am facing a very strange issue where the stored procedure is very slow through an SSIS package (executed through a SQL Agent job) if I run it manually in SSMS.
It takes ~ 2 hours through the job, when manual control only takes 30 seconds!
Exact same stored procedure and run on the same server.
This is the flow structure in the SSIS package:
The name of the stored procedure in question is BR_SHP_Timekeeper_Costs
.
Execute SQL Task
with the same name uses ADO.NET connection manager
and runs:
EXEC BR_SHP_Timekeeper_Costs @p1, @p2
As you can see, this task is "tied" with a priority constraint, so it will work on its own, that is, it will not compete with other tasks.
I noticed that during batch execution (via SQL agent) when it hits this task, I could see many CXPACKET wait types in Activity Monitor and CPU is 97-99%.
FYI, the server has 8 vCPUs with MAXDOP set to 0 and the cost of Parallelism Threshold set to 5
So far I have tried / researched / figured out the following:
-
There is only 1 cached execution plan for this stored procedure and is used by both SSIS and SSMS (manually executing the stored procedure)
-
Dummy SQL Agent Created T-SQL Job -
EXEC BR_SHP_Timekeeper_Costs
. The task is completed in ~ 30 seconds. -
I created a dummy SSIS package that contains only the Execute SQL Task and runs the same stored procedure using the ADO.NET Connection Manager. Then start it with a new SQL Agent job. Completed in ~ 30 seconds.
What else can I check here?
Any ideas why this is happening? I have been scratching my head for a week or so.
source to share
Perhaps you could try to assign the parameters @ p1 and @ p2 to the two variables defined in the stored procedure, and then use those variables instead of the parameters. For example:
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
....
....
This workaround can speed up execution in some cases.
Hope this helps you!
source to share