4

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?

James Lupolt
  • 4,258
  • 5
  • 27
  • 45
  • 4
    That is an odd question for so many people to ask unless they are experiencing high network IO waits and misunderstand what they mean. High network IO waits are generally caused by the client failing to read data as quickly as SQL can send it. What is the reason people are asking this question? – Robert L Davis Aug 19 '12 at 18:57
  • Usually because SQL Server is saturating an Ethernet link and this is causing horrible latency in a multi-server OLTP environment. I haven't seen anyone ask it because of misinterpreting ASYNC_NETWORK_IO waits yet. – James Lupolt Aug 19 '12 at 19:06
  • What have you done that shows SQL Server is actually saturating the network? There are performance counters on the OS that can be used to monitor the traffic being sent and received from the server in question. I would probably look there first to see how much it is actually doing. –  Aug 19 '12 at 19:18
  • Yes, I would always confirm by looking at Perfmon, NIC statistics, switchport statistics, MRTG graphs, etc. – James Lupolt Aug 19 '12 at 19:49
  • To be more specific: Resource Monitor's Network tab tracks network bytes sent & received per process. – James Lupolt Aug 20 '12 at 14:42

1 Answers1

7

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

Edward Dortland
  • 94
  • 1