Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Apr 24 '12 at 12:57
    
Thanks, @JNK - how to fix it? What is it about the local machine? Hardware? Software? Report design? –  JHFB Apr 24 '12 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 Apr 24 '12 at 13:05
add comment

1 Answer

up vote 4 down vote accepted
+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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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