Using ‘sys.dm_os_wait_stats’ to measure SSIS Package Performance
Microsoft SQL Server Escalation Services
We worked with a customer the other day who had 2 SSIS packages running queries against SQL Server database. One SSIS package executed twice as fast on one SQL Server as it did on another SQL Server. While troubleshooting, we wanted to see if the longer run time was a SQL engine/query issue or a SSIS package execution issue. By dividing the problem this way we could better understand where to focus the investigation.
Here is what we did:
1. Stopped as much SQL activity as possible to get clean stats.
2. Clear out SYSTEM CAHCE by executing:
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR)
3. Executed the SSIS Package and allowed it to complete.
4. Used query below to pull the cached sys.dm_os_wait_stats
We discovered the 2nd SQL/SSIS package had higher ASYNC_NETWORK_IO indicated SQL engine was waiting for SSIS to consume the rows.
ASYNC_NETWORK_IO 277625 >>>> 277 seconds = 4 minutes waiting for SSIS
ASYNC_NETWORK_IO 569515 >>>> 570 seconds = 9 minutes waiting for SSIS
This moved the investigation from “Why do these queries take longer to run on SQL” to “Why isn’t network/SSIS consuming these rows as fast on SQL/SSIS 2?”
Try this technique to divide and concur the problem the next time your seeing long running SSIS package execution times.