Async_Network_IO wait type 50% of wait times...how to resolve?

[Blue Sky]

I have a powerful box with the following Async_network_io implementing the
lion's share of waits...

wait_type % WaitTimSecs AvgWaitsec waitingtasks#
ASYNC_NETWORK_IO 50.86 5057 6.656 759869
MSQL_XP 12.22 1800 1039.6 1732
...
This is occuring on a two cpu dual-core 64bit 16GB ram, node1 of 2 node
act-act cluster. It sits on a SAN, with lots of space tempdb & logs
separated from the data. 300GB of data spread into 44 databases.

I am reading "SQL server 2005 waits and queues" as well as "Troubleshooting
Performance Problems in SQL Server 2005".

At this exact moment we're not experiencing user complaints... ie i'm
between fires. I want to be ready for the next one. We've had significant
'storms' of complaints seperated by silence. During the storms, everyone
(network, web, & me) see no real issues.

My CPU use is the most visible issue...it can raise to 60 to 70% during the
'storms' according to the 2008 Activity monitor. (I'm using SQL 2008 SSMS on
a different box to monitor the SQL 2005 nodes.) "Normal" cpu use is
averaging 30%. According to dm_os_schedulers, i am averaging 5
'current_tasks_Count', and 0 runnable_tasks_counts.

So, in short
1. Any specific guidance on how to troubleshoot async_network_io errors?
2. Why if the ActivityMonitor is showing a steady 30% processor time, does
the dm_os_schedulers show generally 0 (or sometimes 1) runnable tasks?

Thanks for any help in this! :)

[Chris Wood]

Are you using any 3rd party backup software like Red Gate SQLBackup? I have
seen the MSQL_XP wait with SQLBackup.

Chris

Post by Blue Sky
I have a powerful box with the following Async_network_io implementing the
lion's share of waits...
wait_type % WaitTimSecs AvgWaitsec
waitingtasks#
ASYNC_NETWORK_IO 50.86 5057 6.656 759869
MSQL_XP 12.22 1800 1039.6
1732
...
This is occuring on a two cpu dual-core 64bit 16GB ram, node1 of 2 node
act-act cluster. It sits on a SAN, with lots of space tempdb & logs
separated from the data. 300GB of data spread into 44 databases.
I am reading "SQL server 2005 waits and queues" as well as
"Troubleshooting
Performance Problems in SQL Server 2005".
At this exact moment we're not experiencing user complaints... ie i'm
between fires. I want to be ready for the next one. We've had significant
'storms' of complaints seperated by silence. During the storms, everyone
(network, web, & me) see no real issues.
My CPU use is the most visible issue...it can raise to 60 to 70% during the
'storms' according to the 2008 Activity monitor. (I'm using SQL 2008 SSMS on
a different box to monitor the SQL 2005 nodes.) "Normal" cpu use is
averaging 30%. According to dm_os_schedulers, i am averaging 5
'current_tasks_Count', and 0 runnable_tasks_counts.
So, in short
1. Any specific guidance on how to troubleshoot async_network_io errors?
2. Why if the ActivityMonitor is showing a steady 30% processor time, does
the dm_os_schedulers show generally 0 (or sometimes 1) runnable tasks?
Thanks for any help in this! :)
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

[BlueSky]

No, i'm using straight SQL server backup. In any case, i'm really focused on
the Network_io issue, the MSql_xp wait time, i just listed for reference...to
show that the next largest wait type was down at the 12% Zone.

Thanks for your answer! On my second node the top 2 waits in descending
order are
MSQL_XP @ 18% ...
Async_network_io 16%

So here, i think I agree with you, the MSQL_xp issue is in fact, a backup
issue, but i'm not too concerned with this box (at the moment).
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

Post by Chris Wood
Are you using any 3rd party backup software like Red Gate SQLBackup? I have
seen the MSQL_XP wait with SQLBackup.
Chris

Post by Blue Sky
I have a powerful box with the following Async_network_io implementing the
lion's share of waits...
wait_type % WaitTimSecs AvgWaitsec
waitingtasks#
ASYNC_NETWORK_IO 50.86 5057 6.656 759869
MSQL_XP 12.22 1800 1039.6
1732
...
This is occuring on a two cpu dual-core 64bit 16GB ram, node1 of 2 node
act-act cluster. It sits on a SAN, with lots of space tempdb & logs
separated from the data. 300GB of data spread into 44 databases.
I am reading "SQL server 2005 waits and queues" as well as
"Troubleshooting
Performance Problems in SQL Server 2005".
At this exact moment we're not experiencing user complaints... ie i'm
between fires. I want to be ready for the next one. We've had significant
'storms' of complaints seperated by silence. During the storms, everyone
(network, web, & me) see no real issues.
My CPU use is the most visible issue...it can raise to 60 to 70% during the
'storms' according to the 2008 Activity monitor. (I'm using SQL 2008 SSMS on
a different box to monitor the SQL 2005 nodes.) "Normal" cpu use is
averaging 30%. According to dm_os_schedulers, i am averaging 5
'current_tasks_Count', and 0 runnable_tasks_counts.
So, in short
1. Any specific guidance on how to troubleshoot async_network_io errors?
2. Why if the ActivityMonitor is showing a steady 30% processor time, does
the dm_os_schedulers show generally 0 (or sometimes 1) runnable tasks?
Thanks for any help in this! :)
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

