Performance Counters (ADO.NET)
ADO.NET 2.0 introduced expanded support for performance counters that includes support for both System.Data.SqlClient and System.Data.OracleClient. The System.Data.SqlClient performance counters available in previous versions of ADO.NET have been deprecated and replaced with the new performance counters discussed in this topic. You can use ADO.NET performance counters to monitor the status of your application and the connection resources that it uses. Performance counters can be monitored by using Windows Performance Monitor or can be accessed programmatically using the PerformanceCounter class in the System.Diagnostics namespace.
Available Performance Counters
Currently there are 14 different performance counters available for System.Data.SqlClient and System.Data.OracleClient as described in the following table. Note that the names for the individual counters are not localized across regional versions of the Microsoft .NET Framework.
Performance counter |
Description |
|---|---|
HardConnectsPerSecond |
The number of connections per second that are being made to a database server. |
HardDisconnectsPerSecond |
The number of disconnects per second that are being made to a database server. |
NumberOfActiveConnectionPoolGroups |
The number of unique connection pool groups that are active. This counter is controlled by the number of unique connection strings that are found in the AppDomain. |
NumberOfActiveConnectionPools |
The total number of connection pools. |
NumberOfActiveConnections |
The number of active connections that are currently in use. Note This performance counter is not enabled by default. To enable this performance counter, see Activating Off-By-Default Counters. |
NumberOfFreeConnections |
The number of connections available for use in the connection pools. Note This performance counter is not enabled by default. To enable this performance counter, see Activating Off-By-Default Counters. |
NumberOfInactiveConnectionPoolGroups |
The number of unique connection pool groups that are marked for pruning. This counter is controlled by the number of unique connection strings that are found in the AppDomain. |
NumberOfInactiveConnectionPools |
The number of inactive connection pools that have not had any recent activity and are waiting to be disposed. |
NumberOfNonPooledConnections |
The number of active connections that are not pooled. |
NumberOfPooledConnections |
The number of active connections that are being managed by the connection pooling infrastructure. |
NumberOfReclaimedConnections |
The number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application. Not explicitly closing or disposing connections hurts performance. |
NumberOfStasisConnections |
The number of connections currently awaiting completion of an action and which are therefore unavailable for use by your application. |
SoftConnectsPerSecond |
The number of active connections being pulled from the connection pool. Note This performance counter is not enabled by default. To enable this performance counter, see Activating Off-By-Default Counters. |
SoftDisconnectsPerSecond |
The number of active connections that are being returned to the connection pool. Note This performance counter is not enabled by default. To enable this performance counter, see Activating Off-By-Default Counters. |
Connection Pool Groups and Connection Pools
When using Windows Authentication (integrated security), you must monitor both the NumberOfActiveConnectionPoolGroups and NumberOfActiveConnectionPools performance counters. The reason is that connection pool groups map to unique connection strings. When integrated security is used, connection pools map to connection strings and additionally create separate pools for individual Windows identities. For example, if Fred and Julie, each within the same AppDomain, both use the connection string "Data Source=MySqlServer;Integrated Security=true", a connection pool group is created for the connection string, and two additional pools are created, one for Fred and one for Julie. If John and Martha use a connection string with an identical SQL Server login, "Data Source=MySqlServer;User Id=lowPrivUser;Password=Strong?Password", then only a single pool is created for the lowPrivUser identity.
Activating Off-By-Default Counters
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:
<system.diagnostics>
<switches>
<add name="ConnectionPoolPerformanceCounterDetail"
value="4"/>
</switches>
</system.diagnostics>
Retrieving Performance Counter Values
The following console application shows how to retrieve performance counter values in your application. Connections must be open and active for information to be returned for all of the ADO.NET performance counters.
Note
This example uses the sample AdventureWorks database included with SQL Server 2005. The connection strings provided in the sample code assume that the database is installed and available on the local computer with an instance name of SqlExpress, and that you have created SQL Server logins that match those supplied in the connection strings. You may need to enable SQL Server logins if your server is configured using the default security settings which allow only Windows Authentication. Modify the connection strings as necessary to suit your environment.
Example
Option Explicit On
Option Strict On
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Runtime.InteropServices
Class Program
Private PerfCounters(9) As PerformanceCounter
Private connection As SqlConnection = New SqlConnection
Public Shared Sub Main()
Dim prog As Program = New Program
' Open a connection and create the performance counters.
prog.connection.ConnectionString = _
GetIntegratedSecurityConnectionString()
prog.SetUpPerformanceCounters()
Console.WriteLine("Available Performance Counters:")
' Create the connections and display the results.
prog.CreateConnections()
Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Sub
Private Sub CreateConnections()
' List the Performance counters.
WritePerformanceCounters()
' Create 4 connections and display counter information.
Dim connection1 As SqlConnection = New SqlConnection( _
GetIntegratedSecurityConnectionString)
connection1.Open()
Console.WriteLine("Opened the 1st Connection:")
WritePerformanceCounters()
Dim connection2 As SqlConnection = New SqlConnection( _
GetSqlConnectionStringDifferent)
connection2.Open()
Console.WriteLine("Opened the 2nd Connection:")
WritePerformanceCounters()
Console.WriteLine("Opened the 3rd Connection:")
Dim connection3 As SqlConnection = New SqlConnection( _
GetSqlConnectionString)
connection3.Open()
WritePerformanceCounters()
Dim connection4 As SqlConnection = New SqlConnection( _
GetSqlConnectionString)
connection4.Open()
Console.WriteLine("Opened the 4th Connection:")
WritePerformanceCounters()
connection1.Close()
Console.WriteLine("Closed the 1st Connection:")
WritePerformanceCounters()
connection2.Close()
Console.WriteLine("Closed the 2nd Connection:")
WritePerformanceCounters()
connection3.Close()
Console.WriteLine("Closed the 3rd Connection:")
WritePerformanceCounters()
connection4.Close()
Console.WriteLine("Closed the 4th Connection:")
WritePerformanceCounters()
End Sub
Private Enum ADO_Net_Performance_Counters
NumberOfActiveConnectionPools
NumberOfReclaimedConnections
HardConnectsPerSecond
HardDisconnectsPerSecond
NumberOfActiveConnectionPoolGroups
NumberOfInactiveConnectionPoolGroups
NumberOfInactiveConnectionPools
NumberOfNonPooledConnections
NumberOfPooledConnections
NumberOfStasisConnections
' The following performance counters are more expensive to track.
' Enable ConnectionPoolPerformanceCounterDetail in your config file.
' SoftConnectsPerSecond
' SoftDisconnectsPerSecond
' NumberOfActiveConnections
' NumberOfFreeConnections
End Enum
Private Sub SetUpPerformanceCounters()
connection.Close()
Me.PerfCounters(9) = New PerformanceCounter()
Dim instanceName As String = GetInstanceName()
Dim apc As Type = GetType(ADO_Net_Performance_Counters)
Dim i As Integer = 0
Dim s As String = ""
For Each s In [Enum].GetNames(apc)
Me.PerfCounters(i) = New PerformanceCounter()
Me.PerfCounters(i).CategoryName = ".NET Data Provider for SqlServer"
Me.PerfCounters(i).CounterName = s
Me.PerfCounters(i).InstanceName = instanceName
i = (i + 1)
Next
End Sub
Private Declare Function GetCurrentProcessId Lib "kernel32.dll" () As Integer
Private Function GetInstanceName() As String
'This works for Winforms apps.
Dim instanceName As String = _
System.Reflection.Assembly.GetEntryAssembly.GetName.Name
' Must replace special characters like (, ), #, /, \\
Dim instanceName2 As String = _
AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
.Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")
'For ASP.NET applications your instanceName will be your CurrentDomain's
'FriendlyName. Replace the line above that sets the instanceName with this:
'instanceName = AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
' .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")
Dim pid As String = GetCurrentProcessId.ToString
instanceName = (instanceName + ("[" & (pid & "]")))
Console.WriteLine("Instance Name: {0}", instanceName)
Console.WriteLine("---------------------------")
Return instanceName
End Function
Private Sub WritePerformanceCounters()
Console.WriteLine("---------------------------")
For Each p As PerformanceCounter In Me.PerfCounters
Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue)
Next
Console.WriteLine("---------------------------")
End Sub
Private Shared Function GetIntegratedSecurityConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrive it from a configuration file.
Return ("Data Source=.\SqlExpress;Integrated Security=True;" &
"Initial Catalog=AdventureWorks")
End Function
Private Shared Function GetSqlConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrive it from a configuration file.
Return ("Data Source=.\SqlExpress;User Id=LowPriv;Password=Data!05;" &
"Initial Catalog=AdventureWorks")
End Function
Private Shared Function GetSqlConnectionStringDifferent() As String
' To avoid storing the connection string in your code,
' you can retrive it from a configuration file.
Return ("Initial Catalog=AdventureWorks;Data Source=.\SqlExpress;" & _
"User Id=LowPriv;Password=Data!05;")
End Function
End Class
using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Runtime.InteropServices;
class Program
{
PerformanceCounter[] PerfCounters = new PerformanceCounter[10];
SqlConnection connection = new SqlConnection();
static void Main()
{
Program prog = new Program();
// Open a connection and create the performance counters.
prog.connection.ConnectionString =
GetIntegratedSecurityConnectionString();
prog.SetUpPerformanceCounters();
Console.WriteLine("Available Performance Counters:");
// Create the connections and display the results.
prog.CreateConnections();
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
private void CreateConnections()
{
// List the Performance counters.
WritePerformanceCounters();
// Create 4 connections and display counter information.
SqlConnection connection1 = new SqlConnection(
GetIntegratedSecurityConnectionString());
connection1.Open();
Console.WriteLine("Opened the 1st Connection:");
WritePerformanceCounters();
SqlConnection connection2 = new SqlConnection(
GetSqlConnectionStringDifferent());
connection2.Open();
Console.WriteLine("Opened the 2nd Connection:");
WritePerformanceCounters();
SqlConnection connection3 = new SqlConnection(
GetSqlConnectionString());
connection3.Open();
Console.WriteLine("Opened the 3rd Connection:");
WritePerformanceCounters();
SqlConnection connection4 = new SqlConnection(
GetSqlConnectionString());
connection4.Open();
Console.WriteLine("Opened the 4th Connection:");
WritePerformanceCounters();
connection1.Close();
Console.WriteLine("Closed the 1st Connection:");
WritePerformanceCounters();
connection2.Close();
Console.WriteLine("Closed the 2nd Connection:");
WritePerformanceCounters();
connection3.Close();
Console.WriteLine("Closed the 3rd Connection:");
WritePerformanceCounters();
connection4.Close();
Console.WriteLine("Closed the 4th Connection:");
WritePerformanceCounters();
}
private enum ADO_Net_Performance_Counters
{
NumberOfActiveConnectionPools,
NumberOfReclaimedConnections,
HardConnectsPerSecond,
HardDisconnectsPerSecond,
NumberOfActiveConnectionPoolGroups,
NumberOfInactiveConnectionPoolGroups,
NumberOfInactiveConnectionPools,
NumberOfNonPooledConnections,
NumberOfPooledConnections,
NumberOfStasisConnections
// The following performance counters are more expensive to track.
// Enable ConnectionPoolPerformanceCounterDetail in your config file.
// SoftConnectsPerSecond
// SoftDisconnectsPerSecond
// NumberOfActiveConnections
// NumberOfFreeConnections
}
private void SetUpPerformanceCounters()
{
connection.Close();
this.PerfCounters = new PerformanceCounter[10];
string instanceName = GetInstanceName();
Type apc = typeof(ADO_Net_Performance_Counters);
int i = 0;
foreach (string s in Enum.GetNames(apc))
{
this.PerfCounters[i] = new PerformanceCounter();
this.PerfCounters[i].CategoryName = ".NET Data Provider for SqlServer";
this.PerfCounters[i].CounterName = s;
this.PerfCounters[i].InstanceName = instanceName;
i++;
}
}
[DllImport("kernel32.dll", SetLastError = true)]
static extern int GetCurrentProcessId();
private string GetInstanceName()
{
//This works for Winforms apps.
string instanceName =
System.Reflection.Assembly.GetEntryAssembly().GetName().Name;
// Must replace special characters like (, ), #, /, \\
string instanceName2 =
AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(', '[')
.Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('\\', '_');
// For ASP.NET applications your instanceName will be your CurrentDomain's
// FriendlyName. Replace the line above that sets the instanceName with this:
// instanceName = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(','[')
// .Replace(')',']').Replace('#','_').Replace('/','_').Replace('\\','_');
string pid = GetCurrentProcessId().ToString();
instanceName = instanceName + "[" + pid + "]";
Console.WriteLine("Instance Name: {0}", instanceName);
Console.WriteLine("---------------------------");
return instanceName;
}
private void WritePerformanceCounters()
{
Console.WriteLine("---------------------------");
foreach (PerformanceCounter p in this.PerfCounters)
{
Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue());
}
Console.WriteLine("---------------------------");
}
private static string GetIntegratedSecurityConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrive it from a configuration file.
return @"Data Source=.\SqlExpress;Integrated Security=True;" +
"Initial Catalog=AdventureWorks";
}
private static string GetSqlConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrive it from a configuration file.
return @"Data Source=.\SqlExpress;User Id=LowPriv;Password=Data!05;" +
// "Initial Catalog=AdventureWorks";
}
private static string GetSqlConnectionStringDifferent()
{
// To avoid storing the connection string in your code,
// you can retrive it from a configuration file.
return @"Initial Catalog=AdventureWorks;Data Source=.\SqlExpress;" +
"User Id=LowPriv;Password=Data!05;";
}
}
See Also
Concepts
OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET)
Performance Counters for ASP.NET
Introduction to Monitoring Performance Thresholds