x

what could cause Network I/O waits HIGH?

**SQL Version: SQL 2008 sp1

In the Activity Monitor//RESOURCE Waits//Network I/O, I could see a high number of accumulative waits number (right now it reads 152923) and the wait Time (ms/sec) is hanging around 1000. Is it BAD? if it is What could cause this? where do i need to look at the causes? TIA**

more ▼

asked Dec 14, 2010 at 12:42 PM in Default

avatar image

DBA808
11 1 1 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Look at the client side code that is consuming the data. Failing that, look at the network utilisation between the SQL Server and the client. Definitely worth making sure that you have separated front end network (i.e. client connectivity) from iSCSI network if you are using iSCSI storage...

more ▼

answered Dec 14, 2010 at 01:52 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

It could be the database backup tools, they are heavy users of network. If thats the case you can decrease the Network IO waits with tools like Red-gate Backup to backup to "local" hard drive and then use a different tool to take file backup. You will still have network traffic and heavier load on the disk system, but you can use compression to decrease the IO and network load.

You can use some DMV to find what resources your statements are waiting for:

 SELECT st.text AS [SQL Text],
 w.session_id, 
 w.wait_duration_ms,
 w.wait_type, w.resource_address, 
 w.blocking_session_id, 
 w.resource_description FROM sys.dm_os_waiting_tasks AS w
 INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id 
 CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
 AS st WHERE w.session_id > 50
 AND w.wait_duration_ms > 0

Read more at http://www.confio.com/English/Tips/Async_Network_IO.php

more ▼

answered Dec 15, 2010 at 12:53 AM

avatar image

Håkan Winther
16.4k 36 45 57

(comments are locked)
10|1200 characters needed characters left

If you want to see details on all waits then this is one of the 1st places to start: http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

"

ASYNC_NETWORK_IO - This is commonly where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it's consuming it reeeeeally slowly because of poor programming."

Looks like you could be starting quite a trail to find the culprit...

more ▼

answered Dec 15, 2010 at 01:56 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

  • for the link to a blog with all wait stats.

Dec 15, 2010 at 05:06 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

Network configuration. Check for negotiated speeds at the NIC and switch levels

more ▼

answered Dec 14, 2010 at 07:24 PM

avatar image

ozamora
1.4k 3 17 7

(comments are locked)
10|1200 characters needed characters left

In addition to @Ozamora post...

Check the post below out? If you have network admin, check with them before making ANY nic changes...

Link

more ▼

answered Dec 15, 2010 at 01:02 AM

avatar image

sp_lock
10.5k 27 37 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2072

asked: Dec 14, 2010 at 12:42 PM

Seen: 29357 times

Last Updated: Dec 14, 2010 at 12:44 PM

Copyright 2016 Redgate Software. Privacy Policy