Managing and Monitoring .NET Connection Pools

What’s this all about?

One of the more far-reaching data access features implemented by Microsoft in the past few years (other than ADO.NET itself) is the ability to manage a pool of connections for an application, middle-tier component (such as a Web Service), or Web application. Connection pooling was first implemented relatively long ago in the Pleistocene[1] data era as early versions of ODBC were well established in their evolutionary climb. While connection (and object) pooling continue to evolve, the basic tenets are fairly easy to understand. This article discusses how to properly connect (and disconnect), how the pooling mechanism works, how you can monitor the pooler’s activity, and how to make sure your application is using the pooler correctly—hopefully before it overflows and shorts out your system.

All too often I hear the same questions from clients, students, the newsgroups, and list servers:

·         “How can I enable/disable the connection pool?”

·         “How many connections are already in the pool?”

·         “Why can’t ADO/ADO.NET open any more connections? It seems to lock up/slow down/die after about 100 connections.”

·         “I want to be able to identify the user executing the code in the connection string, but if I do, I quickly run out of connections. How can I make sure only the ‘“right’” people have access to the database and still leverage the connection pool?”

This whitepaper is extracted in part from Chapter 9 of my book Hitchhiker’s Guide to Visual Studio and SQL Server (7^th Edition) (Addison Wesley). After you read this whitepaper and experiment with the test applications, you’ll know the answers to all of these questions and many others. I also focus on how the new SqlClient .NET Data Provider can let you manage how (or even if) the pool is created and how to tell how many connections are languishing therein. I show several techniques to determine how many pools have been created and how many connections are being held at the SQL Server—despite the fact that you thought your Web Service closed the connection forty-five minutes ago. I go over the rules and realities of connection pools and some easy ways to avoid the more serious issues. I also provide several test applications to help monitor the pool and experiment with the pooling options. No, I won’t be discussing pH levels or how to super-chlorinate your connection pool after the neighbor’s kid had his little accident.

How does the connection pool work?

When connection pooling is enabled (and it is by default), any connection established by executing the Open method in ADO.NET (or any other data provider that implements pooling) is either drawn from a pool of pre-opened (and idle) connections, or a new connection to the database is made and a new pool is created. This means when you or .NET “closes” your connection in code, you’re really just releasing the connection back to a pool of (slightly or heavily) used connections; and whether you know it or not, the actual “physical” connection to the data source remains open and continues to hold resources on the server for a (sometimes indeterminate) length of time. However, when (or if) the same process needs to reconnect, the connection pool simply plugs you back into an existing “hot” connection—assuming the connection string has not changed. No, you might not get the same connection again, but (in theory) it should be functionally equivalent. This means your application can save (sometimes considerable) time that would otherwise be used to establish a connection to the server.

When do Pools Make Sense?

No, not all applications need a pool of available connections or the overhead associated with the pooling mechanism. For example, Windows Forms applications rarely need a connection pool as they can be designed to open one (or two) Connection instances and hold them open for the life of the application. Consider that the connection pool is not on the server—it’s built and maintained on the client.

Now, if you’re building an ASP.NET or XML Web service application the pooling mechanism can noticeably help improve performance. That’s because this architecture usually dictates that you open, query and close a connection “just in time”—just before it’s needed. This approach returns viable connections to the pool so that other instances of the application (within the App Domain) can reuse the connections.

Connection pooling is not free. Each time you open a pooled connection some overhead is added to the client’s and server’s CPU load. This overhead includes:

·         Code to determine if the current ConnectionString matches an existing pool

·         Reauthentication of the SSPI credentials.

·         Resetting the server-side connection before first use—each time it’s reused.

This means that each Open operation and each Close operation takes some processing time—time that’s not needed if you simply keep the Connection open.

When a new pool is created

At this point, let’s make a few things clear. First, if any of the following items change, a new pool is created for you:

·         The ConnectionString property: It has to be the same each time. If the arguments are in a different order, it’s different. If you add, change, or remove arguments—even if they simply reflect the default behavior, it’s different and you get a new pool.

·         The User ID or password: If you use a specific User ID for each connection, each User ID gets its own pool. If you specify Integrated Security=SSPI, this does not constitute a change.

·         The process ID: That is, each process/program/component/Web Service/Web application gets its own pool. I’ll discuss this in more depth later.

·         The transaction scope/enlistment: If you’re using transactions, each transaction scope gets its own pool.

