ASYNC_NETWORK_IO Issue
Oct 5, 2006
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
View 1 Replies
Feb 20, 2008
We are having a problem with an SSIS package. The package gets stuck when it runs a data flow component that reads data from 5 large tables, performs a union on the data, does a conversion on one field and then writes the data to another table. This all happens in the same database and the ssis package runs on the same server as the database.
When I look at the waiting tasks on the sql server I always see the following type of scenario. It is always the ASYNC_NETWORK_IO that is causing the others to wait.
SPID
Wait type
duration
Waiting Address
Blocking Address
118
ASYNC_NETWORK_IO
47
0x0000000000C2FD68
NULL
118
CXPACKET
47
0x0000000000C2F2E8
0x0000000000C2FD68
118
CXPACKET
47
0x0000000000EB4DA8
0x0000000000C2FD68
118
CXPACKET
47
0x0000000000ECB048
0x0000000000C2FD68
118
CXPACKET
47
0x0000000000EE12E8
0x0000000000C2FD68
In some cases the duration column goes up to several hours until we finally just kill the package. At the same time, we also see that session 118 is consuming very large amounts of memory (in sys.dm_exec_query_memory_grants). However, when we look at the dtexec process that is running the package on the server it only has a working set of about 100Mb. We've tried rewriting the dataflow task as a simple stored procedure and this goes through smoothly.
Can anyone shed some light on what is going on and how to avoid this type of locking situation (other than just using plain old T-SQL).
View 1 Replies
View Related
Jun 8, 2006
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.
View 3 Replies
View Related
Oct 5, 2006
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?
Environment
SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.
Thanks
-Ashis
View 9 Replies
View Related
Mar 28, 2007
Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.
When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.
The query makes any sence or is there any way to write it in better way
'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'
'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'
We have been fighting it out for so many days.
Can anybody help
Thanks
knvdssr
View 5 Replies
View Related
