none
Pool size question RRS feed

  • Question

  • Hi,
    I have a question about this example from MSDN:
    using (SqlConnection connection = new SqlConnection(
      "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {
            connection.Open();      
            // Pool A is created.
        }
    
    using (SqlConnection connection = new SqlConnection(
      "Integrated Security=SSPI;Initial Catalog=pubs"))
        {
            connection.Open();      
            // Pool B is created because the connection strings differ.
        }
    
    using (SqlConnection connection = new SqlConnection(
      "Integrated Security=SSPI;Initial Catalog=Northwind"))
        {
            connection.Open();      
            // The connection string matches pool A.
        }
    What if you specify the 'Max Pool Size' on the first connection on 5
    and on the third on 10? How big will the pool(s) be?
    thanks,
    James
     
    Wednesday, September 10, 2008 3:12 PM

Answers

  • James,

     

    SQL creates a connection pool by default for each unique connection made (based on the entire connection string not the server details) with a minimum and maximum number of connections.

     

    If you connect with any string that differs from the existing pool connection (even blank spaces in a similar connection string count) a new pool is created with default parameters (0 min - 100 max)

     

    To avoid accidentaly creating connection pools you should cache the connection string on first opening your applications and use the cached string for all connections. This way you avoid accidentaly creating new connection pools

     

    If you want more granular control over the pool, then you can set the pool size in the connection string as below:

     

    Dim strConn As String = “Server=PS002;Database=Northwind;Integrated Security=True;Min Pool Size=10;Max Pool Size=200”

     

    Creating the ten-connection pool causes a performance hit to the first client that opens a connection from web servers, but will not affect lan connections.

     

    I am not aware that you can redimension the pool once created as this would require a change to the connection string and therefore generate a new pool!

     

    I hope this clarifies the whole thing.

     

    Regards

     

    Rupert

    Friday, September 12, 2008 10:57 AM

All replies

  • The pool size will be two and the max pool size will be 10 after the second connection is made.  The second pool will continue to have the default max size of 100 and 1 connection in the pool.

    Hope that helps,

    John
    Wednesday, September 10, 2008 5:42 PM
  • what if I put the first max pool size to 10 and the third to 5,

    will the max pool size shrink upon the third connection?

    Thursday, September 11, 2008 8:46 AM
  • James,

     

    SQL creates a connection pool by default for each unique connection made (based on the entire connection string not the server details) with a minimum and maximum number of connections.

     

    If you connect with any string that differs from the existing pool connection (even blank spaces in a similar connection string count) a new pool is created with default parameters (0 min - 100 max)

     

    To avoid accidentaly creating connection pools you should cache the connection string on first opening your applications and use the cached string for all connections. This way you avoid accidentaly creating new connection pools

     

    If you want more granular control over the pool, then you can set the pool size in the connection string as below:

     

    Dim strConn As String = “Server=PS002;Database=Northwind;Integrated Security=True;Min Pool Size=10;Max Pool Size=200”

     

    Creating the ten-connection pool causes a performance hit to the first client that opens a connection from web servers, but will not affect lan connections.

     

    I am not aware that you can redimension the pool once created as this would require a change to the connection string and therefore generate a new pool!

     

    I hope this clarifies the whole thing.

     

    Regards

     

    Rupert

    Friday, September 12, 2008 10:57 AM