Queries going to Suspend state with wait type ASYNC_NETWORK_IO

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

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.