[解決済み] SqlException を取得することがあります。タイムアウトが発生しました。

質問

私のサーバーでアプリケーションを動かしています。このアプリケーションの問題は、毎日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のうち1つだけです。以下は私の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)

解決方法は？

なぜ実行がタイムアウトになるのか、サーバー側で調査する必要があります。サーバーにはタイムアウトがないことに注意してください。 SqlCommand.CommandTimeout .

良いリソースは 待ち時間と待ち行列 これは、SQL Server のパフォーマンスのボトルネックを診断するための方法論です。タイムアウトの実際の原因に基づいて、適切な対処を行うことができます。まず第一に、実行速度が遅い（計画が悪い）のか、ブロッキングが発生しているのかを確認する必要があります。

あえて推測すると、不健康なパターンである IF EXISTS... UPDATE が根本的な原因です。このパターンは不正確であり、同時実行のもとでは失敗の原因となります。2つの同時実行トランザクションが IF EXISTS を同時に実行すると、どちらも同じ結論に達し 両方 を試みる。 INSERT または UPDATE . データベースの終了制約によって、デッドロック（幸運なケース）または書き込み失敗（不運なケース）に終わる可能性があります。しかし、適切な調査によってのみ、実際の根本原因を明らかにすることができます。次のような、まったく別のものである可能性もあります。 自動成長イベント .

また、CATCHブロックの処理も間違っています。あなたは 常に をチェックします。 XACT_STATE() なぜなら、CATCHブロックが実行される頃には、トランザクションがすでにロールバックされている可能性があるからです。これは、名前付きトランザクションとセーブポイントを混同している場合によく見られる間違いです。正しいパターンとしては 例外処理とネストされたトランザクション .

Edit

これを調査するために考えられる方法を紹介します。

1. 該当する CommandTimeout を0にする（つまり無限大）。
2. を有効にします。 blocked process threshold 30秒に設定します（旧CommandTimeout）。
3. プロファイラで ブロックされたプロセスの報告イベント
4. ワークロードを開始する
5. Profilerが何らかのレポートイベントを生成するかどうかを確認します。発生した場合は、その原因を突き止めます。

これらのアクションは、タイムアウトがブロッキングによって引き起こされた場合、タイムアウトが発生するたびに「ブロックされたプロセスの報告」イベントを発生させます。が原因であれば、ブロックが解除されるまでアプリケーションは待ち続けます。 ライブロック の場合、永遠に待ち続けることになります。