determining max pool size

[steven]

hi

i've got an app that runs something like 4000 updates when certain
pages are updated. it's taking on average 20 seconds for these pages
to execute the update command (15 seconds if all i do is open and
close the connection without even executing the sql statement). the
original max pool size set in the web.config file was set to 100. I've
boosted the max pool size to 500 and now get the update command to
execute in about 2-3 seconds.

i'm worried that i'm going to run out of resources somewhere. this is
an SQL Express server. the "maximum number of concurrent connections"
in SQL Express is set to 0 (unlimited). the machine SQL Express is
installed on is Intel Xeon 2.8 with 2.5 gigs ram.

the ado.net default max pool size is 100. why? why not boost it to
1000 or more? what environmental issues do i need to consider when
setting the max pool size?

tks in advance.

 

[Peter Morris]

what environmental issues do i need to consider when

setting the max pool size?

I believe SQL Server Express isn't meant for this kind of situation and that
you should spend some money on proper SQL Server licenses.

 

[steven]

I believe SQL Server Express isn't meant for this kind of situation and that
you should spend some money on proper SQL Server licenses.

SQL Express is running in my QA environment. I've got SQL 2000
Standard running in production and plan on making the changes there
should things stress test well in QA.

 

[George Ter-Saakov]

I do not see how increasing pool size changes the speed Update command is
running with..
Are you sure you are closing DB connections always? Cause it sounds like you
do not... 500 open connections to SQL server I awfully lot....

George.

 

[steven]

I do not see how increasing pool size changes the speed Update command is
running with..

the larger the pool size, the more requests can be server. anything
over 500 requests sits in a queue.

Are you sure you are closing DB connections always? Cause it sounds like you
do not... 500 open connections to SQL server I awfully lot....

agreed. i've inherited this project from another developer who was
apparently aware of a memory leak somewhere because she had pooling
turned off altogether. this worked fine for her until the number of
records grew and now we're dealing with some very expensive opening
and closing of db connections. i've spent a little while working on
trying to make sure the connections are closed, but there's thousands
of lines of code and i can't be sure i've caught everything. we're
also looking at phasing out the current site by the end of the year,
so i'm just trying to make things work without expending huge amounts
of resources on trying to track down every issue.

i've reduced the number of connectios in the pool to 100 and it seems
to be executing quickly enough. my main concern now is that i've
missed closing connections somewhere and if i create too small a
connection pool, the pool will max out. so, i'd rather have a really
large pool and hope the garbage collector has time to clean up any
unused connections than too small a connection pool. if a large
connection pool simply means more CPU and ram usage, i'm okay with
that.