Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server, or Is it always utilized as in that is just how SQL Server spools data to the client?

Is "Row By Agonizing Row (RBAR)" a feature of ODBC or .NET Data Components, also, does anyone know if it can be turned off or overridden?

This stems from a SP that was tuned to run in 5 minutes, but when put in a report it falls in and out of ASYNC_NETWORK_IO for another 5 minutes for a total time of 10 minutes. In the worst case scenario, 800K records are returned, however, I would think the data would be "pushed" more quickly than what I am seeing. This is based on the fact that a similar report with no Column grouping seems to pull data quickly and returns the same amount of data. I know that grouping brings reports to a crawl, but please help me work with this issue if possible. Why would one query on a report with no grouping,with the same amount of records,"dumped" to a excel file, finish in 2 minutes, whereas, column grouping causes the client to buffer. Is there a way to force SSRS to buffer all the data locally, then render?

I found that using a "shared memory connection" will share the buffer, however, that is only valid if the client and SQL Server share the same server.

Also, the query plan for the SP is the size of a roadmap but it is a good plan. It uses no cursors and is entirely a set based query.

  • 4
    You may want to distill your questions into a couple bullet points to make it easier for the volunteers here to focus on the right things so you can get answers. – Erik Darling Jan 28 '19 at 23:11
  • 4
    The symptoms suggest the slowness isn't due to the speed SQL Server "pushes" the results but rather slowness of the client "pulling" the results. I suspect the grouping causes more CPU and memory resource utilization on the client so it can't fetch results as quickly. – Dan Guzman Jan 29 '19 at 3:39
  • @Dan Guzman - You are correct. We are stress testing many instances of some large reports in SSRS and trying to determine the correct amount of RAM required. When the reports run the system is severely stressed. I follow what you are saying. I wonder if this wait type is a aspect of the scheduler task switching the client threads, or is it a higher level switch based on some other metric like available memory, network throughput, read duration, or just the way SQL server implements sending packets over the wire in its protocol? Thanks for you response. – Ross Bush Jan 29 '19 at 13:04
  • Note that RBAR is a expression that simply describes either code or query plans that do require some sort of row by row processing. It can only be "disabled" by rewriting the code or tuning the query. – RDFozz Jan 30 '19 at 16:24

With .NET SqlClient (and every other client library) there is no setting that forces the client to fetch and cache the entire resultset. And SSRS will read the query results as it renders reports, and whether it reads the entire resultset at once will depend on the report design and the rendering destination.

You may be able to get SSRS to read the results more quickly if you use Shared Dataset Caching.

But it's not obvious that there's a problem that requires fixing. You have a report that consumes 800K rows. Forcing it to fetch all that data at the beginning is going to be expensive on the report server, and may not make anything better on your SQL Server.

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