Sql-server – async_network_io issues with BIDS

sql-server-2008ssrswait-types

This is related to my previous question drastically different runtimes in BIDS and the SSRS Web Portal.

I am struggling with refreshing some reports in BIDS because they run forever. I have now noticed that when I refresh the report in BIDS the query will experience ASYNC_NETWORK_IO waits. (also CXPACKET but I understand that's more an effect than a cause)

The post Need help with ASYNC_NETWORK_IO seems to indicate SQL Server is waiting on the client, which I assume in this case is BIDS on my desktop.

If it can be identified from this snippet of information, where is the problem here? Is it my desktop and its lack of oomph? Could it be the network? Is my report doing too many calculations on the report-side? Where else should I look? How can I fix it?

I will add that oftentimes the data never full returns. Instead I receive the following (redundant) error message:

An error occurred during local report processing. An error has occurred during report processing. Exception of type 'System.OutOfMemoryException' was thrown.

Best Answer

We actually ran into a similar issue at StackOverflow and Kyle blogged about it:

http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/

The problem can be a number of things:

  • Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields)
  • Client-side apps processing data row by row instead of pulling it all into memory and then doing whatever work necessary
  • Underpowered app server hardware (or in your case, client machines) that are paging to disk or churning on CPU. I see this a lot on over-committed virtual machines.

To tune it, start by looking at Perfmon counters on your own machine. My tutorial on it is at http://www.BrentOzar.com/go/perfmon, and it includes a list of counters to gather, how to analyze 'em, and how to interpret your bottleneck.

Related Question