偶爾出現SqlException: Timeout expired

我的伺服器上正在執行應用程序。這個應用程序的問題是每天我得到近 10-20，System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding只有我的一個 SP。這是我的SP，

           ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] 
                           (@OS                NVARCHAR(50) 
                           ,@UniqueID          VARCHAR(500)
                           ,@Longitude         FLOAT 
                           ,@Latitude          FLOAT
                           ,@Culture           VARCHAR(10)
                           ,@Other             NVARCHAR(200)
                           ,@IPAddress         VARCHAR(50)
                           ,@NativeDeviceID    VARCHAR(50))
           AS 
           BEGIN 

               DECLARE @OldUniqueID VARCHAR(500) = '-1';
               SELECT @OldUniqueID = [UniqueID] FROM DeviceCatalog WHERE (@NativeDeviceID != '' AND [NativeDeviceID] = @NativeDeviceID);

               BEGIN TRANSACTION [Tran1]
                   BEGIN TRY
                       IF EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @UniqueID) 
                       BEGIN 
                           UPDATE  DeviceCatalog 
                              SET  [OS] = @OS
                                  ,[Location] = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100 ), @Longitude) + ' ' + CONVERT(VARCHAR(100), @Latitude) + ')', 4326)
                                  ,[Culture] = @Culture
                                  ,[Other] = @Other
                                  ,[Lastmodifieddate] = Getdate()
                                  ,[IPAddress] = @IPAddress
                           WHERE   [UniqueID] = @UniqueID;
                       END
                       ELSE 
                       BEGIN
                           INSERT INTO DeviceCatalog
                                       ([OS]
                                       ,[UniqueID]
                                       ,[Location] 
                                       ,[Culture] 
                                       ,[Other]
                                       ,[IPAddress]
                                       ,[NativeDeviceID])
                               VALUES  (@OS
                                       ,@UniqueID
                                       ,geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100) ,@Longitude) + ' ' + CONVERT(VARCHAR(100), @Latitude) + ')', 4326) 
                                       ,@Culture
                                       ,@Other
                                       ,@IPAddress
                                       ,@NativeDeviceID);
                               IF(@OldUniqueID != '-1' AND @OldUniqueID != @UniqueID)
                               BEGIN
                                   EXEC DeleteOldDevice @OldUniqueID, @UniqueID;
                               END
                       END
                       COMMIT TRANSACTION [Tran1];
                   END TRY
                   BEGIN CATCH
                       ROLLBACK TRANSACTION [Tran1];
                       DECLARE @ErrorNumber nchar(5), @ErrorMessage nvarchar(2048);
                       SELECT
                           @ErrorNumber = RIGHT('00000' + ERROR_NUMBER(), 5),
                           @ErrorMessage = @ErrorNumber + ' ' + ERROR_MESSAGE();
                       RAISERROR (@ErrorMessage, 16, 1);
                   END CATCH
           END

這個SP有問題嗎？為什麼我只在這個 SP 中得到超時異常？這是堆棧跟踪，

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
  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 App.Classes.DBLayer.Execute(SqlCommand command, Boolean executeNonQuery)
  at App.Helpers.SQLHelper.GetResult(List`1 parameters, Boolean storedProcedure, String commandText, ResultType type)
  at App.Helpers.SQLHelper.ExecuteNonQuery(List`1 parameters, Boolean storedProcedure, String commandText)
  at App.Services.DeviceCatalogService.InsertOrUpdateDeviceCatalog(DeviceCatalog deviceCataLog)
  at WebApplication1.Handlers.RegisterDevice.ProcessRequest(HttpContext context)
  at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
  at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

您需要在伺服器端對此進行調查，以了解執行超時的原因。注意伺服器沒有超時，超時是由預設的 30 秒 on 引起的SqlCommand.CommandTimeout。

一個很好的資源是Waits and Queues，這是一種診斷 SQL Server 性能瓶頸的方法。根據超時的實際原因，可以採取適當的措施。您必須首先確定您是在處理緩慢的執行（一個糟糕的計劃）還是阻塞。

如果我冒險猜測，我會說不健康的模式IF EXISTS... UPDATE是根本原因。這種模式是不正確的，會導致並發下失敗。同時執行的兩個並發事務IF EXISTS都將得出相同的結論，並且都嘗試INSERTor UPDATE。根據數據庫中的現有約束，您最終可能會出現死鎖（幸運的情況）或失去的寫入（不幸的情況）。然而，只有適當的調查才能揭示真正的根本原因。可能是完全不同的東西，比如自動增長事件。

您的過程也錯誤地處理了 CATCH 塊。您必須始終檢查，XACT_STATE()因為在您的 CATCH 塊執行時事務可能已經回滾。也不清楚您對命名事務的期望，這是我經常看到的一個常見錯誤，與將命名事務與保存點混淆有關。有關正確的模式，請參閱異常處理和嵌套事務。

編輯

這是調查此問題的一種可能方法：

1. 將相關更改CommandTimeout為 0（即無限）。
2. 啟用blocked process threshold，將其設置為 30 秒（前 CommandTimeout）
3. 在 Profiler 中監視阻塞程序報告事件
4. 開始你的工作量
5. 查看 Profiler 是否產生任何報告事件。如果是這樣，他們將查明原因。

如果超時是由阻塞引起的，那麼每次您將獲得超時時，這些操作都會導致“阻塞程序報告”事件。您的應用程序將繼續等待，直到阻塞被刪除，如果阻塞是由活鎖引起的，那麼它將永遠等待。

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