使用程序外會話狀態的 ASP.NET 應用程序中的 SQL Server 連接問題

我在一個由 4 台 Windows 2003 機器組成的場中部署了幾個 ASP.NET 應用程序。每個應用程序在 IIS 中使用單獨的應用程序池和虛擬目錄。它們嚴重依賴於單個 SQL Server 2000 ( )上的程序外持久化會話。<sessionstate mode="sqlserver" ... />應用程序是針對 .NET 3.0 編譯的，但 .NET 3.5 SP1 安裝在伺服器上。

每個 Web 伺服器每秒接收大約 10 個請求。每隔一段時間，我就會在日誌中遇到一些異常：

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
  at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
  at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
  at System.Data.SqlClient.TdsParserStateObject.ReadByte()
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
  at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

或其他：

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.WriteSni()
  at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
  at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
  at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
  at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

或者另一個：

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
  at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
  at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
  at System.Data.SqlClient.TdsParserStateObject.ReadByte()
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
  at System.Data.SqlClient.SqlDataReader.get_MetaData()
  at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader()
  at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)

這些錯誤每天發生幾次，持續約 1-2 分鐘，然後消失。有沒有人遇到過這樣的問題？為了進一步追查問題，你能建議我做什麼？對我來說，它看起來更像是網路問題而不是應用程序。可能是 SQL Server 上的某些設置無法處理這麼多的並發連接嗎？任何建議將不勝感激。

---

更新：

我通過對應用程序執行主要更新來解決這個問題，以減少會話中儲存的對象的數量和大小。

傳輸級別錯誤通常與連接到 sql server 的連接被破壞……通常是網路。

當 sql 查詢執行時間過長時，通常會拋出 Timeout Expired。

因此，我將對指向您的 Sql Server 的連結進行故障排除，然後監視以查看哪些查詢超時。

聽起來像是 SQL 作業正在執行，備份？這可能是鎖定表或重新啟動服務。

引用自：https://stackoverflow.com/questions/810673