Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
MSSQL SQL Server on Linux, SQL server performance issue, SQL server timeout expiredProblem 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.
- Database blocking
- Overall load on database server
- Statistics are not upto date
- 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.