"Execute SQL"-tasks sent to MS SQL Server are not processed in parallel

Question

"Execute SQL"-tasks sent to MS SQL Server are not processed in parallel

svenflock

SSC-Addicted

Points: 459
More actions
April 21, 2020 at 12:46 pm

#3744948

Hi all,
I am using SQL Server 2016 with SSIS (latest patches installed as of January 2020).
It has been decided to use SSIS only as process and batch-management tool, i.e. no data streaming is used, but INSERT INTO statements are sent to the database. As I can define a workflow to allow several tasks to executed in parallel, I was wondering why I don't see that behavior on the database.
For example please find below a screenshot of my SSIS packate where I load one hub, one link and two satellites (Data Vault modelling apporach).
Each sequence container has a preparation step (i.e. drop PK, disable indices) and finalization step (i.e. re-create PK, rebuild indices offline) to allow minimal logging into an uncompressed heap (second step).
As described above, "Load target table" does not have a data flow task, but just an "execute SQL" task where the SQL query with an INSERT INTO is set.
My problem is: although several tasks are executed from SSIS (parallel execution is set to 0), on SQL server side, only one task is currently running:
I have checked that while going into the details of each process and checking the query. Appearantly, SQL server is switching between the different tasks.
Is there a way that SQL server is executing every task in parallel without just processing one at a time?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

the way you have it setup it should execute them in parallel - except potentially the drop of indexes as that locks system tables - but that should be fast enough

regarding seeing what it is doing I advise using sp_whoisactive instead of activity monitor (which has its own problems)

another possibility is that you have referential integrity and your sqls are loading both hub and sats at same time for same entity - on this case sats will wait until hub is finished

svenflock SSC-Addicted Points: 459 More actions · Answer 2

There is a top level task which drops all foreign key constraints. The tables are without any constraints (PK, Unique, FK, Check). Also, compression is disabled. Further, I use INSERT INTO XYZ WITH (TABLOCK) SELECT * FROM XYZ_QUERY to have minimal logging in place.

Didn't know of sp_whoisactive, I will have a look at it.

Do you have any ideas what may cause that behavior? Indices (with exception of primary key) are just disabled and rebuilt offline after loading.

Thanks again!

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

its now necessarily as you think - just by looking at the image you have tasks running in parallel - maybe it was just timing of when you took the print

one thing that could affect is property "MaxConcurrentExecutables" at package level - default is -1, but if it was changed to another value it could affect it.

svenflock SSC-Addicted Points: 459 More actions · Answer 4

Hi,

The MaxConcurrentExecutables setting is set to -1. I guess that the decision what to run is not done by SSIS but the SQL server engine.

I have to take a deeper look on that.

Thanks for your help!