·         The MARS enlistment: If your application enables multiple active resultsets (MARS) that connection cannot be shared/reused by parts of your application (as dictated by the ConnectionString) that do not enable MARS.

How does the security setting affect pooling?

If you use a specific User ID for each connection, each User ID gets its own pool. On the other hand, if you specify Integrated Security=SSPI, you get a single pool for your process’ User Account. This means that you can’t specify a specific role or User ID and expect to share the pool created for another role or user.

Why does the process ID affect pooling?

ADO.NET is incapable of sharing connections across processes—just like every database interface since the dawn of time. For this reason, each process must have its own pool of connections. This means that when you run a client/server application, it gets its own process ID—each time you run it. If you run five instances of an application on the same system or five separate systems, each gets its own process ID.

However, if you run a middle-tier component—such as a Web Service—it also has its own process ID; but, in this case, multiple instances are considered to be running the same process. This means that a Web Service can serve a couple or a couple thousand clients—all sharing the same pool of connections. However, when developing your Web Service, each time you compile and create a new ASPX, the new executable gets a new Process Identifier—and a new connection pool. This means you might potentially have two virtually identical copies of an executable on your system (just different versions); but be assured that each gets its own connection pool.

Opening Pooled Connections

So, when you open a Connection with pooling enabled, the pooling mechanism (as implemented by the .NET Data Provider) tries to find a matching ConnectionString in the existing pool (if there is one). If there is no pool one is created. If there is no matching ConnectionString or a matched ConnectionString connection is still active (not closed), a new physical connection is established with SQL Server and a member is added to the pool. At that point SQL Server creates an agent to handle the operations requested on that connection.

When a the pooling mechanism finds a connection in the pool that has been closed by the application it is reactivated. The security credentials are revalidated and when the first TSQL operation is executed, it’s prepended with a “Reset Connection” to clear any residual state or temporary objects from the connection before it’s reused. This is like changing the sheets on the bed in a hotel just before the new guest arrives. This takes some time. Later I show how to disable the connection reset operation to improve performance. This approach assumes the application inheriting the connection does not mind sleeping in the dirty sheets left behind by the previous tenant.

This process is repeated until the pool is “full”—it reaches the programmed capacity which defaults to 100 pooled connections (per pool). This means a single client application can have as many pools as necessary and each can open and maintain up to 100 (or more) connections. Does that make sense? Ah, no. Generally, a heavily loaded ASP.NET application will leave about 25 connections in the pool.

Closing Pooled Connections

When your code uses the Connection.Close or Dispose methods or when the Fill or Update methods do the same behind the scenes, the logical pooled connection is marked as reusable in the connection pool. Nothing is done on the server to reflect this. All of the server state and other resources assigned to and owned by the physical connection are left intact. Until a connection is closed, it remains in the pool and cannot be used by any other instance of your application.

Note that if a Connection object falls out of scope the .NET Framework garbage collector (GC) will eventually close the connection and dispose of the object. However, this can take quite some time—even days as the GC does not run until the system needs more memory. Never depend on the GC to clean up and close your connections—especially in an ASP environment where pooling is essential to performance.

Automatically Flushing Connections

If a pooled connection remains in the “closed but reusable” state for between 4 and 8 minutes (an interval chosen at random) the connection pooling mechanism closes the physical connection and discards the pooled connection. That is unless the number of remaining connections is greater than the minimum connections configured for the pool (the default is 0). Note that a connection must have been closed by the application (and released back to the pool) before it can be subject to automatic release. If you don’t close the connection in code or orphan the Connection object, the pooling mechanism will do nothing. No, there are no ConnectionString arguments to change the timeout value.

When are Pools destroyed?

In ADO 1.0 and 1.1 the original .NET CLR Data performance counters proved to be more trouble than they were worth. In ADO.NET 2.0 these have been augmented with a new, far more reliable set of counters exposed off of the .NET Data Provider for SQL Server. These can be monitored in your application (as I illustrate in the book) or via Perfmon. By monitoring these perf counters, you can see that when a Connection object is correctly opened and closed, based on the NumberOfPooledConnections counter value. Note that these counters are exposed on a process-by-process basis.

When the application (process) ends, all of the pools associated with the process are destroyed but before that happens, all of the “physical” connections to the data source (SQL Server) are closed. As each connection is closed, the server releases any resources and state being held for the connection.      

