SQL Server - Execution Timeout Expired query

Mark 21 Reputation points
2021-03-13T01:23:21.867+00:00

Hi

I am have encountered an issue with SQL Server timeouts and I'm not sure how to troubleshoot.

Basically, when users are using our software it is 'crashing' very frequently at random points in the software when it has to run a query in the database. When we check the logs we are setting the same error message.

1/03/2021 19:16:40 : Thread1 : CustomerCheckout::CustomerCheckout_Load - Exception: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
11/03/2021 19:16:41 : Thread1 : TaskMgr::AddTask - Exception: Execution 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, Action`1 wrapCloseInAction)

The customer has SQL Server replication and if they use the software pointing to the subscription the issue does not happen so it has to be something on the main publisher DB that is causing these timeouts.

I've dealt with deadlocks in the past however I've never encountered issues with timeouts happening. What is the best way to troubleshoot these timeouts further? Is there anything in SQL Server that can be used to narrow down on the issue?

Any help is much appreciated.

Thanks,
Mark

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,772 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-03-13T10:27:04.81+00:00

    First, these timeouts occur in the client, not in SQL Server. By default, most client APIs get tired of waiting for response from the database after 30 seconds, whereupon they tell SQL Server to stop running the submitted batch and then they throw an error to the rest of the application.

    There can be many reasons for these timeouts. It may be perfectly normal that the batch runs for a few minutes, because it needs to process a lot of data. The remedy is to adjust the timeout which is a property on the command object. My recommendation is that if you have no other requirement to set the timeout to 0, which means wait forever. (And in my not-so-humble opinion, this should be default. Nothing else makes sense.)

    But it could also be that the batch is expected to complete in a second, but runs for a much longer time, because of an issue with the query plan.

    Another possible reason is blocking. That is, one or more rows that the SQL code needs to access are locked by another process for a longer time. That could be for all sorts of reasons, including someone starting a transaction in SSMS but never committing or rolling it back.

    Given that the issue only happens on the publisher and not on the subscriber, suggests that this is a blocking problem. However, it could also be that you get different query plans in the two environments.

    The first step is to identify which operations that are failing. Is it always the same query that fails? Or are the multiple, all accessing the same table? Are the queries expected to always be quick? The answers to these questions will lead you whether this is a blocking problem a or an issue with a long-running query.

    2 people found this answer helpful.
    0 comments
  2. CarrinWu-MSFT 6,856 Reputation points
    2021-03-15T07:58:52.82+00:00

    Hi @Mark ,

    Firstly, you could confirm with client which operation will execute timeout issue. Is it the issue can be reproduced every time? What kind of query does client run in database that cause this issue?

    Here have some reasons will cause timeout issue:

    1. There's a deadlock somewhere
    2. The database's statistics and/or query plan cache are incorrect -> It can be resolved by clearing the statistics: exec sp_updatestats. If that doesn't work, you could also try: dbcc freeproccache
    3. The query is too complex and needs to be tuned

    You can get more information from this link.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. neo 1 Reputation point
    2022-04-11T21:25:34.173+00:00

    ErlandSomarskog gave you the most valuable answer.
    I have recently come across with the same issue and would like to share what I learned.

    There is a place to change/set your query timeout in your .NET code when you have SqlCommand object to run your query, specify that .CommandTimeOut property to some number, that is in seconds, it is default to 30 seconds if you don't give any value.

    in my case, VB .NET
    Dim cmd As New SqlCommand ... With cmd .Connection = New SqlConnection(ConnectStringBuild(ConnectionNo)) .Connection.Open() .CommandText = SQLStatement .CommandTimeout = 60 '' 1 minute

    and further more, I advise that you analyse your SQL statement/commands you are running. Optimise it for a better performance and use of the resources. Use Pkeys to join the queries or create necessary indexes if the select queries much more important than the insert/update operations.