0

Context: I have around 30 independent stored procedures that are currently executing in series through the SSIS package. I believe by taking advantage of parallel execution in SSIS I can gain some performance.

How to figure out the number of Execute SQL tasks that have to be configured? (get maximum out of it / optimal approach)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
N.Dinesh.Reddy
  • 522
  • 2
  • 7
  • 15
  • 1
    You are probably thinking of MaxDOP, [Max Degree of Parallelism](https://blogs.msdn.microsoft.com/psssql/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop/). That setting affects "concurrency of a single query" not "how many concurrent queries." They are different things. – AlwaysLearning Aug 12 '19 at 06:26
  • 1
    "How to figure out the number of Execute SQL tasks that have to be configured" - how many cores do you have? – Mitch Wheat Aug 12 '19 at 06:34
  • QUAD - v4 core CPU – N.Dinesh.Reddy Aug 12 '19 at 08:12
  • 1
    Test. There is no single correct number, this depends greatly on the actual work the queries do. SQL Server was built to scale and can handle as many queries as the hardware can handle, but how many that is also depends on the I/O, memory and network subsystems, usually more so than the CPU. – Jeroen Mostert Aug 12 '19 at 09:42
  • 1
    I would configure SSIS to run all of the tasks in parallel. SSIS will base the number it actually executes in parallel on the MaxConcurrentExecutables property which defaults to -1, which equates to the number of physical or logical processors plus 2 and the EngineThreads property which is a property of each Data Flow task, which defaults to 10 which means that each dataflow can create 10 source threads and 10 worker threads. see https://www.jamesserra.com/archive/2011/11/parallel-execution-in-ssis/ – Steve Ford Aug 13 '19 at 10:44

0 Answers0