2

We have several packages that will intermittently run until they are killed.

When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with an ASYNC_NETWORK_IO wait. The task will remain in the state until the process is killed.Source query suspended

This has been happening to several different packages on several different servers. The query that becomes suspended is always a really simple query that will run in under a second if run in SSMS.

There are 2 queries we see that get suspended often. One is an inner join from one table in a database to a table in another database. It returns ~500 rows with ~10 columns in under a second usually. The second one is just Select * from a table with ~100,000 rows.

I have been reading up on what the ASYNC_NETWORK_IO means and it looks like it is typically caused by the client application not signalling to SQL that it has consumed the data.

I have also read that it could be a resource/NIC issue but that seems unlikely to me. Some of these servers are overspec'd and the package is running on the server that holds all the data it manipulates. There should be no need for any network usage.

How can I ensure that my packages will not be suspended indefinitely?

EDIT:

The two servers that have had this issue recently have both been SQL Server 2014 Standard Edition. Version 12.0.5605.1 and Version 12.0.4213.0 .

EDIT2: I am pretty sure the cause is back pressure caused by a whole bunch of expensive transformations. The package has around 10 Oledb commands and a few slowly changing dimensions. I think It just needs to be re-written.

Improve this question
6
  • What version of SQL Server?
    – Anthony Genovese
    Commented Jun 21, 2019 at 14:51
  • @AnthonyGenovese Thanks for the clarifying question, I added the most recent server versions in an edit.
    – Matthew Bell
    Commented Jun 21, 2019 at 15:12
  • On the face of it it looks like a network issue. I would recommend doing a packet trace, or checking what network changes have been made recently if the package has previously been running successfully.
    – Tony Hinkle
    Commented Jun 21, 2019 at 15:41
  • @TonyHinkle Can it be a network issue if everything is local? I was assuming that since the job that runs the package is on the local DB server and that the package only hits databases on that server it couldn't be a network issue. The weirdest part is that it is happening on two completely different networks/servers.
    – Matthew Bell
    Commented Jun 21, 2019 at 15:51
  • @MatthewBell Possible, but not as likely. The network drivers on the server are still involved to deliver the packets, so if the NIC drivers have changed recently, for example, that would be a suspect. Something seems to be dropping packets as both sides are waiting and think the connection is alive.
    – Tony Hinkle
    Commented Jun 21, 2019 at 15:57

1 Answer 1

Reset to default
1

Based upon your description my guess is that the rest of the SSIS data flow is the problem in that it can’t process rows as fast as the source query sends them. Thus SSIS applies backpressure so that you don’t get a huge amount of rows in memory in SSIS. This backpressure causes the SQL query to be suspended. Can you describe the full data flow and all the transforms/components/destinations?

I would recommend installing the free BI Developer Extensions and running the Pipeline Component Performance Breakdown feature to confirm which component is your bottleneck.

Improve this answer
1
  • Hi Greg, it has been a while since I asked but I wanted to flag this as the answer. I am pretty sure the cause is back pressure caused by a whole bunch of expensive transformations. The package has around 10 Oledb commands and a few slowly changing dimensions. I think It just needs to be re-written.
    – Matthew Bell
    Commented Oct 2, 2019 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.