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..