Connections

I've got a third party download app that creates ten connections to Sql
Server 2000 per user! We've got an average of 40 users/day and so we have
around 400 connections floating around all day just for this one small app!
We asked the vendor and all they will say is "it's working as designed".
So here are my questions:
1. Is there ANY reason on planet earth, performance or otherwise, that
someone would require ten connections?!?
2. How can I get it across that this is unacceptable? How can I calculate
how many connections Sql Server can handle? Are there any rules of thumb
that I can use for leverage?
Thx.CLM wrote:
> I've got a third party download app that creates ten connections to
> Sql Server 2000 per user! We've got an average of 40 users/day and
> so we have around 400 connections floating around all day just for
> this one small app! We asked the vendor and all they will say is
> "it's working as designed".
> So here are my questions:
> 1. Is there ANY reason on planet earth, performance or otherwise,
> that someone would require ten connections?!?
Possibly, but not likely.
> 2. How can I get it across that this is unacceptable? How can I
> calculate how many connections Sql Server can handle? Are there any
> rules of thumb that I can use for leverage?
>
Maybe you can turn on connection pooling in control panel if the app is
using ODBC connections to SQL Server. However, you may need to enable
connection pooling in the application using SQLSetEnvAttr and you
probably can't do this. ADO.Net provides connection pooling
automatically.
Maybe you can find out from the vendor if they support connection
pooling. Then send or email someone high up at the company (like the
CTO) your concerns about the product and the way it uses connections
very liberally.
Each connection consumes 12 KB + (3 * Network Packet Size). So, even
with 400 connections using a 4K packet size, you're only consuming about
10MB of memory.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
Server will start to go over 2 connections per thread and resources will get
shared.
Plus, in addition to what you wrote, isn't Sql Server using some cpu
resources to manage these 400 inactive connections'
"David Gugick" wrote:
> CLM wrote:
> > I've got a third party download app that creates ten connections to
> > Sql Server 2000 per user! We've got an average of 40 users/day and
> > so we have around 400 connections floating around all day just for
> > this one small app! We asked the vendor and all they will say is
> > "it's working as designed".
> >
> > So here are my questions:
> >
> > 1. Is there ANY reason on planet earth, performance or otherwise,
> > that someone would require ten connections?!?
> Possibly, but not likely.
> > 2. How can I get it across that this is unacceptable? How can I
> > calculate how many connections Sql Server can handle? Are there any
> > rules of thumb that I can use for leverage?
> >
> Maybe you can turn on connection pooling in control panel if the app is
> using ODBC connections to SQL Server. However, you may need to enable
> connection pooling in the application using SQLSetEnvAttr and you
> probably can't do this. ADO.Net provides connection pooling
> automatically.
> Maybe you can find out from the vendor if they support connection
> pooling. Then send or email someone high up at the company (like the
> CTO) your concerns about the product and the way it uses connections
> very liberally.
> Each connection consumes 12 KB + (3 * Network Packet Size). So, even
> with 400 connections using a 4K packet size, you're only consuming about
> 10MB of memory.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||CLM wrote:
> Yes, but I've been reading that if Sql Server goes over 255 threads,
> Sql Server will start to go over 2 connections per thread and
> resources will get shared.
If they're mostly idle, then you probably won't reach the default of 255
worker threads. Even if you do (it would require about 6+ active batches
run on each of your 40 clients), SQL Server will temporarily have
batches wait until a worker thread is available for use. From BOL:
"Having all worker threads allocated does not mean that the performance
of SQL Server will degrade. Typically, a new batch has only a short wait
for a free thread. Allocating more threads may degrade performance
because of the increased work required to coordinate resources among the
threads. Many SQL Server systems running in production reach this state
and run with very high performance levels."
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections'
Not much if they are inactive.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:7A9AD5AD-E23C-469F-B8C6-0124D2D2788C@.microsoft.com...
> Yes, but I've been reading that if Sql Server goes over 255 threads, Sql
> Server will start to go over 2 connections per thread and resources will
> get
> shared.
That's not true. Or rather, it's not relavent. A connection is assigned
not to a thread, but to a scheduler. Each scheduler has a pool of workers
(threads) to carry out work. So there is not a 1-1 relationship between the
number of connections and the number of threads on the server.
> Plus, in addition to what you wrote, isn't Sql Server using some cpu
> resources to manage these 400 inactive connections'
No.
David