Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 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. –  Shawn Melton Aug 19 '12 at 19:18
    
Yes, I would always confirm by looking at Perfmon, NIC statistics, switchport statistics, MRTG graphs, etc. –  James Aug 19 '12 at 19:49
    
To be more specific: Resource Monitor's Network tab tracks network bytes sent & received per process. –  James Aug 20 '12 at 14:42
add comment

1 Answer

up vote 6 down vote accepted

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

share|improve this answer
    
Hey thanks, that helps me think about it a little more clearly. –  James Aug 19 '12 at 21:24
1  
Just one more thing. The rows counters are there from SP1 onwards. just a tiny little detail I forgot to mention in my previous comment ;-) –  Edward Dortland Aug 19 '12 at 21:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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