I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter period of time, it seems to flatline at around 20%.

Looking at Task Manager I can see that all cores are being used during the test runs.

It is a Windows 2016 Server running 16 CPU cores on a VM with 32GB of RAM. SQL Server 2016.

Cost Threshold for Parallelism is set to 50 and MAXDOP is set to 0 on the SQL instance.

My question is, is there a setting on the SQL Server instance or in the SSIS package that will allow me to make the CPU run at 100% and save CPU idle time?

  • 1
    It's very likely that you're running into is a situation where CPU isn't your bottleneck. Either disk or something in your queries or packages are where your aggregations are waiting. – Zane May 16 '19 at 15:18
  • 1
    empirically, 20% of those 16 cores is 3.3 cores. Which would make me think it's a CPU per data flow/process/whatever. You sure it's SQL Server or the SSIS service filling all the CPUs? – Marian May 16 '19 at 15:56
  • @Zane, very true, i think I'll have a dig around on the waits metrics. The developers also found MAXDOP=1 in the code, so they will try taking them out and try another run. – Dennis Finke May 17 '19 at 7:17
  • 1
    @DennisFinke well once you've got some more details please keep updating the question and we'll likely be able to help get that running faster. – Zane May 17 '19 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.