number of connections and performance

we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections at
pick times.
We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
The CPU usage averages around 6% with picks at 20-30% (no problem there)
Our RAM usage (Bytes Commited) keeps on increasing until it reaches the 2GB
limit (SQL server uses 1.7Gb)
I've been told that 100 to 200 connection was a lot but not enough to bug
the server down.
We have the opportunity to work on the main app connecting to that box to
bring the number of connections down (and we will do it no matter what) but
before we invest time in it we want to know if this could be the main thing
creating the performance issue.
What do you think?
Does the number of connection seem to be my problem here?
ThanksThere is obviously no issue with the cpu's and the ram usage is normal in
that it will continue to increase as long as there is newly read data and
there is ram left. 200 users should not be an issue in itself but if the
app is written poorly and you have lots of blocking it will only get worse
with more users. What are your disk queues and cache hit ratio like? Have
you checked for blocking?
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections
at
> pick times.
> We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> The CPU usage averages around 6% with picks at 20-30% (no problem there)
> Our RAM usage (Bytes Commited) keeps on increasing until it reaches the
2GB
> limit (SQL server uses 1.7Gb)
> I've been told that 100 to 200 connection was a lot but not enough to bug
> the server down.
> We have the opportunity to work on the main app connecting to that box to
> bring the number of connections down (and we will do it no matter what)
but
> before we invest time in it we want to know if this could be the main
thing
> creating the performance issue.
> What do you think?
> Does the number of connection seem to be my problem here?
> Thanks
>|||in many places we were retrieving data from the DB using datareaders but we
are changing that to return datasets instead... that should reduce the
number of locks (I think)
What do you mean by blocking? Did you mean DB locks?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> There is obviously no issue with the cpu's and the ram usage is normal in
> that it will continue to increase as long as there is newly read data and
> there is ram left. 200 users should not be an issue in itself but if the
> app is written poorly and you have lots of blocking it will only get worse
> with more users. What are your disk queues and cache hit ratio like?
Have
> you checked for blocking?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > we have a SQL Adv Server 2000 box with 100 to 200 concurrent connections
> at
> > pick times.
> > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > The CPU usage averages around 6% with picks at 20-30% (no problem there)
> > Our RAM usage (Bytes Commited) keeps on increasing until it reaches the
> 2GB
> > limit (SQL server uses 1.7Gb)
> >
> > I've been told that 100 to 200 connection was a lot but not enough to
bug
> > the server down.
> > We have the opportunity to work on the main app connecting to that box
to
> > bring the number of connections down (and we will do it no matter what)
> but
> > before we invest time in it we want to know if this could be the main
> thing
> > creating the performance issue.
> >
> > What do you think?
> > Does the number of connection seem to be my problem here?
> >
> > Thanks
> >
> >
>|||Blocking is fundamentally caused by incompatible locks. For instance, if one
user is in the middle of updating a row, another user trying to update the
same row would have to wait. The second user is said to be blocked by the
first user. Blocking is normal in a relational database, and is the price
you pay to get the appearance of concurrency. But sustained blocking can
degrade the user response time, is often (not always) a sign of poor design,
and should be avoided.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> in many places we were retrieving data from the DB using datareaders but
we
> are changing that to return datasets instead... that should reduce the
> number of locks (I think)
> What do you mean by blocking? Did you mean DB locks?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > There is obviously no issue with the cpu's and the ram usage is normal
in
> > that it will continue to increase as long as there is newly read data
and
> > there is ram left. 200 users should not be an issue in itself but if
the
> > app is written poorly and you have lots of blocking it will only get
worse
> > with more users. What are your disk queues and cache hit ratio like?
> Have
> > you checked for blocking?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
connections
> > at
> > > pick times.
> > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > The CPU usage averages around 6% with picks at 20-30% (no problem
there)
> > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
the
> > 2GB
> > > limit (SQL server uses 1.7Gb)
> > >
> > > I've been told that 100 to 200 connection was a lot but not enough to
> bug
> > > the server down.
> > > We have the opportunity to work on the main app connecting to that box
> to
> > > bring the number of connections down (and we will do it no matter
what)
> > but
> > > before we invest time in it we want to know if this could be the main
> > thing
> > > creating the performance issue.
> > >
> > > What do you think?
> > > Does the number of connection seem to be my problem here?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||A datareader is pretty efficient and I doubt you will get less locks by
moving from them if you are using them correctly. Is this a read only
database? It is usually not the reads that block others but more the
updates. How are you updating the data?
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> in many places we were retrieving data from the DB using datareaders but
we
> are changing that to return datasets instead... that should reduce the
> number of locks (I think)
> What do you mean by blocking? Did you mean DB locks?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > There is obviously no issue with the cpu's and the ram usage is normal
in
> > that it will continue to increase as long as there is newly read data
and
> > there is ram left. 200 users should not be an issue in itself but if
the
> > app is written poorly and you have lots of blocking it will only get
worse
> > with more users. What are your disk queues and cache hit ratio like?
> Have
> > you checked for blocking?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
connections
> > at
> > > pick times.
> > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > The CPU usage averages around 6% with picks at 20-30% (no problem
there)
> > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
the
> > 2GB
> > > limit (SQL server uses 1.7Gb)
> > >
> > > I've been told that 100 to 200 connection was a lot but not enough to
> bug
> > > the server down.
> > > We have the opportunity to work on the main app connecting to that box
> to
> > > bring the number of connections down (and we will do it no matter
what)
> > but
> > > before we invest time in it we want to know if this could be the main
> > thing
> > > creating the performance issue.
> > >
> > > What do you think?
> > > Does the number of connection seem to be my problem here?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||this is not a read only DB. We are updating the data using stored procedures
accessed from the application.
How can I find out if there is a lot of blocking using the Performance
monitor?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:enqScqerDHA.2568@.TK2MSFTNGP09.phx.gbl...
> A datareader is pretty efficient and I doubt you will get less locks by
> moving from them if you are using them correctly. Is this a read only
> database? It is usually not the reads that block others but more the
> updates. How are you updating the data?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> > in many places we were retrieving data from the DB using datareaders but
> we
> > are changing that to return datasets instead... that should reduce the
> > number of locks (I think)
> >
> > What do you mean by blocking? Did you mean DB locks?
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > > There is obviously no issue with the cpu's and the ram usage is normal
> in
> > > that it will continue to increase as long as there is newly read data
> and
> > > there is ram left. 200 users should not be an issue in itself but if
> the
> > > app is written poorly and you have lots of blocking it will only get
> worse
> > > with more users. What are your disk queues and cache hit ratio like?
> > Have
> > > you checked for blocking?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
> connections
> > > at
> > > > pick times.
> > > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > > The CPU usage averages around 6% with picks at 20-30% (no problem
> there)
> > > > Our RAM usage (Bytes Commited) keeps on increasing until it reaches
> the
> > > 2GB
> > > > limit (SQL server uses 1.7Gb)
> > > >
> > > > I've been told that 100 to 200 connection was a lot but not enough
to
> > bug
> > > > the server down.
> > > > We have the opportunity to work on the main app connecting to that
box
> > to
> > > > bring the number of connections down (and we will do it no matter
> what)
> > > but
> > > > before we invest time in it we want to know if this could be the
main
> > > thing
> > > > creating the performance issue.
> > > >
> > > > What do you think?
> > > > Does the number of connection seem to be my problem here?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||You can look at the lock counters to see how many locks you have but your
better off using something like
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
to see who is blocking who.
--
Andrew J. Kelly
SQL Server MVP
"Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
news:OYWVthhrDHA.2676@.TK2MSFTNGP11.phx.gbl...
> this is not a read only DB. We are updating the data using stored
procedures
> accessed from the application.
> How can I find out if there is a lot of blocking using the Performance
> monitor?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:enqScqerDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > A datareader is pretty efficient and I doubt you will get less locks by
> > moving from them if you are using them correctly. Is this a read only
> > database? It is usually not the reads that block others but more the
> > updates. How are you updating the data?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > news:OwsHL9XrDHA.1444@.tk2msftngp13.phx.gbl...
> > > in many places we were retrieving data from the DB using datareaders
but
> > we
> > > are changing that to return datasets instead... that should reduce the
> > > number of locks (I think)
> > >
> > > What do you mean by blocking? Did you mean DB locks?
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:%23iFKh1XrDHA.1084@.tk2msftngp13.phx.gbl...
> > > > There is obviously no issue with the cpu's and the ram usage is
normal
> > in
> > > > that it will continue to increase as long as there is newly read
data
> > and
> > > > there is ram left. 200 users should not be an issue in itself but
if
> > the
> > > > app is written poorly and you have lots of blocking it will only get
> > worse
> > > > with more users. What are your disk queues and cache hit ratio
like?
> > > Have
> > > > you checked for blocking?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Benoit Martin" <bmartin_hpu@.hotmail.com> wrote in message
> > > > news:%23ZiXUnWrDHA.2488@.TK2MSFTNGP09.phx.gbl...
> > > > > we have a SQL Adv Server 2000 box with 100 to 200 concurrent
> > connections
> > > > at
> > > > > pick times.
> > > > > We have some performance issue on that box (P4 2.6GHz, 2GB RAM).
> > > > > The CPU usage averages around 6% with picks at 20-30% (no problem
> > there)
> > > > > Our RAM usage (Bytes Commited) keeps on increasing until it
reaches
> > the
> > > > 2GB
> > > > > limit (SQL server uses 1.7Gb)
> > > > >
> > > > > I've been told that 100 to 200 connection was a lot but not enough
> to
> > > bug
> > > > > the server down.
> > > > > We have the opportunity to work on the main app connecting to that
> box
> > > to
> > > > > bring the number of connections down (and we will do it no matter
> > what)
> > > > but
> > > > > before we invest time in it we want to know if this could be the
> main
> > > > thing
> > > > > creating the performance issue.
> > > > >
> > > > > What do you think?
> > > > > Does the number of connection seem to be my problem here?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>