CODEWITHSUNDEEP
t-sqlstored-proceduressql-server-2016
HazemTafour
HazemTafour

Reputation: 70

SQL Stored procedure stuck strange issue

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.

Upvotes: 0

Views: 1936

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

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

Upvotes: 3

Related Questions