You can also monitor the number of open connections by using the SQL Server performance counter User Connections. This counter is listed under SQL Server: General Statistics in the Performance object dropdown list. I like to monitor the User Connections value and some selected .NET CLR Data SqlClient counters (which I describe in a moment) because I can get the information I want without worrying about instances.
Using code to monitor performance counters. When you need to monitor the connection pool programmatically, you can write code to monitor the SqlClient-managed performance counters—the same counters that the MMC Windows NT Performance Monitor snap-in exposes. Writing the code that performs the monitoring is somewhat daunting. But I've included a snapshot of the routine that extracts these counters from the inner workings of the SqlClient provider as one of the downloadable programs included with this article.
You can write code that examines the five counters that Table 2 shows. These five counters let you monitor the connection pool in realtime. .NET expects you to provide a category—dubbed Performance Object in the Performance Monitor—and select the appropriate counters from those registered with the system. To access the SqlClient counters, set the category to .NET CLR Data.
Using PerformanceCounter controls. You might find adding a PerformanceCounter to your application form at design time easier than hand-coding access to the performance counters. To do so, select a PerformanceCounter from the Visual Studio .NET Toolbox Components menu, drag it to your application form, and set the properties as Figure 2 shows. These controls work in Web forms and in WinForms applications.
Because the PerformanceCounter control exposes convenient drop-down lists, you can see any of the performance-counter categories, counter names, and specific instances at design time—except the instance you're about to run. This means you'll have to use the technique that Figure 2 shows to capture the appropriate instance of the pool your application is using. To bypass this problem, I select the _global_ instance. Again, this technique assumes that some application has already created at least one pool, so you need to be prepared for ADO.NET to throw an exception when no counter instances exist, just as it does when no pooled connections exist.
Beware of inaccurate pool counts. Because of a bug in the SqlClient .NET Data Provider that wasn't fixed in .NET Framework 1.1, the performance counters incorrectly indicate that pools remain "alive" when in fact they've been deleted. I was able to verify that the pools no longer existed by ending the MMC Performance Monitor snap-in, then ending Visual Studio .NET. These steps showed that the .NET Data Provider properly deleted connection pools when the process that created them ended. Obviously, this inaccuracy reduces the usefulness of the performance counters for monitoring the pools, but I hope that Microsoft will resolve this problem in the future.
What the Counters Don't Show
One problem you might face is that the configuration of each pool isn't available from the counters or SqlClient properties. Each SqlConnection object's ConnectionString holds the key to these pool settings. Because you can't depend on the default settings, it's tough to determine whether the pool is almost full or hardly being used. This would be another handy feature of a future version of ADO.NET.
However, assuming you know the values for the various connection pool ConnectionString arguments, the code in Listing 1 makes it simple to set up a timer to check a specific pool you created and report when it's x percent full. Then, a monitoring application could alert you so that you could resolve the problem and prevent an overflow.
Finally, remember that ADO.NET performs differently from COM-based ADO. Visual Basic .NET radically changes the way objects are discarded and no longer guarantees that a Connection object will be closed when it falls out of scope. Be sure that the SqlConnection object (or any Connection object) is closed before it falls out of scope.
Connection pooling is a powerful functionality that can improve your applications' performance. But if you aren't a good lifeguard, your connection pools can become a detriment instead of a benefit. I hope the techniques in this article help you effectively monitor your pools and keep your users happy.
End of Article
Prev. page
1
2
3
[4]
next page -->
