SqlClient 中的效能計數器

發行項
05/23/2023

適用於： .NET Framework .NET Core .NET Standard

您可以使用 Microsoft.Data.SqlClient 效能計數器來監視應用程式的狀態，以及其所使用的連線資源。效能計數器可藉由「Windows 效能監視器」來進行監視，或可藉由 PerformanceCounter 命名空間 (Namespace) 中的 System.Diagnostics 類別以程式設計的方式存取。

可用的效能計數器

目前有 14 個不同的效能計數器可供 Microsoft.Data.SqlClient 使用，如下表所述。

效能計數器	描述
`HardConnectsPerSecond`	每秒鐘對資料庫伺服器所建立的連接數目。
`HardDisconnectsPerSecond`	每秒鐘對資料庫伺服器所進行的中斷連接數目。
`NumberOfActiveConnectionPoolGroups`	使用中的唯一連接集區群組的數目。這個計數器是由 AppDomain 中找到的唯一連接字串數目所控制。
`NumberOfActiveConnectionPools`	連接集區的總數。
`NumberOfActiveConnections`	目前使用中的現用連接的數目。注意：此效能計數器預設為未啟用。若要啟用此效能計數器，請參閱啟用依預設關閉的計數器。
`NumberOfFreeConnections`	連接集區中可用的連接數目。注意：此效能計數器預設為未啟用。若要啟用此效能計數器，請參閱啟用依預設關閉的計數器。
`NumberOfInactiveConnectionPoolGroups`	標示為清除的唯一連接集區群組的數目。這個計數器是由 AppDomain 中找到的唯一連接字串數目所控制。
`NumberOfInactiveConnectionPools`	最近未有任何活動且正等候處置 (Dispose) 的非現用連接集區數目。
`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>

擷取效能計數器值

下列主控台應用程式顯示如何在應用程式中擷取效能計數器值。連線必須已開啟並在作用中，才能針對所有 Microsoft SqlClient Data Provider for SQL Server 傳回資訊。

注意

這個範例會使用 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>;";
        }
    }
}

共用方式為

SqlClient 中的效能計數器

可用的效能計數器

啟用依預設關閉的計數器

擷取效能計數器值

範例

另請參閱

意見反應

其他資源