• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • Last Modified:

SQL Server Connection Pool and Connection String Parameters

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
0
andrepires
Asked:
andrepires
  • 3
  • 2
1 Solution
 
Ted BouskillSenior Software DeveloperCommented:
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
}
0
 
andrepiresAuthor 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
0
 
Ted BouskillSenior Software DeveloperCommented:
Hmm, as a Development Manager I place no value in MS certification.  I've met too many developers that are MS certified but lack common sense.  However, I do realize too many companies take the easy way out and require it so I'll try and help.

Connection pooling is actually controlled by your connection string and you can actually have multiple connection pools.  If the connection string changes, then a new pool is created.  If your application has five different connection strings you will have five different connection pools.

So let's start with question 3)  Let's assume you have no active connection pools or connections because the server was just restarted.  You then request a connection with a connection string using the parameter Min Pool Size=5;  A new connection pool will be created and it will have one connection in the pool.  If a second request for a connection with the exact same connection string is request while the first connection is open, a second connection is stored in the pool.

Now if both connections complete there will be two connections in the connection pool that will never be closed because the minimum is five.  However if suddenly 6 connections are requested with the same connection string it will create 4 new connections and add them to the pool.  Except that when the first connection in the pool time's out, it will be removed from the pool so that only 5 remain.  Those 5 will never disappear (that is why the default is 0)

Question 1)  T-SQL has a USE [Database] command.  So, if you were to create a SQL connection string like so: "Server=MyServer;User ID=MyUser;Password=MyPassword;Connection Reset=false;"  a connection would be created without a database context.  So if you executed a SQL command like "SELECT * FROM MyTable", the SQL database would use the MyUser's default database to attempt to execute the SQL command.  If MyTable didn't exist in the database it would fail.  (When you create SQL login's it sets a default database)

So, if you created a SQL command like this "USE [MyDatabase]; SELECT * FROM MyTable;" after the connection is put back in the pool the next SQL command like "SELECT * FROM AnotherTable" applied to the same connection string would be applied to [MyDatabase] instead of the default because there hadn't been a round trip back to the database to clear the database context.

Question 2) Is tricky.  Basically it means that if Enlist=False then a running transaction "BEGIN TRANS" or using the SqlTransaction object would be reused and a new transaction would be created.  To be honest I haven't run into a business case for using this so I'm struggling with how to create a good example.
0
 
andrepiresAuthor 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!
0
 
Ted BouskillSenior Software DeveloperCommented:
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now