Occasionally Getting SqlException: Timeout expired

Question

I have application running on my server. The problem with this application is that daily I am getting nearly 10-20, System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding only one of my SP. Here is my 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

Is there is any issue with this SP? Why I am getting Timeout exception only in this SP? Here is the Stack Trace,

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)

Why? Because execution took too long. Optimize it. Impossible to answer this question with the details provided. Voting to close. — usr, Jun 04 '13 at 12:13
did you get solution using ***https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx*** `CommandTimeout` ? — Kiquenet, Dec 03 '15 at 10:52

Remus Rusanu · Accepted Answer · 2013-06-04T12:41:04.247

You need to investigate this on the server side to understand why is the execution timing out. Note that the server has no timeout, the timeout is caused by the default 30 seconds on SqlCommand.CommandTimeout.

A good resource is Waits and Queues, which is a methodology to diagnose performance bottlenecks with SQL Server. Based on the actual cause of the timeout, proper action can be taken. You must establish first and foremost whether you're dealing with slow execution (a bad plan) or blocking.

If I'd venture a guess, I would say that the unhealthy pattern of IF EXISTS... UPDATE is the root cause. This pattern is incorrect and will cause failures under concurrency. Two concurrent transaction executing the IF EXISTS simultaneously will both reach the same conclusion and both attempt to INSERT or UPDATE. Depending on the exiting constraints in the database you can end up with a deadlock (the lucky case) or with a lost write (the unlucky case). However, only proper investigation would reveal the actual root cause. Could be something totally different, like auto-growth events.

Your procedure is also incorrectly handling the CATCH block. You must always check the XACT_STATE() because the transaction may be already rolled back by the time your CATCH block runs. Is also not clear what you expect from naming the transaction, this is a common mistake I see often associated with confusing named transactions with savepoints. For a correct pattern see Exception Handling and Nested Transactions.

Edit

Here is a possible way to investigate this:

Change the relevant CommandTimeout to 0 (ie. infinite).
Enable the blocked process threshold, set it to 30 seconds (the former CommandTimeout)
Monitor in Profiler for Blocked Process Report Event
Start your workload
See if the Profiler produces any report events. If it does, they will pinpoint the cause.

These actions will cause a 'blocked process report' event every time you would had get a timeout, if the timeout was cause by blocking. You application will continue to wait until the blocking is removed, if the blocking is caused by a live-lock then it will wait forever.

The slow part of the query could be in any part. The root cause could be anything. I consider the question to be unanswerable. +1 — usr, Jun 04 '13 at 12:20
@usr, I have lot of extensive and big queries that does not throw this error, only this SQL. I believe there is something wrong in this SP. I think the execution is inserting or updating nothing special. There is no indexing in database and the table include only 800 records. What else inf you need? — Imran Qadir Baksh - Baloch, Jun 04 '13 at 12:31
`IF EXISTS ... UPDATE` alternative: [`MERGE`](http://technet.microsoft.com/en-us/library/bb510625.aspx). You must have a (clustered?) index on `[UniqueID]` (yes, even at `VARCHAR(500)`). — Remus Rusanu, Jun 04 '13 at 12:43
Also, checkout this about [how to use `sp_whoisactive`](http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/) — Remus Rusanu, Jun 04 '13 at 12:48
@RemusRusanu done see http://stackoverflow.com/questions/17039925/danger-of-using-if-exists-update-else-insert-and-what-is-the-alternat — Imran Qadir Baksh - Baloch, Jun 11 '13 at 08:47
http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation — N Khan, Sep 27 '16 at 09:43

Freelancer · Answer 2 · 2013-06-04T12:10:10.940

10

Add this line to your connection string:

Connect Timeout=200; pooling='true'; Max Pool Size=200

You can set myCom.CommandTimeout = 200 also

If large number of data is there you can also increase the timeout seconds from 200 to 600 seconds.

Edit this in web.config as well.

Follow THIS doccument.

edited Jun 04 '13 at 12:10

answered Jun 04 '13 at 12:00

Freelancer

9,008
7
42
81

6

I think he's saying that the procedure taking too long is the problem, and increasing the timeout isn't really addressing the problem. – Bill Gregg Jun 04 '13 at 12:12
2

Increasing the timeout can be a valid solution, especially considering that the OP is obviously not an expert at query tuning. It is an obvious solution however. – usr Jun 04 '13 at 12:27
1

although its obvious solution, i think, OP has not tried it. Because after that i dont think so he would have posted this issue. – Freelancer Jun 04 '13 at 12:29
No this one also takes milliseconds in normal condition. – Imran Qadir Baksh - Baloch Jun 04 '13 at 12:42

score 1 · Answer 3 · answered Oct 31 '14 at 08:27

1

This can happens because of parameter sniffing. So just use local variables, declared within the stored proc. and use them appropriately.

Declare @InVar1 ...

.....

where condition=@Invar1

answered Oct 31 '14 at 08:27

Rahul Vadhan

11
1

score 1 · Answer 4 · answered Mar 06 '18 at 21:23

Maybe the OPTIMIZE FOR or WITH RECOMPILE hints solve the SQL Exception Timeout problem.

This article teachs how to implement it and explains the "parameter sniffing" issues:

https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

Occasionally Getting SqlException: Timeout expired

4 Answers4

Linked