none
SQL Server 2005 | Connection Pooling | Number of connection | Guidelines RRS feed

  • Question

  • Hi

    I am looking for some guidelines for SQL Server connection pooling.

    I would like to know the number of recomended connections for a SQL server based upon number of CPU's and or memory. I would also like to know any guidelines around SQL server connection pooling.

    Thanks

    Regards

    Gaurav Verma

    Tuesday, September 25, 2007 10:16 AM

All replies

  • Gaurav,

     

    I'd say that you should start with the default, which from memory I think is 100.

     

    If you have cause to change the value, do so because you are sure that either connections are waiting for a free connection, and it is for the same machine with the same credentials, and so need to be increased.  Alternatively if there is cause to suspect that there are too many connections in the pool, and they're not being garbage collected quickly enough (highly unlikely) then you may need to reduce the pool.  You might also want to look at how the connection is used, if one connection could be utilised in the application to get around any pooling problems that you might encounter also.

     

    I can't see that the value would have any relevance in the context of multiple CPUs, but instead should be down to the load on the server.

     

    I hope that helps you,

     

    Martin Platt.

    Tuesday, October 2, 2007 4:24 AM
  • I agree, the size of the pool depends on the load of the server, not the server's configuration. There are reasons to specify both a maximum and an initial size though.

     

    Setting an initial size other than zero can reduce the start time of the client applications because it ensures that a certain number of connections are always available. That could be an issue at around 09:00 in the morning when all users start their applications and connect to the server at the same time.

     

    Setting the maximum size of the connection pool is usefull if you suspect that you have blocking problems due to the large number of concurrent queries. In this case you may be able to reduce blocking by limiting the maximum size of the connection pool. Of course, the pool already limits the number of concurrent connections, so you may never encounter this problem.

     

    Both cases are rare so you should concern yourself with them only if you suspect that the server is not performing as it should. 

    Tuesday, October 2, 2007 5:43 AM
  • I'm developing an application that insert's huge amount of the data in the database and it takes a lot of time so I work on speeding things up where I can... I make the inserting true threads asynchroniously and can't figure out what is ideal, a lot of threads bombarding the SQL or having let's say 20 connections (inserting in the same table, area if you want) and not overstressing the SQL database...

    Thank's...
    Thursday, December 13, 2007 11:52 AM