Topic
The execution of a SQL SmartObject calling a stored procedure is taking longer than usual and would even hit an unexpected timeout error when it previously did not. This is likely caused by parameter sniffing in SQL.
Instructions
There are two options you may consider to get around this:
- Run the ALTER PROCEDURE command on the stored procedure without making any changes or
- Modify the stored procedure such that OPTION(RECOMPILE) is added to the end of the stored procedure so that the execution plan gets renewed on every execution.
Additional Information
- https://learn.microsoft.com/en-us/answers/questions/585172/i-got-an-error-timeout-expired-after-i-run-alter-p
- https://stackoverflow.com/questions/3398349/stored-procedure-times-out-when-ran-from-code-but-not-from-query-tool
- https://stackoverflow.com/questions/1601939/stored-procedure-timing-out-drop-then-create-and-its-up-again