We help IT Professionals succeed at work.

SQL Server Connection Pool and Connection String Parameters

1,234 Views
Last Modified: 2011-09-20
Hello Experts!
I'm learning about SQL Server Connection Pooling and Connection String Paramenters.
I have som questions about the following paramenters:
1) Connection Reset Parameter
a) It is saying in my book that it determines whether the databse connection is reset when it is removed from the pool and that when it is set to False, it prevents an additional server round-trip when obtainng a connection. What it means? Can you please give me a real world example?
b) It is saying here that I must be aware that the connection state is not being reset in the above context. What it means?
c) It is saying to use the ChangeDatabase method rather than the SQL USE command to anable ADO.NET to automatically reset connections whne they are returned to the pool. What is it??? What it means by saying SQL USE command?

2) Enlist Parameter
a) It is saying here that when it is saet to true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists. Could you please give me a real works example of this so I can understand what it means?

3) Min Pool Size Parameter
I would like to know what happen if I set the Min Pool Size parameter to let's say 5 and we have less than 5 active connections. Will the pool be created?

Regards,

Andre
Comment
Watch Question

Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
If you are learning then leave the connection pooling alone.  Microsoft does a great job of managing it for you.   Simply be sure to close connections where you are done with them and following Microsoft best practices in your data access layer.

try
{
    // The using keyword will close the connection automatically even if there is an exception
    using (SqlConnection sqlConn = new SqlConnection("sqlConnectionString"))
    {
        using (SqlCommand sqlCmd = new SqlCommand(sqlConn)
        {
            // Your code here
        }
    }
}
catch (Exception e)
{
    // Write exceptions to log
}

Author

Commented:
Thanks Ted, but I really need to know about it.
Actually I'm not a begginer, I'm trying to master all info about this subject inorder to get my MS certification.

Regards,

Andre
Senior Software Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok Ted. Thank you for helping.
I understand and aggree with you in almost everything you said.
There is just a little disagreement regarding the connection pooling.
Since I had no responses before yours, I had to do some tests my self.
I noticed that when the min connection pool is set, let's say Min Pool Size = 5, once you create a the first connection string and use it, the server will create 5 available connections at the same time and put them in the pool. Another little detail is that when you create a new connection, the server always create an additional connection and put both connection in the pool. So, even if we set Min Pool Size to zero, qsl server will always start a pool with a minimum of two connections. Weird uh? There are some things that only MS can do for you :)
Anyway, thank you for helping!
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
Well I must admit it was by memory in regard to the connection pooling minimum value.  I had tried a test application previously and couldn't remember for sure.  I should have said so.

I think I might understand why they created one additional connection.  They probably are always creating one spare because the one just created is in use.  That way there is always a spare.

IE: No connections or pools are active.  Your code requests a SQL connection.  Knowing that you will likely consume that connection immediatly for a transaction they create another one in case another request comes in.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.