Be careful, because even if the SQL Server instance is shut down, SqlClient connection pools remain. However, if you subsequently restart the server, the SqlClient provider still tries to use these pools to reconnect to the server—even though the connections they hold are no longer active or usable. The result? A SqlException is thrown. Fortunately, since ADO.NET 2.0 this destroys the zombied pool and a new one is created when the component restarts. I suggest you add an exception handler to deal with this (fairly likely) contingency. ADO.NET 2.0 also permits you to selectively flush the pool or flush all pools on the client as shown in the code examples below:

Overflowing the Pool

Given these issues, you might think that it’s easy to overflow the pool or bog down the server with connections if you aren’t mindful of what valves and switches you open and close. Well, consider that the limiting resource is the number of connections that a pool can handle. This value defaults to 100 and can be set to a higher number—but that rarely makes sense. When you also consider that (ideally) a connection should be used for a brief instant and released back to the pool for other instances of the application to share, 25 connections per pool should be plenty.

I don’t see any indication of an upper limit on the number of pools—just the number of connections a pool can handle at any one time. It’s probably a good idea to separate those components likely to compete for connections within a highly used pool by creating two or more separate pools—one for each functional operation. Watch out though, because when a pool overflows with too many connections, your component performance crawls to a halt and your clients go to other sites with their business—not to mention your neighbors complaining about a flooded back yard.

Understanding Connection Issues

One of the many issues you need to consider when managing connections is how and when you open connections using ADO.NET. If you open connections early in your application or component and expect the connection to remain available for the lifetime of the application (or component), you’re using a client/server connection mentality. This is not bad for some (not all) client/server applications, but for a Web Service or application, it’s not usually possible and it’s certainly not scalable. It’s also unwise to expect a connection to remain alive indefinitely. Stuff happens and servers or networks can go down, so be prepared to trap exceptions caused by a busted connection or other unexpected problems.

To make sure that there are enough connections to go around when working with any architecture,       a highly scalable strategy dictates that you to open your Connection object just in time (just before you need it) and close it as soon as you can. ADO.NET can help in this regard because it opens connections automatically when using the DataSet Fill or Update methods. If you’re using a DataReader, you have to open and close the connection yourself—even if you use the CommandBehavior.CloseConnection option (you still have to close the DataReader to get the associated connection to close). Unlike Visual Basic 6.0, none of the .NET languages can guarantee to close your connection when the Connection object (such as the SqlConnection) falls out of scope. If the connection is still open, it can’t be reused. If the Connection object is still open when it falls out of scope, it’s probably lost forever.

In any case, your Web site should be able to handle success. That is, if you put your URL in an advertisement seen by eighty percent of the worldwide viewing public, you’re likely to get a lot of hits—like the “mlife” ads on Superbowl Sunday. How your application behaves when all of the available connections are in use is the key to the success you crave. One approach is to architect in enough capacity, but once that’s exhausted, you still need a viable strategy that does not include returning a low-level message to your potential customer. When I see an OLE DB error message dumped to the browser, I know the company did not care enough about their customers to try to win my business—I simply move on to their competition. One helpful tip here is to increase the Connection Timeout connection string argument. This is the length of time ADO.NET waits for a connection to be released from the pool. If this value is set too high, the browser (or client) might time out before you get connected. If set too low, your error handler will have to know how to retry—possibly after asking the client to be patient while your application deals with other customers.

Activating, Tuning, and Disabling the Connection Pool

When using the new SqlClient .NET Data Provider to access (just) Microsoft SQL Server 7.0, 2000, 2005 (and later), you’ll want to create a new SqlConnection object and set its ConnectionString property as shown below. This property is the only way to change the behavior of the SqlClient .NET Data Provider and how it interacts with the connection pool. Using the appropriate setting, you can turn pooling off (it’s on by default), change the size of the pool, and tune its operations. The ConnectionString property is also where you choose the security motif you’ll be using, as well as where to point to the server and set the communications protocol.

 

You’ll discover that most of the Connection object properties are read-only—both before and after you use the Open method on the Connection object; so, unlike COM-based ADO “classic” (ADOc), you have to depend on the ConnectionString property to specify every characteristic of your connection, including how the connection pool is managed. No, you won’t be able to pass in last-minute UserID and Password values in the Open. Security settings must be provided in the ConnectionString property.

The ConnectionString property is parsed at run time; when the provider finds an exact match for one of the recognized argument keywords, it assigns the value to the appropriate property. The remaining keywords are used elsewhere behind the scenes. To make things easy, there are several alternative keywords which permit developers familiar with legacy (OLE DB/ODBC) connection strings to use many of the old keyword/value pairs. Table 1 lists the keywords exposed by specific Connection properties. It also shows their default values and what effect they have on the connection itself.       For more complete details on how to build a ConnectionString, see Chapter 9 of my Hitchhiker’s Guide to Visual Studio and SQL Server (7^th Edition).

