Sandra Mace


I have a series of processes being blocked - after running sp_blocker_pss80 I can see that the blocking spid is coming from a batch program that is issuing a 'sp_prepexec' event and has a waittype of NETWORKIO.

It looks very like the problem fixed by hotfix: 884554 in SP4. The db is SQLServer 2000 and running the following command:

SELECT SERVERPROPERTY('ProductLevel')

GO

Gives the result

SP4

So I guess I have the hotfix but I'm still experiencing this problem.

The event that is 'blocking' is a select statement on a large table. Running the same batch process on identical data in a test environment doesn't give the same problem. I'm not certain that other processing on the test environment is identical though so it isn't an identical comparison.

I am able to see that there is a second spid coming from the batch program waiting for the first blocking spid to complete (I guess) before the second spid can start - the second spid is a lock type LCK_M_IX and wants to update the same table that the blocking spid is selecting from. It is this second spid that then causes a pile-up of other spid's behind it from other programs that want to do updates and inserts to the same table.

Why does a waittype of NETWORKIO occur if the client is a batch program, i.e. the response between db and client should be automatic, there is no user intervention holding things up

Any ideas

I have the output from the blocker procedure if anyone wants more info.




Re: waittype NETWORKIO blocking spid on SQLServer 2000

Jerome Halmans


The NETWORKIO waittype is used when a connection's output buffer is full, and SQL Server is waiting on it to be drained to the network. A wait on NETWORKIO generally indicates that the client program fetching the data from SQL Server is not doing so fast enough.

The long blocking chains you see are a side effect of the first client. While scanning a table locks are held during the scan, and while SQL Server waits for the client to drain the output buffer it must hold a lock at the current scan location. When your second connection comes in to update it gets blocked waiting on the first connection to release it's lock.

You should start by determining what the first connection is doing, and why it isn't fetching data more quickly from the server.







Re: waittype NETWORKIO blocking spid on SQLServer 2000

Sandra Mace

Thanks for the explanation. Here is the blocking SPID's command that was trapped:

exec sp_prepexec @P1 output, NULL, N'SELECT t593.* FROM main_table t593 WHERE t593.acct_id = 4401 AND t593.state = 3 ORDER BY t593.amount ASC,t593.department ASC, t593.value_date ASC'

Select @P1

The above statements are part of a batch program.

When this same statement is run through SQL Query Analyzer it runs through to completion ok and returns approx 59,000 rows but it does take 2.5 minutes. This data is coming from a very large table.

Are there any configuration settings I can manipulate to increase the size of the output buffer or is it really a matter of increasing the speed of the query Because it runs ok in the test environment with the same indexes I wonder what else could be affecting it.






Re: waittype NETWORKIO blocking spid on SQLServer 2000

Balmukund - MSFT

There is no way you can increase the size of buffer. Probably you need to find out the place in applicaiton from where this query is getting fired and look at way the result set is handled. Speed of the query execution may not matter unless you handle the recordset properly from client.

You can easily get NETWORKIO waittype using Enterprise Manager by opening a BIG table (return all rows)





Re: waittype NETWORKIO blocking spid on SQLServer 2000

Sandra Mace

Will look at the client program to try to find out how it handles the result set but I still don't understand why the same problem isn't encountered in the test environment when the same data is processed by the same batch program. This is why I wondered if there were some configuration settings that may differ. Any ideas





Re: waittype NETWORKIO blocking spid on SQLServer 2000

Jerome Halmans

There would not be a server side config setting that would result in long NETWORKIO waits.

The problem is very likely in the client program.

Investigating the differences in the production/test servers won't resolve the client issue, but if you do want to pursue that angle here¡¯s some things to look at:

How long does the query run on Test compared to Production

How many rows are returned on each

Are the execution plans identical

Is there a lock escalation

Is the load on both servers the same






Re: waittype NETWORKIO blocking spid on SQLServer 2000

TRACEYMS

The wait stats
wait type networkio
NETWORKIO0x800This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.

I have some really large values in this NETWORKIO

Does this mean that the SQL is waiting on the network outside

All our Data is on our SAN.

Each minute i have network wait seconds 115.43 and
another time i have One hour i have 5082.58

I asked our network guys if there was something with the network and they asking me to give them the meaning of the NETWORKIO and why is SQL telling us this...what direction is it waiting on etc

Is there Any other thing i can run in SQL to identify the problem



Thanks






Re: waittype NETWORKIO blocking spid on SQLServer 2000

Jerome Halmans

Some amount of networkio waits will be normal. Networkio waits are an indicator that SQL Server is producing results faster than the client is consuming them. When all network output buffers for a connection are full SQL Server must wait until a buffer is drained to continue producing results. That wait time is tracked by the networkio wait type.

Before worrying about a specific wait type evaluate whether there is a specific problem you are trying to resolve.

Are your clients experiencing slowdowns because they can't get results fast enough, and if so what does "fast enough" mean If so are any of the client spids waiting on networkio at the time of the slowdown

Do you see long blocking chains where the lead blocker is waiting on networkio






Re: waittype NETWORKIO blocking spid on SQLServer 2000

TRACEYMS

Thanks very much for replying.

Example of some processes

Each minute i have network wait seconds 115.43 and
another time i have One hour i have 5082.58

If i look in the current activity locks processes third one down which shows you the table....i will see probrably 100 or so entries for one table...in here

Users actually time out....on the transactions.

When you mention the network output buffers (is this inside of SQL Buffer or outside).

Yes i see very long blocking chains...i see a lot of S for the locks...nothing blocked






Re: waittype NETWORKIO blocking spid on SQLServer 2000

TRACEYMS

SP4 is installed SQL 2000




Re: waittype NETWORKIO blocking spid on SQLServer 2000

Chris Howarth

We have the same problem with a client app that is based on MS Access 2000 - the problem occurs even when the data has been successfully returned to the client.

As we don't have access to the client's source code, and couldn't find a fix anywhere, we wrote a SQL Agent job to regularly execute sp_who2 and kill any rogue connections showing the NETWORKIO symptoms and meeting other criteria. Although not an ideal solution, by any stretch, it has removed the issues that the connections was causing (in addition to blocking we were also seeing the queried database's log file steadily filling up).

Chris

 






Re: waittype NETWORKIO blocking spid on SQLServer 2000

Jerome Halmans

Chris, this is a somewhat common problem when using Access to retrieve data from SQL Server. Access does not retrieve all rows from the resultset until the user moves to the last record in the recordset. Using linked tables is a rather common cause of the problem you are seeing. Opening a linked table executes a "Select * from table" query, but only enough rows to fill the client's screen are retrieved. Thus leaving the connection holding shared locks and waiting on networkio.

The workaround from the app is to simply scroll to the end of the resultset, thus consuming all pending results and completing the query.






Re: waittype NETWORKIO blocking spid on SQLServer 2000

Chris Howarth

Thanks for the info - I didn't realise that this was the cause.

I guess the problem that we would face would be training the users to do this every time they open the relevant form, particularly as they would see no negative effects as a result of their actions. It's just unfortunate that we don't have access to the source code (the app was written by a contractor some years back before any real control was enforced within the IT department).

Thanks again

Chris