none
Connection pooling when using SQL CLR stored procedure to remote SQL instance RRS feed

  • Question

  • I have a SQL CLR 2005 stored procedure which remotely connects to a SQL 2008R2 instance to execute a procedure and return a tabular result.  It's working just fine but I wanted to know if connection pooling will happen by default? 

    The connection property values are always the same when the local CLR 2005 procedure connects to the remote 2008R2 instance.  I'm trying to ensure that the open/close of the connection in the SQL CLR 2005 stored procedure uses pooling since this sproc is executing repetitively many times a minute.

    My connection string to the SQL 2008R2 instance currently looks like this:

    server=PSQLNYV04W\instance2; database=Dmatch; user id=dbo_Dmatch; password=tree123

    Do I need to add any other properties like “Enlist” or “Pooling” to the connection to ensure that pooling is happening?

    Thanks,

    Sid

    Thursday, June 7, 2012 4:48 PM

Answers

  • Connection pooling is enabled by default in ADO.NET hence for CLR stored proc. Unless you explicitly disable it by adding pooling=false in your connection string. So You do not need any further setting.

    If you want to make sure about this behaviour,the following table lists the connection pooling counters that can be accessed in Performance Monitor under the ".NET CLR Data" performance object.


    Lingaraj Mishra

    • Marked as answer by sidlet Friday, June 8, 2012 11:21 AM
    Friday, June 8, 2012 4:28 AM