0

I have a stored procedure which deployed on different clients.

This stored procedure is working fine all the time.

Sometimes while using the web application, the page which calls this stored procedure keeps loading and will return a time out exception FOR ALL USERS.

To fix this issue, right click on stored procedure, modify, then F5 to execute same procedure (with no change on procedure),

Back to web part and refresh, everything will work fine.

HazemTafour
  • 70
  • 8
  • 2
    sounds like parameter sniffing causing it to get a bad plan every so often – Martin Smith Jan 22 '19 at 07:01
  • 1
    As Martin said, it sounds like parameter sniffing indeed. Dmitrij's answer has one way of sidestepping that problem. Another can be found in [this question](https://stackoverflow.com/q/35477601/243373) on Stack Overflow. It shows two more ways of sidestepping the issue, from which I would prefer my answer in that thread. – TT. Jan 22 '19 at 07:09

1 Answers1

3

It seems that the not very optimal plan is stored to the cache. When you re-create procedure (F5), you clear the cache. When this situation happens again, try to get execution plan and check what params are stored in the cache. Additionally you can add WITH RECOMPILE option, that would probably slow down performance a bit, but you will not have such situations

Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88