Connection pooling in SQL SERVER (express)- recommended amount ?

Question

Hi there,

is there a recommended amount of connections fro each application i have for use with connection pooling.., my apps are using asp.net and c# against sql express on the "same" server.

I have 5 applications running, they are not used intensively, all connections are opened and closed..

So i was thinking of setting each app to have min pool = 5

so this 5 x 5 = 25

Can sql express handle 25 connection ... well actually 5 pools and 5 connections each pool?

Could i go up to ten? And is it necessary to put MAX pool property in the connection string to ensure i don't go over a number per pool?

Any help really apprecaited

Answer 1

It will depend on what version of sql server you're using, but for SQL Server 2005 express there is no limitation on connections, so you should be fine.

The limitations are indicated here

Answer 2

The number of connections does matter little. You can let the connection pool size at the default 100.

What matters is the number of requests. One of the limitations of SQL Express is that it only runs one scheduler, so in effect it utilizes only one CPU core. This limits the number of requests that can be processed. There is no hard limit, is just that the one CPU core will be able to handle only a certain amount of work before you start noticing performance degradation in your applications (requests take longer to complete).

The second important limitation of Express is the 1 GB max buffer pool size. This limits the amount of data that can be cached and the size of the procedure cache. The result is shorter page in-memory lifetime and higher I/O, as well as more often compilations of plans. All these again contribute to gradual performance degradation.

As you see with SQL Express there is no hard limit you reach and it stops working, is just that is constrained in what hardware resources it allocates and the result is a limited overall throughput. As you approach that throughput limit, performance start to degrade.

On the older version MSDE there was a query limit of 5 concurrent requests, on the 6th requests the MSDE engine would artificially slow itself down.

Answer 3

SQL Express can handle the same amount of connections in theory as it's big brother SQL Server, which is 32,767. However ... SQL Express would run into it's memory limit far before it'd ever get to that number since it's limited to using 1 GB of RAM.