Determining When a Pool Is Full
As I discussed in "Swimming in the .NET Connection Pool," when the connection pool reaches the maximum number of connections you specify with the Max Pool Size ConnectionString option, ADO.NET blocks any subsequent attempt to open an additional connection. If a connection becomes available before the time you specify in the ConnectionTimeout option, the .NET Data Provider passes your application a pointer to that connection, returning control to your application. However, if no connection frees up in time, the connection request trips an InvalidOperationException.
Now you have to decide what to do; I don't suggest telling your users that you're out of connections. Some applications tell users that the system is busy helping other customers and encourage the user to return later. Other applications entertain users with an animation that informs them that the system hasn't locked up but is busy working on their requests. In the meantime, your code retries the operation. In all cases, you should log these failures to help diagnose what's going wrong and document that you've run out of resources.
Monitoring the Pool
So you've opened and closed a connection, and now you want to know whether the connection is still in place. Several techniques let you determine how many connections are still open and what they're doing:
- Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
- Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
- Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
- Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls. Both techniques are included in the sample applications that you can download at http://www.sqlmag.com.
Let's look at how to find connection-pool counters and how to use these monitoring methods.
Where are the connection-pool counters? To monitor the connection-pool counters, you have to watch the system where ADO.NET is creating and incrementing them. ADO.NET doesn't always create a pool on the Microsoft IIS server or SQL Server if you're connecting from a remote system; it creates pools on the system where the ADO.NET code runs. This system could be a remote Windows or middle-tier system running IIS, a Web application, or a Web service. In contrast, the SQL Server performance counters are on the SQL Server system—not on the client.
Using Performance Monitor to monitor pools. If you use the Microsoft Management Console (MMC) Windows 2000 System Monitor snap-in, you can graph the SqlClient counters by selecting .NET CLR Data from the Performance object drop-down list, as Figure 1 shows. Note that you can monitor all processes by selecting the _global_ counter instances, or you can look at a specific instance—each pool generates its own set of counters. Performance Monitor lists these counters and exposes them as instances of the selected performance object. But Performance Monitor doesn't expose these counters unless there are instances to monitor. For example, Figure 1 shows the .NET CLR Data performance object, but no specific instances are listed. This means you have to create at least one connection to get the _global_ instance to appear along with a specific instance for each process. This behavior is a problem for your code; you won't be able to use the PerformanceCounter control to return any of these counters until ADO.NET creates them when it opens the connection. So, it's a bit of a catch-22. The lack of a valid counter instance will cause exceptions when you use this method—just be ready to trap them.
Prev. page
1
2
[3]
4
next page 
