I had an issue where a remote procedure call (from .net) for a specific parameterized stored procedure suddenly would end in a time out. This was the case for only some parameters being set not all. (typically most parameters passed to the stored procedure are null, except for 1 or 2 parameters, which have string values).
This happened suddenly. The stored procedure has not changed in ages. Indexes are well maintained for all tables in the database. I resoled the issue by adding WITH RECOMPILE to the stored procedure. But I am concerned, I want to know what triggered this behavior.
My question is:
- What is/are likely causes of this and/or what potential issues should I be looking for?
- Do I need to to check statistics are healthy (I let SQL Server take care of this for me).
- Is there a way (and should I even be doing this) to pull a list of stored procedures that are at risk of this sudden broken behavior.
Thanks!
