SQL timeout

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

avatar image By Aravindan 1 asked Oct 27 at 11:04 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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?

avatar image By SDyckes2 81 answered Oct 27 at 09:38 PM
more ▼
(comments are locked)
avatar image Aravindan Oct 28 at 04:35 PM

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.

avatar image SDyckes2 Oct 30 at 09:26 PM

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.

10|10000 characters needed characters left

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!!

avatar image By Aravindan 1 answered Oct 31 at 03:31 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x4
x2
x2

asked: Oct 27 at 11:04 AM

Seen: 27 times

Last Updated: Oct 31 at 03:31 PM