none
Change maxpoolsize at runtime RRS feed

  • Question

  • Hi,

     

    I want to change the max pool size in the connection poo at runtime.

    Say I have a APS.Net site connected to SQL server. My objective is to maintain the response time of requests by changing the max pool size in the connection pool. Can this be done dynamically without restarting the application. I dont need to change the configuration file, instead a logic will generate the maximum pool size for some spesific time period. How can I do this? If we change the pool size, how long does it take to activated in the system?

    Does the max pool size affect the response time of the system?

    THank you

    Pati

     

     

    Monday, September 13, 2010 7:46 AM

Answers

  • Hi Pati,

    When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. If you change the max pool size in the connection string, and open a new connection with the new connection string, a new pool associated with the new connection string will be created. So you should consider setting the max pool size to a proper value at the first time the connection is opened, and please remember to close the connection when you are finished using it so that the connection will be returned to the pool. Max pool size is used to limit the max number of connections in one single pool, it will not affect the response time of the system.

    Best regards,
    Alex Liang 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by VMazurModerator Wednesday, September 15, 2010 10:19 AM
    Wednesday, September 15, 2010 7:41 AM
    Moderator

All replies

  • Hi Pati,

    When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. If you change the max pool size in the connection string, and open a new connection with the new connection string, a new pool associated with the new connection string will be created. So you should consider setting the max pool size to a proper value at the first time the connection is opened, and please remember to close the connection when you are finished using it so that the connection will be returned to the pool. Max pool size is used to limit the max number of connections in one single pool, it will not affect the response time of the system.

    Best regards,
    Alex Liang 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by VMazurModerator Wednesday, September 15, 2010 10:19 AM
    Wednesday, September 15, 2010 7:41 AM
    Moderator
  • Hi Alex,

    Thanks for the response.

    For instance my idea is to have a single connection string and change the pool size. The entire application will use this connection string at that time.

    at time t1

    Conntectionstiring1 : Data Source=(local);Database=AdventureWorks;Max Pool Size=20 Pooling=True;

     

    at time t2

    Conntectionstiring2 : Data Source=(local);Database=AdventureWorks;Max Pool Size=25 Pooling=True;

     

    As you said, there will be a 2 pools after time t2. However, the pool with Conntectionstiring1 will not be used by the applicaiton. What happen to this connection pool. Can we delete these connection pool? does a unused pool affect the performance of the system because during 3 mins , i may create 100 of different connection pools/strings?

     

    Other than that, does the max pool size parameter affect the response time of the application. for instance If I set it low.

    What is the effect of setting it too high?

     

    Thank you

    pati

    Thursday, September 16, 2010 4:07 AM
  • Hi pati,

    If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.

    For detailed information about about SQL Server Connection Pooling (ADO.NET), please refer to:
    http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.100).aspx

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 16, 2010 10:17 AM
    Moderator