Monitor volume of data returned to the client application

Question

I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team and rebuffed in a way and so I'd like to provide more detail to emphasise that we are sending an excess of data to the client. My analysis contained a supporting document explaining waits and also a wealth of resources including those from the actual client application side stating that high ASYNC_NETWORK_IO is related to poor code.

To follow this up, I'd like additional detail emphasising the volume of data consistently returned the client and tie these to peaks in our ASYNC_NETWORK_IO waits. Are there any DMVs or any other free sources I could use to report on data sent to the client?

I am aware of sys.dm_exec_query_stats although this only displays information for queries still in the plan cache.

Edit: Over the past couple of weeks/last month performance has degraded significantly which coincides with a colleague leaving the company. Whether this is coincidence or not is unknown to me. The issue we have is we are unaware of what is going on at the client side. At the moment we have effectively 1 junior developer looking at it and no means to monitor. From the SQL Server side I am constantly seeing little strain and I want to rule SQL as the bottleneck. Obviously things can be done to improve the databases and I'll never dispute it but the issue at the moment is not SQL Server related and I need to be able to relay this. Factors such as the application simply not being able to respond and issues with connecting from the client to the actual application server (not application SQL Server) are for some reason being attributed to SQL Server, with our software development manager looking at Indexing of all things as a cause for the application not responding and people not being able to connect despite no request being made the the SQL Server ?!?!?

Our situation is an incredibly frustrating one in that it's heavily turning into a political battle and accusations are been thrown at us without any substance or data to back them up. In terms of understanding what happens at the client side, it's really difficult for me, like I said, we have no monitoring on the application side, but also the way in which the client submits queries makes things difficult. The request we see at the SQL Server layer is C\AL code (from the dynamics NAV application), but also they appear to have instances where the client session sumbits many batches....

I'll provide an example from last week which caused great confusion for me. The user submitted a query on the client side to return aged stock for customers. This request executed for 30 minutes however whilst monitoring from the SQL Server side I saw many individual requests from the session. So in SQL obviously we'd just join the relevant tables using good syntax and practise to return data. However on the client side, from what I could tell would iterate through all customers in the customers table, and one by one return the data back to the client whilst simulateanously looking at the other relevant tables for aged stock information by individual customer.

This was just one report and unfortunately I am getting little support from devs for troubleshooting. The whole political situation is really hurting our process with the software development manager pursuing a theory based on 'finger in the air' logic. Apologies for the long read.

Thanks,

Answer 1

Warning: this is more of an soft-skill, wholistic approach answer than a technically oriented one.

I'm not aware of anything that will make quick work of this problem - you did say it was a problem, right? I'm not saying don't pursue this, but if everything is basically working it may not be worth dying on this hill. That is often why developers are had pressed to care at all, in my experience. However, if there are actual problems and you've narrowed down this is the cause...

It should be pretty easy to find out the queries that are your worst offenders (by time, execution frequency, etc) based on your workload. Because if there is a problem, it should be obvious.

Then, just look at what they do. Are they doing a ton of SELECT * on wide tables? Did someone decide to store a ton of XML into an NVARCHAR(MAX) column? Run them a few times and use Client Statistics in SSMS to view the actual size of the data returned. These are all well researched and documented areas of best practice that you can communicate are necessary to ensure smooth operation of the database.

But, it may not even be that the data size is hyuuge. It could be just how the client is handling the data (not uncommonly RBAR is at play in this scenario). In these cases, I try to convince the developers to give me read access to their repositories so I can tunnel in and try to do some light reverse-engineering of the code to understand myself what is going on. I haven't had much push back from this since it is an easy ask and doesn't require much of them.

Depending on your language experience, this may be easy or hard, but it definitely can't hurt to start to understand how the ORM and/or code is traversing through the data it receives. From there, if there are any big flashing red lights, you can probably find more resources or patterns to describe the ideal way to handle the data.

But until you can narrow down the problem more clearly, it isn't surprising no one wants to volunteer their time to help on a wild goose chase.

It can be hard to get development teams to take the concerns of a DBA seriously, so the more you understand the better. The blame game between developers and DBAs can get tense fast and usually doesn't end up benefiting anyone, so just make sure you're not accidentally making them feel attacked or blamed as the source of a problem.

Finally - there are other things that can cause this, but they are very rarely the cause in 2018, so be sure to rule those out just in case.