Stored Procedure Runs 24,000% slower via SSIS (via SQL Agent job) vs Manual Execution via SSMS

Question

I am facing a very weird issue where a stored procedure runs very slowly via SSIS package (run via SQL Agent job) compare to if I run it manually in SSMS.

Via the job, it takes ~2 hours, where manually running it takes only 30 seconds!

Exact same stored procedure and run on the same server.

This is the structure of the flow in the SSIS package:

The stored procedure's name in question is BR_SHP_Timekeeper_Costs.

The Execute SQL Task with the same name uses ADO.NET connection manager and run:

EXEC BR_SHP_Timekeeper_Costs @p1, @p2

As you can see also, this task is "chained" by precedence constraint so that it will run on its own, i.e. won't be contending with other tasks.

What I noticed was that during the execution of the package (via SQL Agent), when it hits that task, I could see lots of CXPACKET wait type in Activity Monitor and CPU is running 97-99%.

FYI, the server has 8 vCPU with MAXDOP is set to 0 and Cost of Parallelism Threshold is set to 5

So far, I have tried / investigated / found out the following:

There is only 1 cached execution plan for this stored procedure and it is used by both the SSIS and SSMS (manually running the stored procedure)
Created a dummy SQL Agent Job running T-SQL - EXEC BR_SHP_Timekeeper_Costs. The job was completed in ~30 seconds.
Created a dummy SSIS package which only contains a Execute SQL Task and runs the same stored procedure using ADO.NET connection manager. Then run it via a new SQL Agent Job. Completed in ~30 seconds.

What else can I check here?

Any ideas why this happens? I've been scratching my head for a week or so..

try running your stored proc in SSIS like below and see if it helps — TheGameiswar
try setting option (maxdop 0) to all statements inside storedproc and also run it using option(recompile) — TheGameiswar
@TheGameiswar yes, I am planning to do this. Although it still does not explain why if I run it manually in SSMS and even when run via dummy SSIS package via Agent Job, it returns in 30 seconds..? Could it be something to do with statistics? At the end of the actual job, it runs sp_updatestats on the database involved. — iKnowNothing
per query wait stats helps in this,with out a total MVCE,we can only do a trial and error — TheGameiswar
Are the parameters passed in SSIS and SSMS the same? If not, have you eliminated parameter sniffing as the culprit? To vet out the execution plan... does it run any faster in SSIS if you option for WITH (RECOMPILE). — Troy Witthoeft

tavito tavito · Accepted Answer · 2017-08-08T01:01:10

Maybe you could try assigning the parameters @p1 and @p2 to two variables defined in the stored procedure and then use these 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, in some cases, could accelerate the execution.

Hope it helps you!

Stored Procedure Runs 24,000% slower via SSIS (via SQL Agent job) vs Manual Execution via SSMS

1 Answers