ADO.NET 2.0 new Performance Counters

  • Article

Today I want to go over one of the least talked about new ADO.NET features, performance counters. This has been a black mark on the ado.net story since RTM. We found out the hard way that performance counters did not work with URT v1.0 and v1.1, it is only with the new reliability enhancements that have been added to 2.0 that we are now able to get a lot closer to offering counters that have a chance of working.

Considering that the previous model did not work we decided to improve our performance counters considerably (a breaking change). We started by adding Performance object names for 2.0 counters, you will still see the old “.NET Clr Data” counters for backwards compatibility (so that code that references them does not break outright) but the real counters are now under “.NET Data Provider for <server name>”

There are two <server names> available for Whidbey:

SqlClient: “.NET Data Provider for SqlServer”

Oracle Client: “.NET Data Provider for Oracle”

Looking at the counters in each of these categories brings the second big surprise; there are a lot of new counters here! We have taken the best of the old ODBC counters and improved the current set to come up with a long list, if there is anything that you believe we have missed please drop me some feedback. Some of them are too expensive to track explicitly and require you to opt in explicitly (they won’t show any information unless you specifically say that you want to monitor it) for these counters you need to set” “ConnectionPoolPerformanceCounterDetail=verbose” in the app/machine.config. configuration options section.

The easiest way to use these counters is to start your data access application and fire up PerfMon (start->run->PerfMon). CTRL-E clears the pane and CTRL-I brings up the Add Counters modal window. Look in the Performance Object list for the counters for the provider that you are using (.NET Data Provider for SqlServer). If your application is running you should see it by name and process id (I find that it can be very confusing name for asp.net apps! let me know if you have any ideas on how to make this better) in the Instance box. Select your instance and click on the All Counters radio button. Click on add and you should be ready to go.

 

Available counters:

 

HardConnectsPerSecond

(from ODBC) The number of actual connections per second that are being made to a server (ignores pooled connections, only real database connection opens counted)

HardDisconnectsPerSecond

(from ODBC) The number of actual disconnects per second that are being made to a server (ignores pooled connections, only real database disconnects counted)

NumberOfActiveConnectionPoolGroups

(new) The number of unique connection strings.

NumberOfActiveConnectionPools

(from SqlClient) Current number of pools associated with the process or appdomain.

NumberOfActiveConnections (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

(from ODBC) The number of connections in a pool that are currently in use.

NumberOfFreeConnections (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

(from ODBC) The number of connections in a pool that are currently available for use.

NumberOfInactiveConnectionPoolGroups

(new) The number of unique connection strings waiting for pruning.

NumberOfInactiveConnectionPools

(new) Current number of pools associated with the process or appdomain that are waiting to be reclaimed because they are idle.

NumberOfNonPooledConnections

(new) Current number of connections that are not using connection pooling.

NumberOfPooledConnections

(from SqlClient) Current number of connections in all pools associated with the process or appdomain.

NumberOfReclaimedConnections

(new) The number of emancipated connections we reclaim. Emancipated connections happen when the object that owns the DBConnection goes out of scope. We reclaim these connections when the pool runs out of connections or during normal thread cleanup.

NumberOfStasisConnections

(new) The number of connections in the pool that are currently unavailable for us. These include connections enlisted in distributed transactions and connections that are

SoftConnectsPerSecond (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

(from ODBC) The number of connections we get from the pool per second.

SoftDisconnectsPerSecond (too expensive to track, need to enable ConnectionPoolPerformanceCounterDetail)

(from ODBC) The number of connections we return to the pool per second

Many of these performance counters require much more in depth explanation, I will get to them as time permits. Let me know if there are any particular ones you would like to hear about.

Rambling out

Standard Disclaimer: This post is provided “AS IS” and confers no rights.