articles >> performance tuning >> SQL Server Connection Pooling Myths

SQL Server Connection Pooling Myths

By : Shawn Kresal Nov 03, 2004

Many of us are already familiar with connection pooling and understand to some degree its importance in web and SQL Server programming. We’ve all heard the adage that “connections are expensive” and “pooling is good”. This article will examine the topic in more detail, bringing to light some of the finer points of connection pooling.

Why is connection pooling so beneficial? Creating a connection is expensive; it requires your application to connect to the database server, authenticate, and return a valid connection. Drawing from the pool eliminates this overhead. With web applications, proper pooling is absolutely critical to performance because IIS closes all of a page’s database connections after a page is processed. With hundreds, thousands, or millions of page requests, each wanting a new database connection, were it not for pooling, our site’s performance would quickly become unacceptable.

Take this quick test to see how well you understand connection pooling with IIS and SQL Server. Keep track of which questions you answer true and, if you’re impatient like me, skip to the end of this article, otherwise follow along as we dissect each answer.

1. True or False: Changing the connection string, even slightly, will force a new connection.
   
    
2. True or False: Setting a connection to Nothing is important for proper pooling.
   
   
3. True or False: Changing the CursorLocation (adUseClient to adUseServer for example) or Mode (adModeRead, adModeShareDenyRead, etc.) of a pooled connection may cause problems when it is returned to the pool.
   
    
4. True or False: Modifying the ConnectionTimeout will cause the pooled connection to remember this new value and could introduce problems when it goes back into the connection pool.
   
    
5. True or False: Opening multiple recordsets on the same connection object will insure you take a connection from the pool.

 

Establishing Our Baseline

Before we can accurately assess what our connection pool is doing, we need to look at it under the ideal scenario. Let’s create an ASP page that opens 1000 connections as quickly as possible. All connections will use the same connection string and perform only a simple SELECT statement. We’ll use the pubs database with a login name of “pubsLogin”.

 

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Option Explicit

 

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 "Iteration " & i & “<br>”

    Set cnPubs = Server.CreateObject("ADODB.Connection")

    cnPubs.ConnectionString = strConnectString

    cnPubs.Open

 

    cnPubs.Execute strSQL

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

 

    cnPubs.Close

    Set cnPubs = Nothing

    Response.Flush

Next '

Response.Write “<p>Done”

</script>