NETWORKIO slowing procedure?
Posted on 2003-12-09
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).