Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).
After suspecting parameter sniffing as the culprit, I masked the input parameters, and the query executed faster.
My question is: why did this happen?
This system has been in production for more than 5 years, and this is the first time we've seen anything like this on our stored procedures. Is this "Database wear and tear"?
We've resolved the issue, so it isn't a big deal, but I'm just curious as to why this was happening.
DROP
/CREATE PROC
.sql files, I typically include anEXEC
for the sproc, with realistic parameters. Thus, any time the sproc is recreated, it gets run once with good params and (should) get a good execution plan.