Understanding the Connection Pool Options

The following table (Table 1) lists those ConnectionString keywords that determine how the connection pool is managed for the specific connection you’re opening. These keywords can be set on a connection-by-connection basis, which means that some Connection objects will be pooled and others won’t be.

Keyword	Default	Function
Connection Lifetime	0	How long (in seconds) should a connection remain open once an application returns it to the pool. 0 = “maximum” timeout[2] (never discard connections).
Connection Reset	'true'[3]	In SQL Server 2000. Determines whether the connection is reset when being drawn out of the pool. Setting to 'false' avoids having to make an additional round trip to the server, but the connection state is not reset. (See earlier discussion.)
Enlist	'true'	When 'true’, the connection pool automatically enlists the connection in the creation thread’s current transaction context.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	'true'	Enable connection pooling for this connection. If set to 'false', connection pooling is disabled for this connection.
Connection Timeout -or- Connect Timeout	15	How many seconds should the .NET data provider wait to establish a connection to the data source.

Table 1: ConnectionString keywords used to disable or configure connection pooling

Turning off connection pooling

It’s really pretty easy to revert back to an un-pooled connection because there are situations where this makes sense. If you’re sure this is what you want to do, simply set the “Pooling” ConnectionString argument to False—the specific Connection object opened with this option will not be pooled, the Open method will establish a new connection to the server (if possible) and when you use the Close method, the server connection will be closed immediately.

Setting the Maximum and Minimum Pool Size

The connection maximum pool size determines how many connections the pool will hold. This option is set by using the Max Pool Size keyword. By default, 100 connections are permitted to accumulate in each pool. No, this does not mean that you start out with 100 SqlConnection objects or 100 connections, only that you can’t create any more than 100 connections per pool. Max Connections does not limit the number of pools you can create—that’s only limited by the number of SQL Server connections available (about 32,768 per cluster).

In some cases, you might want to leave one or more connected SqlConnection objects in a pool for an indefinite length of time. If this is the case, set the Min Pool Size to an appropriate value. This can help connection performance for the application that does not run that often, but can’t wait for a new SqlConnection object to be constructed and a connection established. This can also be a way to “reserve connections.”

Why does the Connection Pool Overflow?

I’ve seen quite a few questions on the newsgroups and MSDN forums that complain about how applications seem to work okay in testing, but fail in production when put under a load. All too often the application seems to fail when about 100 clients get connected. Remember, that the default connection pool size (the number of connections in a single pool) is 100. After that, your application will be blocked until a connection is freed (if ever) or for Connection Timeout seconds.

Connections Left Open

The problem can usually be traced back to one or more places where a connection was not closed while the SqlConnection object was still viable—in scope.

Consider that, in ADO.NET, there are two distinctly different techniques for opening and managing connections:

·         Use the DataReader object and manage the connection manually. In this case your code constructs a SqlConnection object and uses the Open method once the ConnectionString property is set (one way or another). After you’ are done with the DataReader, you close the SqlConnection (one way or another) before the object falls from scope. If you don’t close the SqlConnection, your code “leaks” a connection with each operation and the pool eventually overflows.       No, the .NET garbage collector won’t close the SqlConnection and clean up for you. Code listing 1 above shows how I generated a DataReader to return the rowset from a simple query.

·         Use the DataAdapter object, which manages the connection automatically. The DataAdapter Fill and Update methods automatically open the DataAdapter object’s connection and close it once the data I/O operation is complete. However, if the connection was already open when the Fill or Update method is executed, ADO.NET does not close the SqlConnection after the method completes. Again, this is an opportunity to leak a connection.

In any case, if the exception handler is triggered for some reason another opportunity is created to leave the Connection object orphaned and unclosed. Just make sure that you plug this hole as well.

System is Overloaded

Okay, suppose you’ve verified that all Connections are being closed and you’re still getting Timeout exceptions. In this case I would start looking at how long each Connection is being used. Consider that in order for a pooled Connection to be reused, it must be closed. However, before a Connection can be closed, the query or other operation that is active on the Connection must be complete, the rowset fetched and the application must execute Close (one way or another). If the operation that’s being executed on the Connection is blocked by another operation or is waiting for a user to provide feedback (both design flaws), your pool will fill rather quickly as more users/applications request use of the pooled Connections.

