MSDE / SQL Express Question

what is the limitation in terms of connections? I've read something along
the lines of 5-10 connections?MSDE had a 5 concurrent connection 'governor. With SQL Server 2005 Express,
the governor has been removed, and the limit is mostly related to the type
of activity and hardware. Of course, there will be some relationship with
and processor/memory/disk subsystem -up to the limits.
With low to moderate frequency activity (small packets) in a small OLTP
database, you might be able to adequately support 20-30 users. Large
resultsets in a OLAP may only support 1-5 users. Plan to thoroughly test any
implementation.
See specifications at:
http://www.microsoft.com/sql/prodin...e-features.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"g" <gregoranton_nospamplease_@.hotmail.com> wrote in message
news:8pIPg.24564$Lb5.16987@.edtnps89...
> what is the limitation in terms of connections? I've read something along
> the lines of 5-10 connections?
>|||On Tue, 19 Sep 2006 02:15:32 GMT, g wrote:

>what is the limitation in terms of connections? I've read something along
>the lines of 5-10 connections?
>
Hi g,
In addition to Arnie's answer: the MSDE workload governor limits
concurrent _WORKLOADS_, not connections. An idle connection is not a
workload, a working connection is. Some connections might even spawn
multiple workloads. So ten, twenty, fifty or even hundred data entry
typists coould all simultaneously be connected to MSDE without ever
hitting the 5-workload limit. But one carefully architected procedure
that spawns multiple connections (not easy to do, but possible) could
bring MSDE to it's knees all by itself.
Oh, and if you surpass the maximum of 5 concurrent workloads, the effect
will be that the workload governor starts to throttle performance. A bit
if you have only 6 workloads, but progressively more as the number of
workloads increases - effectively, MSDE will slow down to an almost halt
if you keep increasing concurrent workloads.
Hugo Kornelis, SQL Server MVP

MSDE / SQL Express Question

what is the limitation in terms of connections? I've read something along
the lines of 5-10 connections?
MSDE had a 5 concurrent connection 'governor. With SQL Server 2005 Express,
the governor has been removed, and the limit is mostly related to the type
of activity and hardware. Of course, there will be some relationship with
and processor/memory/disk subsystem -up to the limits.
With low to moderate frequency activity (small packets) in a small OLTP
database, you might be able to adequately support 20-30 users. Large
resultsets in a OLAP may only support 1-5 users. Plan to thoroughly test any
implementation.
See specifications at:
http://www.microsoft.com/sql/prodinf...-features.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"g" <gregoranton_nospamplease_@.hotmail.com> wrote in message
news:8pIPg.24564$Lb5.16987@.edtnps89...
> what is the limitation in terms of connections? I've read something along
> the lines of 5-10 connections?
>
|||On Tue, 19 Sep 2006 02:15:32 GMT, g wrote:

>what is the limitation in terms of connections? I've read something along
>the lines of 5-10 connections?
>
Hi g,
In addition to Arnie's answer: the MSDE workload governor limits
concurrent _WORKLOADS_, not connections. An idle connection is not a
workload, a working connection is. Some connections might even spawn
multiple workloads. So ten, twenty, fifty or even hundred data entry
typists coould all simultaneously be connected to MSDE without ever
hitting the 5-workload limit. But one carefully architected procedure
that spawns multiple connections (not easy to do, but possible) could
bring MSDE to it's knees all by itself.
Oh, and if you surpass the maximum of 5 concurrent workloads, the effect
will be that the workload governor starts to throttle performance. A bit
if you have only 6 workloads, but progressively more as the number of
workloads increases - effectively, MSDE will slow down to an almost halt
if you keep increasing concurrent workloads.
Hugo Kornelis, SQL Server MVP

MSDE / SQL Express Question

what is the limitation in terms of connections? I've read something along
the lines of 5-10 connections?MSDE had a 5 concurrent connection 'governor. With SQL Server 2005 Express,
the governor has been removed, and the limit is mostly related to the type
of activity and hardware. Of course, there will be some relationship with
and processor/memory/disk subsystem -up to the limits.
With low to moderate frequency activity (small packets) in a small OLTP
database, you might be able to adequately support 20-30 users. Large
resultsets in a OLAP may only support 1-5 users. Plan to thoroughly test any
implementation.
See specifications at:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"g" <gregoranton_nospamplease_@.hotmail.com> wrote in message
news:8pIPg.24564$Lb5.16987@.edtnps89...
> what is the limitation in terms of connections? I've read something along
> the lines of 5-10 connections?
>|||On Tue, 19 Sep 2006 02:15:32 GMT, g wrote:
>what is the limitation in terms of connections? I've read something along
>the lines of 5-10 connections?
>
Hi g,
In addition to Arnie's answer: the MSDE workload governor limits
concurrent _WORKLOADS_, not connections. An idle connection is not a
workload, a working connection is. Some connections might even spawn
multiple workloads. So ten, twenty, fifty or even hundred data entry
typists coould all simultaneously be connected to MSDE without ever
hitting the 5-workload limit. But one carefully architected procedure
that spawns multiple connections (not easy to do, but possible) could
bring MSDE to it's knees all by itself.
Oh, and if you surpass the maximum of 5 concurrent workloads, the effect
will be that the workload governor starts to throttle performance. A bit
if you have only 6 workloads, but progressively more as the number of
workloads increases - effectively, MSDE will slow down to an almost halt
if you keep increasing concurrent workloads.
--
Hugo Kornelis, SQL Server MVP

MSDE # connections

Hello All-
We are doing some performance monitoring of clients. We are running merge
replication ( push) on SQL Server 2000. Clients use MSDE.
We notice that merge replication is taking 5 connections to the client. I
thought that MSDE had a max 5 concurrent connections and then adds a
performance penalty when above 5. Our concern is that when our app connects
to MSDE, we are getting a big performance hit.
Is it valid for merge replication to take 5 connections? We verified this
number by monitoring and then stopping & restarting merge agent. The number
went from 7 ( merge agent at central db host enabled ) to 2 connections (
merge agent stopped).
Thanks,
bruce
actually its 8 simultaneous connections. Subsequent connections are
throttled. However if you run sp_who2, you will find that many of these
connections are sleeping, which means that they are not doing work.
So the short answer to your question is that you should not experience a
significant performance hit as long as you have only a few clients
connecting.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:b79Nc.104089$a92.87050@.twister.nyc.rr.com...
> Hello All-
> We are doing some performance monitoring of clients. We are running merge
> replication ( push) on SQL Server 2000. Clients use MSDE.
> We notice that merge replication is taking 5 connections to the client. I
> thought that MSDE had a max 5 concurrent connections and then adds a
> performance penalty when above 5. Our concern is that when our app
connects
> to MSDE, we are getting a big performance hit.
> Is it valid for merge replication to take 5 connections? We verified
this
> number by monitoring and then stopping & restarting merge agent. The
number
> went from 7 ( merge agent at central db host enabled ) to 2 connections (
> merge agent stopped).
> Thanks,
> bruce
>
>