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.

ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types starte
d
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 wa
s
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 fo
r
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 explicitl
y
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.

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.

async_network_io wait during replication

I have re-initialize some subscription. After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.' and I found the replication process is under 'async_network_io wait'. The job has run for more than 8 hrs. Any idea to improve it? Thanks in advaise.

Are you running the sync agent (like merge agent/distribution agent) in continuous mode? If yes, it will show running forever, although the submitted transactions/commands have been replicated, agent job is just keeping waiting for next actions.

Thanks

-Yunjing

|||Yes, in continuous mode. But the replication hadn't completed in 8 hrs. Any idea? Thanks|||

If an continuous sync complete for a session, its current history log usually shows something like "No replicated transactions are available" and sync job is still showing as running.

As for your case, could you open sqlmonitor to check the current status for the job, is it still running or fail already? If it's running, what's the current history shown? Does the history get updated every a few minutes?

-Yunjing

ASYNC_NETWORK_IO issue

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.

Thanks
-Ashis

Hi, Ashis,

Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.

Thanks,

Zhiqiang Feng

ASYNC_NETWORK_IO

Hello, When I execute a SQL SERVER 2005 stored procedure from Visual Basic 6 Application, I get my process locked with ASYNC_NETWORK_IO wait type.

When I Execute the same procedure from sql server console, It doesn't occur...

This is the visual basic code:

With objcommand

.ActiveConnection = CnnConexionPpalDb
.CommandTimeout = 0
.CommandText = "EJECUTIN"
.CommandType = adCmdStoredProc

'- Parametros del Command

'.Parameters("@.StrOrdenEstablecido").Value = StrOrdenEstablecido

End With
Set gTbl.VisOrdenamientoDatosFinalCiudades = objcommand.Execute

With gTbl.VisOrdenamientoDatosFinalCiudades

This is SQL SERVER CODE:

ALTER PROCEDURE [dbo].PCN_CONSULTAR_VISORDENAMIENTODATOSFINALCIUDADES_ORDENADO] @.StrOrdenEstablecido varchar(200)
as
BEGIN TRAN
DECLARE @.lvaSentencia NVARCHAR(4000)

SELECT dbo.Tbl_DatosSubidos.Direccion, dbo.Tbl_DatosSubidos.Ciudad, dbo.Tbl_DatosSubidos.Departamento,
dbo.Tbl_DatosSubidos.Inserto, dbo.Tbl_DatosSubidos.IndiceOLlaveUnica, dbo.Tbl_DatosSubidos.Identificacion,
dbo.Tbl_DetalleCiudadesBuenas.IdArchivo, dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes,
dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytesFinalDato, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Dep_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dep_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.DatoIngresado, dbo.Tbl_DetalleCiudadesBuenas.Dis_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dis_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.Doc_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Doc_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Cli_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Cli_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.DCB_Novedad,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Georreferenciada, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Prioridad,
dbo.Tbl_DetalleCiudadesBuenas.Geo_CodigoZona, dbo.Tbl_DetalleCiudadesBuenas.DatoCiudadCompleta,
dbo.Tbl_DetalleCiudadesBuenas.Tipo_Distribucion, dbo.Tbl_DatosSubidos.Factura, dbo.Tbl_DatosSubidos.Nombre,
dbo.Tbl_ArchivosSubidos.Ars_NombreArchivoSubido AS NombreArchivo
FROM dbo.Tbl_DatosSubidos WITH (NOLOCK) INNER JOIN
dbo.Tbl_ArchivosSubidos WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_ArchivosSubidos.Ars_Codigo INNER JOIN
dbo.Tbl_DetalleCiudadesBuenas WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_DetalleCiudadesBuenas.IdArchivo AND
dbo.Tbl_DatosSubidos.PosicionEnBytes = dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes

ORDER BY 1
COMMIT

This wait type means the server is busy sending rows to the client fo processing. The best way to fix it is to reduce the number of rows returned. Alternatively, client-side processing should be accelerated. Or both.

-Ryan / Kardax

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-Eric
The fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) but the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use â'Localhostâ' rather than the DNS name
(thinking that it might have to ask the DNS server what itâ's name is) but the
process will still get the same error.
Iâ'm confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:
> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use â'Localhostâ' rather than the DNS name
> (thinking that it might have to ask the DNS server what itâ's name is) but the
> process will still get the same error.
> Iâ'm confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>sql

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_I
O
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a proces
s
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but
the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among al
l
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, yo
u
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network
_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID
5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes fr
om
> one table and inserts/updates another. This is the one that starts a proc
ess
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) bu
t the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>