SQL Server Connection Pooling Myths

Const strConnectString = “Provider=sqloledb;User ID=pubsLogin; pwd=pubsPassword; Data Source= YourServerName; Initial Catalog=pubs”

Const strSQL = “SELECT * FROM Authors WHERE au_lname=’This is SQL Statement 1′”

Dim cnPubs ‘ As New ADODB.Connection

Dim i

For i = 1 To 1000

    Response.Write “<br>Iteration ” & i

    Set cnPubs = Server.CreateObject(“ADODB.Connection”)

    cnPubs.ConnectionString = strConnectString

    cnPubs.ConnectionTimeout = i ‘here’s the clincher


    cnPubs.Execute strSQL

    ‘Set cnPubs = Server.CreateObject(“ADODB.Connection”)

    Response.Write “&nbsp;” ‘ our simple delay tactic


    Set cnPubs = Nothing


Next ‘ i

Response.Write “<p>Done”


Question 5: True or False: Opening multiple recordsets on the same connection object will insure you take a connection from the pool.


While this is generally true, opening n forward-only, server-side, recordsets will, in fact, spawn n connections.  By design, SQL Server can only open one forward-only cursor per connection at one time.

So how can we get around this limitation? You could use a different cursor type, use a client-side cursor, or close the recordset before opening another. In practice, however, it is often the case that the overhead of creating another connection pales in comparison to any of the solutions just mentioned. Unless you know you are having connection issues, this is best left alone.

The graph below shows the results of opening 1000 forward-only, server-side recordsets against a single connection.

For sample code and additional information on this topic, please see Microsoft Knowledge Base article Q 235282.


Connection pooling is done smartly behind the scenes by ADO, but use them to your maximum advantage, you need to be aware of several eye-opening gotchas. As we’ve seen above be particularly diligent in your use of ConnectionTimeout settings and your forward-only, server-side (“firehose”) cursors. Both can greatly affect your SQL Server performance.






Shawn Kresal is an award-winning web specialist, database developer, and raconteur in San Francisco, California. He revels in the minutia of both silicon-based and carbon-based interactions.

Copyright ©2003 Shawn Kresal. All Rights Reserved


Leave a comment

Your email address will not be published.