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

DBA808 gravatar 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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(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][1]

[1]: http://www.confio.com/English/Tips/Async_Network_IO.php
more ▼

answered Dec 15, 2010 at 12:53 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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][1] >"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...

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
more ▼

answered Dec 15, 2010 at 01:56 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

+1 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

ozamora gravatar image

ozamora
1.4k 2 3 5

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

answered Dec 15, 2010 at 01:02 AM

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1842

asked: Dec 14, 2010 at 12:42 PM

Seen: 18167 times

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