CODEWITHSUNDEEP
sql-serverstored-proceduresssissql-server-2014database-performance
iKnowNothing
iKnowNothing

Reputation: 79

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

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 SSIS control flow

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:

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

  2. Created a dummy SQL Agent Job running T-SQL - EXEC BR_SHP_Timekeeper_Costs. The job was completed in ~30 seconds.

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

Upvotes: 3

Views: 1677

Answers (1)

tavito
tavito

Reputation: 190

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!

Upvotes: 0

Related Questions