Ocasionalmente obtener SqlException: el tiempo de espera expiró
Tengo una aplicación ejecutándose en mi servidor. El problema con esta aplicación es que a diario recibo casi 10-20,System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
sólo uno de mis SP. Aquí está mi 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
¿Hay algún problema con este SP? ¿Por qué tengo una excepción de tiempo de espera solo en este SP? Aquí está el seguimiento de la pila,
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)