[Chris Wood]

I believe that the MSQL_XP is a wait on extended stored procs. Like you I
could not find a detailed explanation of the Async_network_io waittype.

Chris

Post by BlueSky
No, i'm using straight SQL server backup. In any case, i'm really focused on
the Network_io issue, the MSql_xp wait time, i just listed for
reference...to
show that the next largest wait type was down at the 12% Zone.
Thanks for your answer! On my second node the top 2 waits in descending
order are
Async_network_io 16%
So here, i think I agree with you, the MSQL_xp issue is in fact, a backup
issue, but i'm not too concerned with this box (at the moment).
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

Post by Chris Wood
Are you using any 3rd party backup software like Red Gate SQLBackup? I have
seen the MSQL_XP wait with SQLBackup.
Chris

Post by Blue Sky
I have a powerful box with the following Async_network_io implementing the
lion's share of waits...
wait_type % WaitTimSecs AvgWaitsec
waitingtasks#
ASYNC_NETWORK_IO 50.86 5057 6.656 759869
MSQL_XP 12.22 1800 1039.6
1732
...
This is occuring on a two cpu dual-core 64bit 16GB ram, node1 of 2 node
act-act cluster. It sits on a SAN, with lots of space tempdb & logs
separated from the data. 300GB of data spread into 44 databases.
I am reading "SQL server 2005 waits and queues" as well as
"Troubleshooting
Performance Problems in SQL Server 2005".
At this exact moment we're not experiencing user complaints... ie i'm
between fires. I want to be ready for the next one. We've had significant
'storms' of complaints seperated by silence. During the storms, everyone
(network, web, & me) see no real issues.
My CPU use is the most visible issue...it can raise to 60 to 70% during the
'storms' according to the 2008 Activity monitor. (I'm using SQL 2008
SSMS
on
a different box to monitor the SQL 2005 nodes.) "Normal" cpu use is
averaging 30%. According to dm_os_schedulers, i am averaging 5
'current_tasks_Count', and 0 runnable_tasks_counts.
So, in short
1. Any specific guidance on how to troubleshoot async_network_io errors?
2. Why if the ActivityMonitor is showing a steady 30% processor time, does
the dm_os_schedulers show generally 0 (or sometimes 1) runnable tasks?
Thanks for any help in this! :)
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

[Chris Wood]

I found the answer in Ken Henderson's wonderfull SQL Server 2005 - Practical
Troubleshooting - The Database Engine book.

It says async-network-io wait type is often caused by the client not
processing results from the server. This causes the network buffers to fill.
The server cannot send more data to the client, so the task executing the
batch needs to pause while waiting for the ability to continue sending
results. The fix is to change the client so that it does not leave a
partially fetched result set open.

All from the book.

Chris

Post by BlueSky
No, i'm using straight SQL server backup. In any case, i'm really focused on
the Network_io issue, the MSql_xp wait time, i just listed for
reference...to
show that the next largest wait type was down at the 12% Zone.
Thanks for your answer! On my second node the top 2 waits in descending
order are
Async_network_io 16%
So here, i think I agree with you, the MSQL_xp issue is in fact, a backup
issue, but i'm not too concerned with this box (at the moment).
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

Post by Chris Wood
Are you using any 3rd party backup software like Red Gate SQLBackup? I have
seen the MSQL_XP wait with SQLBackup.
Chris

Post by Blue Sky
I have a powerful box with the following Async_network_io implementing the
lion's share of waits...
wait_type % WaitTimSecs AvgWaitsec
waitingtasks#
ASYNC_NETWORK_IO 50.86 5057 6.656 759869
MSQL_XP 12.22 1800 1039.6
1732
...
This is occuring on a two cpu dual-core 64bit 16GB ram, node1 of 2 node
act-act cluster. It sits on a SAN, with lots of space tempdb & logs
separated from the data. 300GB of data spread into 44 databases.
I am reading "SQL server 2005 waits and queues" as well as
"Troubleshooting
Performance Problems in SQL Server 2005".
At this exact moment we're not experiencing user complaints... ie i'm
between fires. I want to be ready for the next one. We've had significant
'storms' of complaints seperated by silence. During the storms, everyone
(network, web, & me) see no real issues.
My CPU use is the most visible issue...it can raise to 60 to 70% during the
'storms' according to the 2008 Activity monitor. (I'm using SQL 2008
SSMS
on
a different box to monitor the SQL 2005 nodes.) "Normal" cpu use is
averaging 30%. According to dm_os_schedulers, i am averaging 5
'current_tasks_Count', and 0 runnable_tasks_counts.
So, in short
1. Any specific guidance on how to troubleshoot async_network_io errors?
2. Why if the ActivityMonitor is showing a steady 30% processor time, does
the dm_os_schedulers show generally 0 (or sometimes 1) runnable tasks?
Thanks for any help in this! :)
--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)