In this case I recommend an application and system review. Is your SQL Server operating at capacity? Is it leveraging the indexes correctly? Is there enough hardware (CPU cycles and RAM) to bring to bear? Of course there are a dozen dozen other ways to improve performance and I discuss many of these in the book. The bottom line is that once you reach a tipping point, the server cannot perform any more operations/second so all subsequent operations are blocked and the pool overflows.

Examining the DataReader

Orphaned connections and overflowing pools are a big problem so let’s focus a little more closely on the biggest culprit—the DataReader. Since there has been quite a bit of discussion about this object on the Web, I decided to write a sample Windows Form application to test the behavior of the DataReader, concentrating on the CommandBehavior.CloseConnection option. My application clearly showed that even if the option was used, if the SqlDataReader (or SqlConnection) were not explicitly closed, the pool filled to overflowing and the application failed when additional connections were requested.

However, some developers (continue to) insist that if you set the CommandBehavior.CloseConnection option, the DataReader (and the connection associated with it) are closed automatically when the DataReader finishes reading the data. Well, I figured out why some people were coming to this conclusion; —they’re partially right—but only in a Web Application (ASP.NET) and when using a complex bound control. Looping through a DataReader resultset to the end (when Dr.Read returns false) is not enough to trigger automatic connection closing. However, if you use a bound control, such as the DataGrid, the bound control closes the connection, but only if the CommandBehavior.CloseConnection option is set. The new DataTable.Load method also can close the DataReader (thus the Connection).

Another point: If you execute a query using one of the other Execute methods (there are several—such as ExecuteScalar), you alone are responsible for opening and (more importantly) closing the SqlConnection object when the query is done. Miss a close and you’re back in deep water.

To test and illustrate these points, I wrote another sample Web application. The following code was extracted from this application and used to generate Table 4 (shown below). The Windows Form version of this code is supplied for downloading. Notice that it uses PerformanceCounter controls to track the number of SQL Server user connections, as well as the number of (global) pooled connections. All of these examples are available on the book’s DVD.

                                            Dim sngLeak As Single  

                                                        Try

                                                                                      ' Capture the number of pooled connections

                                                                                      sngLeak = pcPooledConnections.NextValue

                                                                                      ' Create a new DataReader

                                                                                      Dim dr As SqlDataReader

                                                                                      Dim i As Integer

                                                                                      Dim strAuID As String

                                                                                      lblConnections.Text = "0"

 

Create new Connection and Command objects to execute the query 110 times. These objects and connections should all be closed and discarded before leaving the routine. If not, these objects are orphaned along with the pooled connection. No, the database connections are still released, but the pooled connections are not. I set the connection pool size to 10 to make the program fail faster—if it’s going to. Actually ten connections is plenty for a query that runs as quickly as this one does. I’ve heard from many developers running very active web sites that use fewer than five connections day-in-day-out to handle tens to hundreds of thousands of hits a day.

                                                                                      For i = 1 To 110

                                                                                                                    Dim cn As New SqlConnection("server=.;integrated security=true;max pool size=10")

                                                                                                                  Dim cmd As New SqlCommand("SELECT TOP 5 au_id FROM pubs..authors", cn)

                                                                                                                    lblConnections.Text = i.ToString

                                                                                                                    lblRtn.Text = (i.ToString)

                                                                                                                    cn.Open()       ' DataReader requires a pre-opened connection

 

At this point, I choose whether or not to use the CloseConnection CommandBehavior when executing the DataReader. These decisions are made based on CheckBox controls on the Web form.

                                                                                                                    If cbCBCmdBehavior.Checked Then

                                                                                                                                                  dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                                                                                                                    Else

                                                                                                                                                  dr = cmd.ExecuteReader()

                                                                                                                    End If

At this point, I choose whether or not to bind to a DataGrid or to simply loop through the DataReader rowset.

                          If cbUseGrid.Checked Then

                                                                                                                                                  DataGrid1.DataSource = dr

                                                                                                                                                  DataGrid1.DataBind()

                                                                                                                    Else

                                                                                                                                                  DataGrid1.DataSource = Nothing

                                                                                                                                                  While dr.Read

                                                                                                                                                                             strAuID = dr.GetValue(0).ToString

                                                                                                                                                  End While

                                                                                                                    End If

 

