Connection Pooling for the .NET Framework Data Provider for SQL Server

http://www.west-wind.com/weblog/posts/9024.aspx

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)

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx - .NET Framework 1.1

Performance Counters for Connection Pooling

The .NET Framework Data Provider for SQL Server adds several performance counters that enable you to fine-tune connection pooling characteristics, detect intermittent problems related to failed connection attempts, and detect problems related to timed-out requests to your SQL Server.

The following table lists the connection pooling counters that can be accessed in Performance Monitor under the ".NET CLR Data" performance object.

Counter	Description
SqlClient: Current # of pooled and non pooled connections	Current number of connections, pooled or not.
SqlClient: Current # pooled connections	Current number of connections in all pools associated with the process.
SqlClient: Current # connection pools	Current number of pools associated with the process.
SqlClient: Peak # pooled connections	The highest number of connections in all pools since the process started. Note: this counter is only available when associated with a specific process instance. The _Global instance will always return 0.
SqlClient: Total # failed connects	The total number of connection open attempts that have failed for any reason.

Note    When using the .NET Framework Data Provider for SQL Server performance counters in conjunction with ASP.NET applications, only the _Global instance is available. As a result, the value returned by the performance counter is the sum of the counter values for all ASP.NET applications.

We will use '.NET Data Provider for SqlServer' counter from .NET 2.0 and above.

 

Activating Off-By-Default Counters

http://msdn.microsoft.com/en-us/library/ms254503

The performance counters NumberOfFreeConnections , NumberOfActiveConnections , SoftDisconnectsPerSecond , and SoftConnectsPerSecond are off by default . Add the following information to the application's configuration file to enable them:

Copy

<system.diagnostics>

  <switches>

    <add name="ConnectionPoolPerformanceCounterDetail"

         value="4"/>

  </switches>

</system.diagnostics>

 

http://www.c-sharpcorner.com/UploadFile/CMouli/PerfCounters10142009095556AM/PerfCounters.aspx

Database Connection Counters
Let's briefly look at the various counters in ADO .Net that are required to monitor.

NumberOfActiveConnectionPools	When connection pooling is configured in the applications, connection pools will be created and connections (min Size mentioned) will be established. This counter tells how many such connection pools are created.
NumberOfActiveConnections	This value denotes how many active connections that are currently being used.
NumberOfFreeConnections	This shows how many number of connections available in the usable state in the pool.
HardConnectsPerSecond	This value denotes the number of connection made to the database per second.
HardDisconnectsPerSecond	This value denotes the number of connections disconnected from the database per second.
SoftDisconnectsPerSecond	This value shows the number of connections that are pulled out of the pool per second.
SoftConnectsPerSecond	This value shows the number of connections that are returned to the pool per second.
NumberOfReclaimedConnections	This shows the number of ACTIVE connections that have reclaimed by the garbage collector without explicit close or dispose connection calls from the creator.
NumberOfPooledConnections	NumberOfPooledConnections