Re: NETWORKIO?!?!

From: Jason McClellan (jason_mcc_at_obsfucated.myrealbox.com)
Date: 04/30/04 

Date: Thu, 29 Apr 2004 23:51:47 -0400

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" <marty@electronicsi.com> wrote in message
news:fdf601a2.0404290543.2574127c@posting.google.com...
> 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" <jason_mcc@obsfucated.myrealbox.com> wrote in message
news:<#AgQUO$KEHA.3292@TK2MSFTNGP11.phx.gbl>...
> > 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
> >
> > "Marty Westra" <marty@electronicsi.com> wrote in message
> > news:fdf601a2.0404260633.679f40ba@posting.google.com...
> > > I know that I'm having the same problem. Have you come up with any
type
> > of fix?
> > >
> > > "Jason McClellan" <jason_mcc@obsfucated.myrealbox.com> wrote in
message
> > news:<eOo6QeFHEHA.912@TK2MSFTNGP12.phx.gbl>...
> > > > Nobody knows anything?
> > > > :(
> > > > "Jason McClellan" <jason_mcc@obsfucated.myrealbox.com> wrote in
message
> > > > news:OQZDGf$GEHA.548@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > > "Jason McClellan" <jason_mcc@obsfucated.myrealbox.com> wrote in
> > message
> > > > > news:%23HXbmd$GEHA.3776@tk2msftngp13.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >