0

My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 second when I manually run it... I used extended events query_pre_execution_showplan & query_post_execution_showplan to capture the estimated/actual execution plan. It shows only the estimated execution plan but without the actual execution plan.

Looks like SQL instance just generated the estimated execution plan...

Any thoughts? Many thanks,

Improve this question
1

I imagine that you aren't seeing the "post execution" event because the client canceled the execution.

Improve this answer
8
  • Thank you for your info David, I do have other extended events that captured sql_batch_starting, sql_batch_completed, and attention. Got the duration of sql_batch_completed is 32089676 which is around 30s. The application time-out value is 30s. I am trying to figure out why it was timed out? any suggestion? Thanks, – DBALUKE HUANG Jun 17 '20 at 19:51
  • Timeouts are always requested by the client. – David Browne - Microsoft Jun 17 '20 at 19:54
  • The application timed out because it cannot get the result in 30s. But why SQL server cannot return the result in the time since I manually run it just less than 1 second every time... How can I isolate the root cause? Thanks, – DBALUKE HUANG Jun 17 '20 at 19:54
  • Look at the query plan and the wait stats. You have the estimated plan. If it's the same plan as when it takes 1sec, then it's probably blocking or some other wait. If the plan is different, then try to reproduce it. – David Browne - Microsoft Jun 17 '20 at 19:57
  • 1. The estimated execution plans are the same. 2. I also have blocked_process_report in the meantime but captured nothing... – DBALUKE HUANG Jun 17 '20 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.