1

Can anyone advise if there is a way to disable or limit parallel execution of SSIS packages at the server level?

I have an SSIS project with a large number of packages drawing from multiple data sources. My development and production environments are of a high enough hardware spec that executing all packages at once and letting them run to completion works fine. However, my test server has a much lower spec, and running all packages causes the CPU to max out and some packages to terminate unexpectedly.

I'm developing a Data Warehouse and currently building the staging tier. I have 50+ tables (and growing), and 50+ dtsx packages with each package populating a single table. Data sources include MSSQL, MySQL, text files and MDBs across multiple local servers.

The packages are deployed from a VS2015 project to SQL server, and all packages are queued for execution simultaneously via a script. After queuing the packages, SQL server pegs the CPU at 100% continuously causing connection timeouts to data sources, and sometimes unexpected package terminations.

Adding more hardware to the test server isn't an option, nor is combining tasks in fewer packages to leverage package-level maximum concurrency.

I want to restrict the number of packages SQL server is trying to run in parallel, at the server level.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Maff
  • 54
  • 4
  • 1
    You can set maxConcurrentExecutables on your ssis packags, which makes it only execute as many task as you want. Lets say you have 10 packages executing in one file. Then you can set maxConcurrentExecutables to 4 ex. Then it will only run 4 at the time. Or if you have many DFT running in one package. – SqlKindaGuy Dec 13 '17 at 14:13
  • That won't work I'm afraid. MaxConcurrentExecutables just limits the number of executable objects within a package. I'm trying to limit packages running in parallel at the server level. – Maff Dec 13 '17 at 14:28
  • But thats up to your architecture how you design that then. The SQL job agent only fires those packages you have. Split them up, and dont let them be in master packages. – SqlKindaGuy Dec 13 '17 at 14:30
  • Server-level settings are more of a DBA question. – Tab Alleman Dec 13 '17 at 14:35
  • @plaidDK I have 50+ tables to populate as part of the staging tier of a data warehouse. Each table is populated by its own package. It would be impossible to split this up any further. – Maff Dec 13 '17 at 15:07
  • But then you dont have a problem? They cannot run paralelle if you dont set it as paralelle. I dont get your problem. And your answer is a question of server performance not paralelleism. But if it works :) – SqlKindaGuy Dec 13 '17 at 15:17
  • @Maff instead of *splitting* create *one* job that runs the packages one after the other. Or create one master package that calls the others. There's absolutely no reason to try and force single package execution, not for such a small data mart. – Panagiotis Kanavos Dec 13 '17 at 15:43

2 Answers2

0

I had a similar problem and I'm solving it in a package launcher script. I run it in a loop until a certain number of n and then check how many packages from the project have the unfinished status. Then the script waits until a slot is free and starts the next packages.

Of course, it would be nice if MS introduced a project-level variable: Max Concurent Packages. Or at package level: Max Connurent Executions. It is probably difficult on their side and the multitude of scenarios makes them leave the problem to the programmer.

I also recommend you to replace these 50 packs with generic one. You can do the whole datawarehouse on 3 SSIS packages, which have, for example, 3 tasks each.

TylerH
  • 20,799
  • 66
  • 75
  • 101
skk
  • 45
  • 5
-1

Changing SQL Server's max degree of parallelism setting from 0 to 1 seems to have resolved the issue.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Maff
  • 54
  • 4
  • 2
    This will cause all of your other work being done by SQL Server to be limited to what ever you set MAX DOP to, not just SSIS. You might be solving one problem right now and creating several others at the same time. – Chris Albert Dec 13 '17 at 14:46
  • Thanks Chris. Fortunately the test server I'm targeting is only being used for this process. – Maff Dec 13 '17 at 15:08
  • But this is only for the SQL Server Execution plan, which makes it take more resoucces. This will absolutely not solve your question. It might tweak your performance of the server but it wont downgrade your paraelleism. – SqlKindaGuy Dec 13 '17 at 15:16
  • @plaidDK I've updated my question. Changing the server setting seems to have fixed the problem, but I'm open to other suggestions. – Maff Dec 13 '17 at 15:39
  • 1
    @Maff you could also switch the server to single-user mode to "fix" the problem. That won't solve your *real* problem though, only cover it up. Databases are meant to be used by *multiple* users, whether actual users, or applications like SSIS. And data warehouses have a LOT more than 50 tables. Write a proper agent job, or use proper precedence constraints in your packages – Panagiotis Kanavos Dec 13 '17 at 15:44
  • *Instead* of having one package per table, you could have multiple *dataflows* in a single package, and control the package's MaxThreads, DefaultMaxRows and DefaultBufferSize settings. If you execute multiple packages in parallel you have no control over them. If you put the flows inside the same package you can set reasonable limits. You can expose those properties as parameters and use different values for testing and deployment – Panagiotis Kanavos Dec 13 '17 at 15:49
  • Having one 'uber' package controlling all data flows for hundreds of tables will be unworkable and not maintainable. As you said, a database server should be able to cope with multiple processes and that's what I'm asking it to do; cope with the maximum number of packages it can run in parallel, but not so many that it maxes out the CPU and causes packages to fail. – Maff Dec 13 '17 at 16:13
  • You should set nummer of degree to AS many cores you have. At least 3 or 4 – SqlKindaGuy Dec 13 '17 at 16:52