1

In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec timeout)) start working?

The select in SSMS doesn't return any results and can be canceled after 60 seconds. At which point the previous failing SP is now returning results. If you run the select while the SP is being called from code it takes about 2 seconds to run.

No parameters are passed into select, there is a join to a busy event table.

To sum I'm looking for explanation as to how running one select can affect (for the better) another select.

3
  • 3
    Well, did you check for blocking? – Aaron Bertrand Jun 20 '16 at 14:04
  • blocking is very likely but why would running an additional select resolve this? – Mac Jun 20 '16 at 14:52
  • 1
    How have you ruled out coincidence? It just happens to get unblocked and everything starts flowing and you have seen a pattern which isn't really there? – Martin Smith Jun 20 '16 at 16:40
1

You would have to do some digging but one way I can imagine is the SELECT is pulling data pages into cache from disk and subsequent SP runs are just getting their data straight from the buffer pool without having to hit disk.

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.