Drop and create stored procedure

Yesterday we have seen some production issue in my project. While many users accessing the reports in SQL Server, it got timed out and CPU went up to 100%. When we checked in the backend, some 60 sessions were open. We just decided to drop and create the stored procedure. We have dropped and recreate the stored procedure and CPU became 40% and the issue has been resolved.

Can you please let me know what might be the reason for the same. This has happened for the different stored procedure today but we have dropped and recreated the stored procedure immediately to resolve the issue.

The phenomenon is generally known as parameter sniffing. You got a bad execution plan due to an unusual set of parameters, and by dropping and recreating the stored proc, you've just cleared the cached execution plan for that stored procedure.

Related reading material:

• Slow in the Application, Fast in SSMS by Erland Sommarskog

• Why Is This Query Sometimes Fast and Sometimes Slow by Brent Ozar (uh, me)

• How to Start Troubleshooting Parameter Sniffing Issues by Brent Ozar (dang, that guy is good)

StackExchange Database Administrators Q#155882, answer score: 6