3

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.

Improve this question
3
  • that error normally means the local machine receiving the data can't return it all. I've seen it before when I try to return a really huge result set in SSMS.
    – JNK
    Commented Apr 24, 2012 at 12:57
  • Thanks, @JNK - how to fix it? What is it about the local machine? Hardware? Software? Report design?
    – JHFB
    Commented Apr 24, 2012 at 13:01
  • 3
    Well I think what you are trying to do is pushing the machine too hard. Are you returning a very large result? Basically it means that your machine can't display whatever it's trying to display because you exceeded the system resources. I'm not sure if more RAM can fix it or not. You're trying to drink water through a fire hose, and the real fix is to dial back what you are returning.
    – JNK
    Commented Apr 24, 2012 at 13:05

1 Answer 1

Reset to default
6
+50

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.

Improve this answer
3
  • Thanks, Brent - I'm going to be digging deeper into your suggestions...!
    – JHFB
    Commented Apr 25, 2012 at 11:59
  • Bounty awarded, and your feedback was well worth my reputation points!
    – JHFB
    Commented Apr 30, 2012 at 13:22
  • @JHFB cool, glad I could help!
    – Brent Ozar
    Commented Apr 30, 2012 at 15:16

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.