Skip to content

Latest commit

 

History

History
75 lines (58 loc) · 5.64 KB

performance-counters.md

File metadata and controls

75 lines (58 loc) · 5.64 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic dev_langs
Performance counters in SqlClient
Use Microsoft SqlClient Data Provider for SQL Server performance counters to monitor your application status and its connection resources by using Windows Performance Monitor or programmatically in .NET Framework.
David-Engel
davidengel
v-chmalh
12/04/2020
sql
connectivity
conceptual
csharp

Performance counters in SqlClient

[!INCLUDEappliesto-netfx-xxxx-xxxx-md]

[!INCLUDEDriver_ADONET_Download]

You can use xref:Microsoft.Data.SqlClient 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 xref:System.Diagnostics.PerformanceCounter class in the xref:System.Diagnostics namespace.

Available performance counters

Currently there are 14 different performance counters available for xref:Microsoft.Data.SqlClient as described in the following table.

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 Activate 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 Activate 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. Note 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 Activate 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 Activate off-by-default counters.

Activate 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"/>  
    <!-- A value of 4 corresponds to System.Diagnostics.TraceLevel.Verbose -->
  </switches>  
</system.diagnostics>

Retrieve 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 Microsoft SqlClient Data Provider for SQL Server performance counters.

Note

This example uses the sample AdventureWorks database. The connection strings provided in the sample code assume that the database is installed and available on the local computer, and that you have created 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

[!code-csharpSqlClient_PerformanceCounter#1]

See also