A frequent request I get from clients is to help figure out what queries or clients are making the largest contribution to SQL Server saturating an Ethernet link.
Ideally I'd look at some counters or DMVs showing network I/O aggregated by query, client, application, database, etc.
Since there doesn't appear to be a direct way of doing that, as a substitute I usually look at the *_rows columns in sys.dm_exec_query_stats (these columns exist in 2008 R2+ only, I think) and also look for queries that appear to involve BLOBs.
Is there a better way of approaching this problem?
Best Answer
Your correct, 2008R2 and onwards has the *_rows added to sys.dm_exec_query_stats. I would say that's the way to go.
You'll have plenty of queries that can create massive disk IO but still have a small result set. However, it's not possible to have a huge result set without huge logical IOs.
So look at query stats ordered by logical io's first, then row count second. That will give you a pretty good starting point.
Before 2008 R2 I would look at which queries with large logical IOs have network related waitstats. The problem with this approach is. 1) Once the network is saturated, it's likely that not only your large queries, but pretty much all your queries will have network related waitstats. 2)Network related wait stats are in many cases not caused by network related issues ;-)
cheers, Edward