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.