Performance degradation after 450 user connections.

I am seeing a strange behavior with my server. After the number of
concurrent connections reaches 450 and above, the performance of the
server drops significantly. 450 seems to be a magic number, anything
below it doesn't effect performance.
We have a large SQL 2k Ent environment that should be able to handle a
lot more users. Server has 16 processors, 7 gig of ram dedicated to
sql, and a fast EMC SAN. Cache hit ration is high, I/O normal, CPU
utilization at 35%.
The locking on the tables is minimal, all the queries use "nolock"
hint and we have no ad-hoc updates/inserts.
Application that connects to the db gets very slow however, and
connecting (opening a new window) through query analyzer takes up to a
minute.
None of the connections use significant amount of resources and
connection are closed properly.
What else should I be looking at?i am certain sql server ought to handle atleast 700-800
connections
what might be happening in your case is that too much
memory is allocated to buffers in the lower 3GB of address
space (memory >4GB is handled by PAE) that not enough
address space is left for network connections
try -g100 (where 100 is in MB) or some other values for
the SQL Server startup parameter
this can be done in Computer Management->Services on the
properties page for MSSQLSERVER at the bottom
you will need to stop the service first
>--Original Message--
>I am seeing a strange behavior with my server. After the
number of
>concurrent connections reaches 450 and above, the
performance of the
>server drops significantly. 450 seems to be a magic
number, anything
>below it doesn't effect performance.
>We have a large SQL 2k Ent environment that should be
able to handle a
>lot more users. Server has 16 processors, 7 gig of ram
dedicated to
>sql, and a fast EMC SAN. Cache hit ration is high, I/O
normal, CPU
>utilization at 35%.
>The locking on the tables is minimal, all the queries
use "nolock"
>hint and we have no ad-hoc updates/inserts.
>Application that connects to the db gets very slow
however, and
>connecting (opening a new window) through query analyzer
takes up to a
>minute.
>None of the connections use significant amount of
resources and
>connection are closed properly.
>What else should I be looking at?
>.
>|||I noticed that I have a lot of context switching, 5000/sec. The
minimum query plan threshold for switching to parrallel execution is
5, I suspect that this could be the cause of all the context
switching. 5 seems to be very low based on our queries.
I will try switching to fibers to see if this helps, but is there a
formula to figure out the parallel execution threshold based on the
CPU speed?|||Another thing to try is to see what the "max worker threads" setting is, via
sp_configure (it is an advanced option). The default setting of this option
is 255, in order to prevent thrashing. If your CPU utilization is pretty
low, you can try increasing this setting and see if it makes a difference.
Typically if there is a difference in response time as opposed to
throughput, it is possible it is because requests are waiting for a thread
to execute.
Something that was not clear from your mail is whether the slowdown happened
after all the connections are established, while just running queries, or
whether the slowdown was just in connect time. If the slowdown was just
during connect time, do you have NT authentication turned on ? There was a
bug in SQL Server (which has since been fixed) where if the DC was not
responding fast, SQL Server used to unnecessarily hold threads while doing
the logon. If you're not running on the latest QFE rollup, that would be
another thing to consider doing, and it should fix this particular issue if
that's what you're having.
S. Ashwin
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Eugene" <kyevgeniy@.hotmail.com> wrote in message
news:71740f98.0308150650.612af5e3@.posting.google.com...
> I noticed that I have a lot of context switching, 5000/sec. The
> minimum query plan threshold for switching to parrallel execution is
> 5, I suspect that this could be the cause of all the context
> switching. 5 seems to be very low based on our queries.
> I will try switching to fibers to see if this helps, but is there a
> formula to figure out the parallel execution threshold based on the
> CPU speed?