Now, I choose whether or not to manually close the connection or leave it up to some other operation to do so. Frankly, it’s safest to close the connection manually.

                                                                                                                    If cbCloseConnection.Checked Then

                                                                                                                                         cn.Close()                ' This should be enough—it closes the connection

                                                                                                                    Else

                                                                                                                                                  ' No, don't manually close the connection.

                                                                                                                                  ' This should cause a leak if it’s not closed elsewhere.

                                                                                                                    End If

                                                                                                                    If cbCloseDataReader.Checked Then

                                                                                                                                                  dr.Close()

                                                                                                                    Else

                                                                                                                                                  ' No, don't manually close the connection. This might cause a leak.

                                                                                                         End If

                                                                                                                    lblPooledConnections.Text = pcPooledConnections.NextValue.ToString

                                                                                                                    lblUserConnections.Text = pcUserConnections.NextValue.ToString

                                                                                      Next i

                                                                                      lblRtn.Text &= "<BR>" & "All 110 operations complete."

 

If I get here, the operations were a success. 110 connections were made and closed. if something goes wrong, however, the exception handlers will trap the Timeout as an InvalidOperationException, which is how ADO.NET responds when the connection pool is full.

                                                        Catch exI As System.InvalidOperationException

                                                                                      lblRtn.Text &= "<BR>" & "Could not open connection: " & exI.Message

                                                        Catch ex As Exception

                                                                                      lblRtn.Text &= "<BR>" & (ex.ToString)

                                                        Finally

                                                                                      ' Calculate the number of additional pooled connections created. This should be 0.

                                                                                      lblLeaks.Text = (pcPooledConnections.NextValue - sngLeak).ToString

                                                                                      lblRtn.Text &= ("<BR>" & "Done")

                                                        End Try

                          End Sub

 

The following table (Table 4) summarizes how the various options permit the routine to work or fail. Notice that if you don’t set the CloseConnection CommandBehavior option, your operations will (eventually) fail—even when using a bound control. The process will still fail even when using the option if you don’t either use a complex bound control or close the SqlDataAdapter or SqlConnection manually. 

Close Connection	Cn.Close	Dr.Close	Bind to DataGrid	Result	Connections Attempted/Made
(default)				Fail	17/10
(default)		X		Fail	26/10
(default)		X	X	Fail	3/10
X				Fail	27/10
X	X			Okay	110/10
X		X		Okay	110/10
X		X		Okay	110/10
X	X		X	Okay	110/10
X		X	X	Okay	110/10
X	X	X		Okay	110/10
X	X	X	X	Okay	110/10

Table 4: Web application CommandBehavior

How the DataAdapter fares

These sample applications also include routines to return rows with the DataAdapter. However, consider that unless you intervene and manually manage connections (it could happen), the DataAdapter properly opens and closes the SqlConnection object so that you’re not likely to encounter orphaned pooled connections.

How do you know when the connection pool is full?

Once the connection pool reaches Max Pool Size connections, your application will block on any subsequent Open, Fill, or Update method that attempts to open an additional connection. If a connection is made available before ConnectionTimeout seconds, your application is given the recently freed SqlConnection object, returning control to your application. If, however, no connection frees up in time, you’ll trip an InvalidOperationException. At this point, you’ll have to decide what to do; —no, I don’t suggest telling the user that you are out of connections (as in Figure 1.1). What do you expect them to do—; make configuration changes to your server or come down to your business and debug your application?

Figure 1.1 “Developer” exception message returned when the pool is full.

Monitoring the connection pool

Okay, so you opened a connection and closed it and want to know if the connection is still in place—languishing in the connection pool on an air mattress. Well, there are several ways to determine how many connections are still in place (still connected) and even what they are doing. I discuss several of these here and in my book:

·         Use the SQL Profiler with the SQLProfiler TSQL_Replay template for the trace. For those of you familiar with the Profiler, this is easier than polling using SP_WHO.

·         Run SP_WHO or SP_WHO2, which return information from the sysprocesses table on all working processes showing the current status of each process. Generally, there’s one SPID server process per connection. If you named your connection, using the Application Name argument in the connection string, it’ll be easy to find.

·         Use the Performance Monitor (PerfMon) to monitor the pools and connections. I discuss this in detail next.

·         Monitor performance counters in code. This option permits you to display or simply monitor the health of your connection pool and the number of established connections. I discuss this in a subsequent section in this paper.

Summary

As I said, this paper is excerpted from Chapter 9 of Hitchhiker’s Guide to Visual Studio and SQL Server (7^th Edition) that includes a lot more detail, code examples a