Solved

NETWORKIO slowing procedure?

Posted on 2003-12-09
4
1,655 Views
Last Modified: 2010-05-18
I have a procedure that queries data from a transaction table and updates a warehouse table. The select on the transaction table is "with (nolock)". As I am running it, I have had a NETWORKIO lock on this process the entire time, with wait time about 140 ms. Not a long time, but there seems to be no reason for the locking, and nothing is on the "network" - process is running as a job on the server itself.

The process started processing about 19 records per second and is now down to 14, so I believe the problem is causing the process to slow down. (I do my monitoring query with a nolock as well, so I am assuming this is not causing the problem).

Table being updated is brand new and clustered index that supports the update is there (update is on a 2-column concatenated key which is not being updated by the update statement in question). Stats seem to be there.  Plenty of LOG and TEMPDB space (GBs worth - table being updated is only 600,000 rows). 4CPU box.

This NETWORKIO lock problem has occurred regularly on this system but has usually gone away. This procedure seems to cause that lock to occur consistently. Stopping and starting the procedure did not help - it locks up after a few seconds, processing as few as 10 records per minute.

I have tried MAXDOP 1 to keep the process from locking itself up (old SQL bug, supposedly fixed).

Any suggestions?

Thanks,

Aaron
0
Comment
Question by:AaronAbend
  • 2
4 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 9906797
Generally, the wait type NETWORKIO occurs anytime SQL Server has to wait while sending results back to a client. However, this does not appear to be the problem, unless you are updating tables, or updating from tables, stored physically somewhere else on your network (Remote DB).

You should look at PERFMON's Network activity and similar things to see what sort of Network I/O is happening here...  any network access to a remote resource (not necessarily the transaction or warehouse tablee) should be looked at suspiciously...
0
 
LVL 10

Author Comment

by:AaronAbend
ID: 9906956
This is not easy since the server is located offsite and I cannot always get access via PCAnywhere, but I will try.  

0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 9908500
"I have a procedure that queries data from a transaction table and updates a warehouse table"

What's the procedure?  DTS?  What runs this?  Like bhess1 stated above, it could be the client waiting on a response.  Just because all the process runs on the server, doesn't mean there isn't a client involved.  If you have stored procs, make sure you have the SET NOCOUNT on as the first line--this will also cause you to see networkio waits.
0
 
LVL 10

Author Comment

by:AaronAbend
ID: 9912029
For completeness -- The procedure is a TSQL procedure that is run as a job (which has one step - running this procedure with "exec myprocedure"). I start the job by accessing the server (located off site) via Enterprise Manager running on my machine.

I added NOCOUNT. I had wanted feedback during testing and had forgot to put it in. That changed the problem. Now I am seeing occasional PAGEIOLATCH_SH locks (which DOES make sense since there is another process accessing one of the tables).

I have been watching it for 10 minutes and have not seen the NETWORKIO error. Thanks, arbert.

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL SELECT query help 7 41
insert wont work in SQL 14 22
SQL View nearest date 5 36
Need help constructing a conditional update query 16 47
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question