SQL timeout

Aravindan 2017-10-27 11:04:25

Currently working on a requirement for a customer where I need to find root cause of SQL timeout issue. The SQL code embedded within the custom application for the requirement is used for validating charge code of contractors and employees.The issue occurred for three days last month and volume of records inserted into temporary tables used in SQL code was 800+ rows when timeout issue occurred. When executing in production environment, the SQL code takes few minutes that is very much less than timeout of 20 minutes set in the application.

I am also attaching SQL code which was causing timeout last month in our custom app for which we are checking on root cause.

link text

SDyckes2 2017-10-27 21:38:35
At which step in the code was the timeout occurring? With as many queries and conditional steps you have, we will not be able to provide much guidance. If you know what step was causing the timeout, then you can generate the execution plan for the step.

A possible cause could have been out of date statistics causing a sub-optimal plan to be generated that may have taken much longer to complete. But did you rule out any blocking causing the issue?

Aravindan 2017-10-28 16:35:38
Thanks for reply.
• The timeout occurred while inserting data into last temporary table #AccMatters in script.

• Unfortunately, any request to the production server has to go through Production Support. We are not sure whether the support team will provide details of execution plan to us after executing script in production as it is a controlled environment. We have read access to prod database and WILL check with DBA if we cannot get plan from our side.

• The application which has embedded in it the script attached is called 4 times a day and hence we can say almost sure SQL Server may not have generated new execution plan which took time but will need to check.

• We did not have any SQL Server / app server blocks in production server on the day when timeout happened. We also checked whether SQL timeout happened with other applications in tool called splunk where we can get logs. Only the application which embeds the script attached in post reported the issue.

SDyckes2 2017-10-30 21:26:18
With ReadOnly access to production, you may be able to run the SELECT portions of the script, depending on the permissions on TempDB.

A new plan could still have been generated, especially if the code is only run 4 times a day. If the table hit the threshold to update statistics, that would invalidate the current plan in cache, forcing a new plan to be generated. If the new plan was doing a seek before, but now is scanning a table or PK, that could be very problematic, especially with all the JOINs in the code.

Hopefully, you will be able to get some good information from the production team. Depending on the rights on the Production server, you may also be able to use the DMVs to pull plan information from sys.dm_exec_cached_plans, sys.dm_exec_sql_text, and sys.dm_exec_query_plan. If you collect the XML, you can view the plan in Plan Explorer. Just paste the XML in the Plan XML tab.

Aravindan 2017-10-31 15:31:30
Thanks for suggestion. Client has now decided to hold case we are doing due to some sudden developments and decisions with respect to the case. Will come back if anything needed. Thanks for all valuable help and tips provided!! It was very useful. Will come back if there is any change in status of case!!