I have a Windows Service app running and sometimes a task which has a method call to a stored procedure (with a Select operation) is keep failing due to a timeout.

But when I execute that SP with same parameters in the SSMS directly, at the first time it takes a while to complete (80 seconds) but then the Windows Service app task starts to work fine.

I checked the actual exection plan, and it shows a 90% cost for a Clustered Index Scan (To a table with 180 million rows).

What I can do to stop this failure without executing the SP in the SSMS every time?

  • 1
    Its sounds like a bad query which isnt using indexes properly. How many rows should the query return? Can you provide the query plan? And/or the query? – Sir Swears-a-lot Apr 12 '18 at 9:28
  • Does your table have other indexes? Can you filter by any other field to reduce the number of rows returned? – Sir Swears-a-lot Apr 12 '18 at 9:33
  • The best solution would be index and query tuning as @Peter suggested. The other option is to increase the command timeout in the application so that it waits for data to be cached the first execution instead of cancelling the query when it takes too long. – Dan Guzman Apr 12 '18 at 11:04
  • @Peter It is a batch wise operation. So it returns 500 rows at a time. Do you have any idea why it works after executing the SP in the SSMS? – Aki T Apr 23 '18 at 6:30
  • Without seeing the query plan and wait stats its really just speculation. But i suspect that the initial load is a tablescan and its reading the entire table into memory. – Sir Swears-a-lot Apr 23 '18 at 8:35

Your Answer

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

Browse other questions tagged or ask your own question.