none
C# SQL Server Connection Pooling

    Question

  • Hello,

    I'm using SQL Server 2005 and Visual Studio 2008.  I've been reading up on connection pooling and I can see that it would make quite a difference in the application we use.  I'm not from a C# background, I'm a DBA.  I've been using SQL Profiler on the inhouse application that was created and notice that it opens and closes a connection to SQL Server for every task.  I thought connection pooling is enabled by default.

    I went to the Microsoft page about connection pooling (http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx).  I copied the example into a new C# project.  I created a simple Windows Forms application with a button on it, when clicked it runs the following code...

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // Pool A is created.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database2"))
    
    {
    
      connection.Open();
    
      // Pool B is created because the connection strings differ.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // The connection string matches pool A.
    
    }
    
    
    That's fine, when I run profiler it only makes two login events in SQL Server Profiler no mater how many times I click the button to run the above code.  However when I put the following code in there to actually return data...

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      SqlCommand myCommand = connection.CreateCommand();
    
      myCommand.CommandType = System.Data.CommandType.Text;
    
      myCommand.CommandText = "SELECT * FROM dbo.activities";
    
    
    
      connection.Open();
    
      myCommand.ExecuteReader();
    
      myCommand.Dispose();
    
      // Pool A is created.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database2"))
    
    {
    
      connection.Open();
    
      // Pool B is created because the connection strings differ.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // The connection string matches pool A.
    
    }
    
    
    I see the following in SQL Server Profiler...

    Button Click One

    1.  Audit Login
    2.  Select Statement
    3.  Audit Login

    Button Click Two

    1.  Audit Logout
    2.  Audit Login
    3.  Select Statement

    What is happening is that when I click the button a second time to run the second code block above Profiler shows that it logs out of SQL Server then logs back in.  I thought connection pooling was meant to re-run that connection?

    Again, appoligies I'm not a programmer but a DBA however I'd love to know how to get connection pooling to work.

    Kind regards,

    Chris
    Thursday, August 20, 2009 9:14 PM

Answers

All replies

  • Hi Chris,

     

    Welcome to Visual C# General Forum!

     

    The Audit Logout event when the button is clicked twice does not mean the connection is recreated instead of getting from the connection pool.  We can enable the EventSubClass column of the SQL Server Profiler trace to check whether the connection is pooled or not.  (From Trace Properties window)   For detail, please see http://weblogs.sqlteam.com/billg/archive/2007/10/31/Login-Events-include-Pooled-Connections.aspx.

     

    I think the Audit Logout is called because connection pool is going to reset the current connection to reuse it.  We can see the Audit Logout, exec sp_reset_connection, and Audit Login events are always combined together.   The sp_reset_connection will reset the connection.   For detailed information about sp_reset_connection, please see http://www.sqldev.net/misc/sp_reset_connection.htm. 

     

    If you have any questions, please feel free to let me know.

     

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Friday, August 21, 2009 2:50 AM
    Moderator
  • Hello Lingzhi,

    Thank you very much for the reply.  Yes it's as you said about the event sub class and I've confirmed with Activity Monitor in SQL Server Management Studio.  Thank you for the detailed reply, very much appreciated.

    Kind regards,

    Chris Rutherford
    Monday, August 24, 2009 8:37 PM
  • Hi Chris,

     

    You are welcome!   

    Have a nice day!
     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Tuesday, August 25, 2009 9:36 AM
    Moderator