SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What could cause Network I/O waits high?


What could cause Network I/O waits high?

Author
Message
DBA in Unit 7
DBA in Unit 7
SSC Eights!
SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)

Group: General Forum Members
Points: 965 Visits: 1124
Running SQL Server 2008 SP1 Enterprise Ed.

In Activity Monitor/Resource Waits, the numbers in the Network I/O waits are catching my attention: Accumulative Waits is reading as 152926; Wait time is hanging around 1000, average waiter is 1 or 2.

I would like to know if these number are too high or not. if too high, what could cause this and where should I start to look at?

Thanks in Advance..
bjhogan
bjhogan
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 382
You could always checkout the wait_type and last_wait_type from sys.dm_exec_requests.

However, I would have to ask what the problem is? Is this a performance problem you are looking into?

Network i/o could most likely be SQL Server waiting on a response from the application. We've seen instances like this before, where the developer complains that the database is slow. We see that their last request was processed and are now waiting on the application. Come to find out they went into some kind of loop within the application.
DBA in Unit 7
DBA in Unit 7
SSC Eights!
SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)

Group: General Forum Members
Points: 965 Visits: 1124
Yes, you are right. I am looking at a 'performance' problem. The application side was complaining that it is slow to pull out of data from Database. and I went to the server, this high network I/O waits is the only thing that looks out of normal to me.

I am thinking it is something with the APP side too in my case.

As DBA, before we are proved innocent, we are always Default Blame Acceptor.

I have to show them something:

Could you please give some advise on the methods to get the proof showing the app side that their DB requests are processed and waiting on the APP side?


Thank you very much for your reply!
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14847 Visits: 4639
DBA in Unit 7 (12/15/2010)
Yes, you are right. I am looking at a 'performance' problem. The application side was complaining that it is slow to pull out of data from Database. and I went to the server, this high network I/O waits is the only thing that looks out of normal to me.

I am thinking it is something with the APP side too in my case.

As DBA, before we are proved innocent, we are always Default Blame Acceptor.

I have to show them something:

Could you please give some advise on the methods to get the proof showing the app side that their DB requests are processed and waiting on the APP side?


I'll run offending query both from the application and on the server.

If query takes about the same time then you are ruling out network as the root cause of the issue and you can focus on fine tuning the query.

If query from app side really takes much longer then you contact your Network guys and ask them to monitor network status.

Just for fun...
how many hops in between app client and server?
what's the latency in between app client and server?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
bjhogan
bjhogan
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 382
In cases like this, I will start by looking at overall performance. Here are sort of the high level things I do

1) Check overall CPU.
2) If CPU is good check for blocking
3) If no blocking, check the active processes(spids)

Again...this is very high level and from each one you can obviously start digging in. However, I would get more information from the developer as to what the app is doing(my luck is they usually don't know). How long it's been slow...did they change anything?

Typically once I get past those three things above and if there isn't really any activity from that application, I can at least tell them I don't see them doing much work.

Obviously, if they can get you the query that is running slow, that is extremely helpful.
DBA in Unit 7
DBA in Unit 7
SSC Eights!
SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)

Group: General Forum Members
Points: 965 Visits: 1124
Thanks a lot for the pointers!
The overall server status is pretty good. CPU usage is well under 10%.
Buffer cache hit ratio is almost 100%.

There is no developer to ask for code in my case. It is a third party application from vendor backed by SQL Server database. I think I will ask the user to run the app tasks that are taking long time and at the same time I do the profiler at the SQL side to capture what's going on. Then go from there.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search