'In my first post about my tuning series I wrote about the general methodology how tuning works: SSIS Performance Tuning – Methodology and general approach
Now I’d like to talk about what we need to collect, what the tools are and how they work. Before I start I would like to say a special thank you to my colleague Christian Jurjut, who invested a lot of time in analyzing this area and put together this fantastic guideline.
In order to achieve effective and goal-oriented performance tuning it is mandatory to know your system’s bottlenecks. In most cases the root causes for those bottlenecks lie deep in the system’s architecture and are not visible at first sight. So the identification can be very time consuming and, in worst case, goes round in circles and comes to no result. Fortunately there are a few tools which help us monitoring different parts of the system. The most important and helpful will be explained in the following.'...
https://nexxtjump.com/2012/06/12/ssis-performance-tuningmonitoring-data-collection/
'While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature of the SSIS catalog from 2012 and up is the dm_execution_performance_counters function, which tracks these perfmon counters for you during package execution. This provides the ability to begin proactive monitoring of SSIS performance monitor counters, checking important perfmon metrics such as Buffers Spooled prior to being notified by management or users. It also provides an alternate way to troubleshoot and understand SSIS bottlenecks without needing to remote to the server and configure a separate perfmon trace. This blog post serves to walk through the dm_execution_performance_counters function, what it tracks, and how it can be utilized effectively within an environment.'...
https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/15/ssis-capturing-perfmon-counters-during-package-execution/
'While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature of the SSIS catalog from 2012 and up is the dm_execution_performance_counters function, which tracks these perfmon counters for you during package execution. This provides the ability to begin proactive monitoring of SSIS performance monitor counters, checking important perfmon metrics such as Buffers Spooled prior to being notified by management or users. It also provides an alternate way to troubleshoot and understand SSIS bottlenecks without needing to remote to the server and configure a separate perfmon trace. This blog post serves to walk through the dm_execution_performance_counters function, what it tracks, and how it can be utilized effectively within an environment.'...
https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/03/15/ssis-capturing-perfmon-counters-during-package-execution/