Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What could cause Network I/O waits high? Expand / Collapse
Author
Message
Posted Tuesday, December 14, 2010 2:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:13 PM
Points: 195, Visits: 1,080
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..
Post #1034732
Posted Wednesday, December 15, 2010 6:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 8:20 PM
Points: 56, Visits: 380
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.
Post #1035070
Posted Wednesday, December 15, 2010 6:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:13 PM
Points: 195, Visits: 1,080
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!
Post #1035076
Posted Wednesday, December 15, 2010 7:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1035107
Posted Wednesday, December 15, 2010 7:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 8:20 PM
Points: 56, Visits: 380
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.
Post #1035112
Posted Wednesday, December 15, 2010 7:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:13 PM
Points: 195, Visits: 1,080
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.


Post #1035126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse