Using 'sys.dm_os_wait_stats' to measure SSIS Package Performance


Using 'sys.dm_os_wait_stats' to measure SSIS Package Performance

Chris Skorlinski
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)

GO

3. Executed the SSIS Package and allowed it to complete.

4. Used query below to pull the cached sys.dm_os_wait_stats

--dm_os_wait_stats

SELECT *

FROM sys.dm_os_wait_stats

Go

We discovered the 2nd SQL/SSIS package had higher ASYNC_NETWORK_IO indicated SQL engine was waiting for SSIS to consume the rows. 

--SQL/SSIS 1

wait_type                           wait_time_ms

----------------------------------- --------------------

ASYNC_NETWORK_IO                    277625 >>>> 277 seconds = 4 minutes waiting for SSIS

PAGEIOLATCH_EX                      10406

 

 

--SQL/SSIS 2

wait_type                           wait_time_ms

----------------------------------- --------------------

ASYNC_NETWORK_IO                    569515 >>>> 570 seconds = 9 minutes waiting for SSIS

PAGEIOLATCH_EX                      3125

 

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.

 


Comments (0)

Skip to main content