SQL Server Connection Pooling Myths
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.
- True or False: Changing the connection string, even slightly, will force a new connection.
- True or False: Setting a connection to Nothing is important for proper pooling.
- 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.
- 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.
- 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>
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
For i = 1 To 1000
Response.Write “Iteration ” & i & “<br>”
Set cnPubs = Server.CreateObject(“ADODB.Connection”)
cnPubs.ConnectionString = strConnectString
Response.Write “ ” ‘ our simple delay tactic
Set cnPubs = Nothing