如何解决连接池问题？

Question

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

System.InvalidOperationException：超时已过期。从池中获取连接之前超时时间已过

如何监视连接池以了解发生了什么情况？

更多信息：

我在这方面运气不太好 - 我绝对没有泄漏连接。每个连接都位于 using 语句内。

当问题确实发生时，我打开了性能监视器窗口，并且它没有显示任何接近池限制（即 100）的地方 - 通常大约有 2 - 5 个连接，所以我认为池没有耗尽，所以也许这是一个超时。

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

当它确实发生时，它发生得相当快 - 我在 VS2010 的调试器下运行 - 启动我的应用程序的一个新实例 - 它可能会在启动后的一两秒内发生 - 在启动应用程序时，有几个查询发生。我实际运行的 SQL Server 是 SQL Express 2008。也许我应该尝试在 SQL 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