0
(0)

Problem Description:

Application job failed with timeout error seen in application log.

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.TryRun(RunBehavior runBehavior,SqlCommand cmdHandler,sqlDataReader dataStream)

at​​ System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

Cause:

There are different scenarios in which query can be timed out and below are the possible reasons from database end.

  1. Database blocking
  2. Overall load on database server
  3. Statistics are not upto date
  4. Huge data change could have caused optimizer to choose non-optimal query plan

Possible Resolution:

Below are the possible resolutions to fix this issue.

  • If query has failed. Ask​​ application team to re-run the query​​ to capture the Query execution plan to understand where it is taking more time.​​ Below query can be used to get execution plan.

SELECT​​ EQP.query_plan,​​ *

FROM​​ sys.dm_exec_requests​​ AS​​ ER

 ​​ ​​​​ CROSS​​ APPLY​​ sys.dm_exec_query_plan(ER.plan_handle)​​ AS​​ EQP

WHERE​​ ER.session_id​​ =​​ @@spid​​ --​​ replace @@spid with your SPID

  • Get the sql text from the query plan as shown below and check for last statistics details for captured tables.
  • Also check if there is huge data change for the affected table (refer modification counter column) using below query.
  • If modification counter is high, then run update statistics(with full scan) on affected tables.

update statistics student with fullscan

  • Also, in some scenarios updating the stats does not help in improving the query performance. in such cases, it is better to run sp_recompile post updating the stats. It is because query plan which is available in cache is not optimized to run that specific query due to possible recent changes in the table/index etc which is being used in the query. sp_recompile will delete the existing query plan from the cache and forcing new plan for the next procedure run.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.