SqlClient 中的性能计数器

项目
02/07/2024

适用于：.NET Framework .NET Core .NET Standard

可以使用 Microsoft.Data.SqlClient 性能计数器来监视应用程序的状态及其使用的连接资源。可以使用 Windows 性能监视器来监视性能计数器，或使用 PerformanceCounter 命名空间中的 System.Diagnostics 类以编程方式访问性能计数器。

可用的性能计数器

当前有 14 个不同的性能计数器可用于 Microsoft.Data.SqlClient，如下表所述。

性能计数器	说明
`HardConnectsPerSecond`	每秒与数据库服务器连接的数量。
`HardDisconnectsPerSecond`	每秒与数据库服务器断开连接的数量。
`NumberOfActiveConnectionPoolGroups`	处于活动状态的唯一连接池组的数量。此计数器由 AppDomain 中唯一连接字符串的数量控制。
`NumberOfActiveConnectionPools`	连接池的总数。
`NumberOfActiveConnections`	当前正在使用的活动连接的数量。注意：默认情况下不启用此性能计数器。若要启用此性能计数器，请参见激活默认关闭的计数器。
`NumberOfFreeConnections`	连接池中可用连接的数量。注意：默认情况下不启用此性能计数器。若要启用此性能计数器，请参见激活默认关闭的计数器。
`NumberOfInactiveConnectionPoolGroups`	标记为删除的唯一连接池组的数量。此计数器由 AppDomain 中唯一连接字符串的数量控制。
`NumberOfInactiveConnectionPools`	最近未进行任何活动且等待被释放的非活动连接池的数量。
`NumberOfNonPooledConnections`	未存入池中的活动连接的数量。
`NumberOfPooledConnections`	由连接池基础结构管理的活动连接的数量。
`NumberOfReclaimedConnections`	通过垃圾回收而回收的连接的数量，其中应用程序未调用 `Close` 或 `Dispose`。注意：不显式关闭或处理连接会影响性能。
`NumberOfStasisConnections`	当前正在等待完成某项操作并因此无法供应用程序使用的连接的数量。
`SoftConnectsPerSecond`	从连接池中拉出的活动连接的数量。注意：默认情况下不启用此性能计数器。若要启用此性能计数器，请参见激活默认关闭的计数器。
`SoftDisconnectsPerSecond`	被返回连接池的活动连接的数量。注意：默认情况下不启用此性能计数器。若要启用此性能计数器，请参见激活默认关闭的计数器。

激活默认关闭的计数器

性能计数器 NumberOfFreeConnections、NumberOfActiveConnections、SoftDisconnectsPerSecond 和 SoftConnectsPerSecond 默认情况下为关。将下面的信息添加到应用程序配置文件中，以启用这些信息：

<system.diagnostics>  
  <switches>  
    <add name="ConnectionPoolPerformanceCounterDetail" value="4"/>  
    <!-- A value of 4 corresponds to System.Diagnostics.TraceLevel.Verbose -->
  </switches>  
</system.diagnostics>

检索性能计数器值

下面的控制台应用程序演示如何在应用程序中检索性能计数器的值。若要为所有用于 SQL Server 的 Microsoft SqlClient 数据提供程序性能计数器返回信息，连接必须处于打开状态并保持连接。

注意

本示例使用 AdventureWorks 示例数据库。示例代码中提供的连接字符串假定本地计算机已安装数据库且可用，并且你创建的登录名与连接字符串中提供的登录名匹配。如果使用仅允许 Windows 身份验证的默认安全设置来配置服务器，则需要启用 SQL Server 登录。可以根据您的环境需要修改连接字符串。

示例

using System;
using Microsoft.Data.SqlClient;
using System.Diagnostics;
using System.Runtime.InteropServices;

namespace PerformanceCounterTest
{
    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 retrieve it from a configuration file.  
            return @"Data Source=.;Integrated Security=True;" +
              "Initial Catalog=AdventureWorks";
        }
        private static string GetSqlConnectionString()
        {
            // To avoid storing the connection string in your code,  
            // you can retrieve it from a configuration file.  
            return @"Data Source=.;User Id=<myUserID>;Password=<myPassword>;" +
              "Initial Catalog=AdventureWorks";
        }

        private static string GetSqlConnectionStringDifferent()
        {
            // To avoid storing the connection string in your code,  
            // you can retrieve it from a configuration file.  
            return @"Initial Catalog=AdventureWorks;Data Source=.;" +
              "User Id=<myUserID>;Password=<myPassword>;";
        }
    }
}

Share via

SqlClient 中的性能计数器

可用的性能计数器

激活默认关闭的计数器

检索性能计数器值

示例

另请参阅

反馈

反馈

其他资源