data-konzept

'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/