ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types started
showing up in activity monitor (sql 2005). It turns out the optimizer was
picking a bad execution plan for a certain report stored procedure and it was
taking much longer than normal. We've fixed that issue, but my question was
more of why we were seeing that wait type. My understanding of
async_network_io is it indicates that SQL Server is waiting on sending data
back to the client. It looks like stored procedure was taking long enough for
the client app to time out. Eventually when the procedure finished, the
connection just hangs with a wait type of async_network_io until I explicitly
issue a Kill. Is this behavior by design? It used up quite a few connections.
Thanks in advance.
Perhaps try adding WITH RECOMPILE to the sproc to ensure it gets appropriate
plan for all parameter combinations. Perhaps update statistics too, and
check indexes for fragmentation.
TheSQLGuru
President
Indicium Resources, Inc.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:352EC2C2-67DD-4C14-A9AB-58C4BE18FC80@.microsoft.com...
> We had an issue yesterday where a mess of async_network_io wait types
> started
> showing up in activity monitor (sql 2005). It turns out the optimizer was
> picking a bad execution plan for a certain report stored procedure and it
> was
> taking much longer than normal. We've fixed that issue, but my question
> was
> more of why we were seeing that wait type. My understanding of
> async_network_io is it indicates that SQL Server is waiting on sending
> data
> back to the client. It looks like stored procedure was taking long enough
> for
> the client app to time out. Eventually when the procedure finished, the
> connection just hangs with a wait type of async_network_io until I
> explicitly
> issue a Kill. Is this behavior by design? It used up quite a few
> connections.
> Thanks in advance.