So I’m revisiting my performance counters that I run on my Web site that provide some rudimentary performance monitoring for my site. It’s useful to keep track of the number of open SQL Connections, number of pooled connections in the CLR pool, StateServer Sessions and a few other things.

I’ve been doing this for some time in my applications but I’ve noticed that over time the reliability of these counters is really flaky. So tonight I’m trying to take a closer look on my new Vista install as I have an odd database connection failure that I need to track down.

First the .NET CLR Data counters don’t work. I can’t add those even to Performance Manager. I've used these perf counters since .NET 1.x and I suspect these are no longer used for .NET 2.0. So the following is no longer valid (but they used to work in 1.x):

this.AddCounter(".NET CLR Data", "SqlClient: Current # pooled connections");

this.AddCounter(".NET CLR Data", "SqlClient: Current # connection pools");

this.AddCounter(".NET CLR Data", "SqlClient: Total # failed connects");

AddCounter in this case creates a new counter and adds it to a collection for later rendering. I’m not sure, but I suspect that the above is measuring the old Sql client rather than the new managed Sql Client provider.

There are other counters that can be used instead though, but it takes a little more work to pin the individual IIS Application Pool instance:

string InstanceId =AppDomain.CurrentDomain.FriendlyName.ToLower().Replace("/","_") +

 "[" + Process.GetCurrentProcess().Id.ToString() +"]";

this.AddCounter(".NET Data Provider for SqlServer","NumberofPooledConnections",

                InstanceId,"Sql Connection Pool");

this.AddCounter(".NET Data Provider for SqlServer","NumberofActiveConnections",

                InstanceId,"Active SQL Pool Connections");

This works correctly.

But there are other counters that also don’t seem to work right at least here on my Vista install. For example, the StateServer connections don’t show up. I’m running most of my apps with StateServer, yet the StateServer connections counter shows 0:

this.AddCounter("ASP.NET State Service", "State Server Sessions Total",null,

                "State Server Sessions Total");

Another oddity is that if I look in Performance Manager the various CLR counters are process specific so they point at a specific IIS worker process. That’s basically what this code does:

string InstanceId =AppDomain.CurrentDomain.FriendlyName.ToLower().Replace("/","_") +

 "[" + Process.GetCurrentProcess().Id.ToString() +"]";

It identifies the current AppDomain Id and Process Id that is used as the instance Id (is there an easier way to get this?).

However, it looks like the Performance Counters don’t properly detect W3WP processes that have been killed and so the list keeps getting longer and longer and the counters are invariably out of sync when you look at them with Performance Monitor. It’s not really useful to do any sort of CLR monitoring in PerfMon since the Instance Ids will change on any Web recycle which means you can't really create a profile to reuse in the future. Bummer.

All of this PerfMon stuff was brought on by an odd problem I have started to run into with my West Wind Web Store on my development machine with Vista. It turns out the problem is that my logging routine is choking on the IP Address which is coming in as IP6 address which overflows the IP address field in my log file. The Insert Statement fails and due to a bug in the logging code the connection doesn't close. I mentioned this in an earlier post and this is breaking all of my apps that use this logging component.

So there’s a bug in the code that doesn’t close the connection on the early exit when the Insert Error occurs – so the connection stays open and the pool runs out. So the failure at least makes sense and it's an easy fix.

But while I was figure what was causing the problem I couldn't really get a clear picture of the connection scenario happening on the machine. I am logging the above perf counters and while pool connections goes straight up to 10. The Pool Connections I assume are the connections the pool has to Sql Server, which can often be higher than the actual number of current connections, so the maxed out 10 shouldn’t really be a problem.

But the active connection count only shows 0 or 1 (counters are not completely real time so since the request gets logged the connection could be active or not <s>). I would expect NumberOfActiveConnections to give me this value, but apparently it’s not. The actual ActiveConnection count is likely maxed out at 10 as well and so any new requests are getting connections refused. But alas the counter is not showing that.

Anybody know if there’s a way to get the actual active connections in the pool?