NETWORKIO?!?!

  • Thread starter Thread starter Jason McClellan
  • Start date Start date
J

Jason McClellan

Hello.. I have a database here which is used for purchasing/stock etc. It
is an Access front end, a very simple MDE with a few queries that people
run. Backend is SQL, access utilizes linked tables. The queries return all
records by default.

Recently, two events occurred; the server which SQL lives on was
re-installed with Windows 2003 server (SQL is version 2000 standard) and the
database exceeded 32,768 records.

Previously, everything worked fine. But now, I am seeing alot of lock
contention, PID's will have 'runnable' status, with command 'SELECT', Wait
Type 'NETWORKIO' and an ever-increasing Wait Time. This occurs only in a
certain condition; if a query is run, this lock is exhibited. The lock is
released as soon as they do something with the query, filter, edit
something, or even just go to the last record (with the > button). But
until something is done with it, this lock is held on the entire table, and
nobody else can edit/add etc anything. This occurs even if the person
running the query has read-only access.

My only/best guess is that for whatever reason, there is some kind of pause,
or timeout, or something occuring when access gets to the 32k records point,
and this puts a kaibosh on the query until the user forces it to do
something. I can't be 100% sure, but there was a time after the server was
reinstalled during which it did not do this, so I am more inclined to lean
towards the 32k theory, but I can't find any info on any possible causes..
in fact I can find extremely little info even on the NETWORKIO wait type.

I would appreciate any help/ideas/info anyone might have.
Thanks
Jason
 
PS

I also tried creating a stored procedure, and creating a passthrough query
in access to display it. This exhibits the exact same behaviour.

Thanks
 
Nobody knows anything?
:(
Jason McClellan said:
PS

I also tried creating a stored procedure, and creating a passthrough query
in access to display it. This exhibits the exact same behaviour.

Thanks
Click to expand...
 
Nothing, Marty! I have found numerous questions about this same issue
scattered about the internet, but not a single one has any real answers, no
ideas what the cause may be. Just "NETWORKIO means it's waiting for network
I/O" no-really? hmm.

This started after the server was upgraded to Windows 2003 server. SQL is
unchanged, the application is unchanged.

We currently are simply working around the problem, in our case, it only
does it when somebody runs a query, and then does nothing. The networkio
state is held until they perform some further action on the query results,
such as editing a record or filtering the results. Then, it is cleared and
things return to normal. So, people are just being careful to not run any
queries until they need to do something. Fortunately, in our application
this is but a minor annoyance.

My next plan is to install SQL on another server which is still running
Windows 2000 and move the database there and see if the problem goes away.

Jason
 
You're right...there are several people with the same problem, no
answers though.

I have found what's causing my problem, I just don't know how to fix
it.

I've got an Access 2000 database with a couple ODBC linked tables to
SQL server 2000 on a Windows 2000 server. In this database, I have a
form with 2 combo boxes that have the rowsources set to those ODBC
linked tables. That part all works fine.

Once I add a text box on the form with a control source that
references a column of those combo boxes (ie =[combo0].[column](1)),
that's when it takes a dump on me and as long as you are sitting on
that form, the connection to SQL Server hangs there with a NETWORKIO
state. Once you leave the form, everything works fine again. Your
database wouldn't happen to have textboxes with column references,
would it?

It's not a big deal until 2-3 people access that form at the same
time. Then SQL Server is totally unresponsive.

I'm not sure what I'm going to do about this. I've tried making the
combo boxes and queries read only. I've tried, unsuccessfully, to use
a stored procedure as the rowsource of my combobox...I've found out it
can't be done. I'm to the point where I think I need to code around
the problem. I just don't know HOW to get the same results as the
column reference that I have now. I better figure something out as I
use column references all over the place and as I move more of my
tables to SQL Server. I have a feeling this thing is going to bite me
again and again.

All I know is this issue has got me stumped, that's for sure.

Marty
 
Maybe it's waiting for user input for your text box or something like that?

My setup is almost the same. Access 2000 MDE frontend with 4 ODBC linked
tables to SQL 2000 running on Windows 2003 server.

We are not using any forms, reports or anything fancy at all. All there is
are a few queries, which return all records. People then use the filter by
form function to find what they need. This works fine for our needs. We
just reached about 32,000 records, so it's not a huge database.

My problems all started around the time the server was upgraded to 2003
server from 2000 server. However, around the same time, we also exceeded
32,768 records. I don't know the precise instant the problem started, since
nobody reported it right away. So, my obvious thoughts are that it either
has to do with 2003 server, or something related to my queries returning
over 32,768 records. But everything seems to work just fine, except this
NETWORKIO thing hanging things up.

This must be some kind of system-level thing, because I have found that the
locking occurs even when read-only users run the queries.

Marty Westra said:
You're right...there are several people with the same problem, no
answers though.

I have found what's causing my problem, I just don't know how to fix
it.

I've got an Access 2000 database with a couple ODBC linked tables to
SQL server 2000 on a Windows 2000 server. In this database, I have a
form with 2 combo boxes that have the rowsources set to those ODBC
linked tables. That part all works fine.

Once I add a text box on the form with a control source that
references a column of those combo boxes (ie =[combo0].[column](1)),
that's when it takes a dump on me and as long as you are sitting on
that form, the connection to SQL Server hangs there with a NETWORKIO
state. Once you leave the form, everything works fine again. Your
database wouldn't happen to have textboxes with column references,
would it?

It's not a big deal until 2-3 people access that form at the same
time. Then SQL Server is totally unresponsive.

I'm not sure what I'm going to do about this. I've tried making the
combo boxes and queries read only. I've tried, unsuccessfully, to use
a stored procedure as the rowsource of my combobox...I've found out it
can't be done. I'm to the point where I think I need to code around
the problem. I just don't know HOW to get the same results as the
column reference that I have now. I better figure something out as I
use column references all over the place and as I move more of my
tables to SQL Server. I have a feeling this thing is going to bite me
again and again.

All I know is this issue has got me stumped, that's for sure.

Marty

"Jason McClellan" <[email protected]> wrote in message
Nothing, Marty! I have found numerous questions about this same issue
scattered about the internet, but not a single one has any real answers, no
ideas what the cause may be. Just "NETWORKIO means it's waiting for network
I/O" no-really? hmm.

This started after the server was upgraded to Windows 2003 server. SQL is
unchanged, the application is unchanged.

We currently are simply working around the problem, in our case, it only
does it when somebody runs a query, and then does nothing. The networkio
state is held until they perform some further action on the query results,
such as editing a record or filtering the results. Then, it is cleared and
things return to normal. So, people are just being careful to not run any
queries until they need to do something. Fortunately, in our application
this is but a minor annoyance.

My next plan is to install SQL on another server which is still running
Windows 2000 and move the database there and see if the problem goes away.

Jason

type
of fix? message
passthrough
query purchasing/stock
etc.
It that
people standard)
and
the of
lock only
in
a The
lock
is button).
But kind
of
pause, inclined
to
lean possible
causes.. wait
type.
Click to expand...
Click to expand...
 
Back
Top