如何解决连接池问题？

Question

我最近开始在我的开发机器上遇到与SQL Server的数据库连接问题。

System.InvalidOperationException:超时过期。从池获得连接之前经过的超时时间。

如何监视连接池以确定发生了什么？

进一步信息：

我在这件事上运气不佳-我绝对不会泄露连接。每个连接都在一个using语句中。

当问题发生时，我打开了Performance窗口，它没有显示在池的极限附近( 100) --通常是2-5个连接，所以我不认为池已经耗尽了，所以可能是超时了。

但是，我已经将ConnectionTimeout设置为0--根据文档，这意味着它应该永远等待连接--但我没有看到这一点。

当它确实发生时，它发生得相当快--我正在VS2010的调试器下运行--启动我的应用程序的一个新实例--它可能在启动后的一两秒钟内发生--在启动应用程序时，会发生几个查询。实际运行的Server是2008。也许我应该尝试在Server 2008上运行它，看看是否有任何不同的行为。

还有其他想法吗？

Answer 1

看看与池相关的ADO.NET性能计数器。

您所描述的症状通常表明您正在泄漏连接。确保所有连接在完成连接后都被释放，最好是在using语句中包装。

Answer 2

下面是一些代码，用于尝试池，然后将故障转移到未池:如果池出现问题，请使用这个子程序：

Public Sub OpenConn()
    Dim sTempCNString As String = cn.ConnectionString

    Try
        ' add a timeout to the cn string, following http://www.15seconds.com/issue/040830.htm
        Dim iTimeOut As Integer = utils_Configuration.Get_ConfigInt("DBConnectTimeout", 0)
        If (iTimeOut > 0 And Not cn.ConnectionString.ToLower.Contains("timeout")) Then
            Diagnostics.Debug.Print("<><><><><><><> SHORT CONNECT WITH POOLING <><><><><><><><><> ")
            cn.ConnectionString += ";Connect Timeout=" & iTimeOut.ToString() & ";"
        End If

        cn.Open()
        IsOperational = True
    Catch ex As Exception
        Diagnostics.Debug.Print("ERROR IN OPENING, try no pool")
        ' see http://www.15seconds.com/issue/040830.htm
        ' turn off pooling
        Diagnostics.Debug.Print("<><><><><><><> CONNECT WITHOUT POOLING <><><><><><><><><> ")
        Dim sAddOn As String = ";Pooling=false;Connect Timeout=45;"
        cn.ConnectionString = sTempCNString & sAddOn
        cn.ConnectionString = cn.ConnectionString.Replace(";;", ";")
        cn.Open()
    End Try
End Sub

下面是一些监视池的代码：

Option Explicit On
Option Strict On

Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Runtime.InteropServices
Imports Microsoft.VisualBasic


' ref: http://msdn2.microsoft.com/en-us/library/ms254503.aspx

Public Class utils_SqlPerfMon

    Private PerfCounters(9) As PerformanceCounter
    Private connection As SqlConnection
    Public sConnectString As String = ""
    Public sResult As String = ""

    Public Sub New()
        sConnectString = Tools.GetMainDBConn().ConnectionString
        connection = New SqlConnection(sConnectString)
        Exec()
    End Sub

    Public Sub New(ByVal strC As String)
        sConnectString = strC
        connection = New SqlConnection(sConnectString)
        Exec()
    End Sub

    Public Sub Exec()

        Me.SetUpPerformanceCounters()
        Diagnostics.Debug.Print("Available Performance Counters:")

        ' Create the connections and display the results.
        Me.CreateConnectionsAndDisplayResults()

    End Sub

    Private Sub CreateConnectionsAndDisplayResults()
        ' List the Performance counters.
        WritePerformanceCounters()

        Dim connection1 As SqlConnection = New SqlConnection( _
           Me.sConnectString)
        connection1.Open()

        Diagnostics.Debug.Print("Opened the 1st Connection:")
        WritePerformanceCounters()

        connection1.Close()
        Diagnostics.Debug.Print("Closed the 1st Connection:")
        WritePerformanceCounters()


        Return


    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 instanceName 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 & "]")))
        Diagnostics.Debug.Print("Instance Name: {0}", instanceName)
        Diagnostics.Debug.Print("---------------------------")
        Return instanceName
    End Function

    Private Sub WritePerformanceCounters()
        Dim sdelim As String = vbCrLf  ' "<br>"
        Diagnostics.Debug.Print("---------------------------")
        sResult += "---------------------------"
        sResult += sdelim

        Dim strTemp As String = ""
        For Each p As PerformanceCounter In Me.PerfCounters
            Try
                Diagnostics.Debug.Print("{0} = {1}", p.CounterName, p.NextValue)
                strTemp = p.CounterName & "=" & p.NextValue.ToString
            Catch ex As Exception
                strTemp = ""
            End Try
            sResult += strTemp
            sResult += sdelim
        Next
        Diagnostics.Debug.Print("---------------------------")
        sResult += "---------------------------"
            sResult += sdelim
    End Sub


    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