I would like to understand how an
alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a particular stored procedure (that referenced a table that had schema changes but required no modifications) that normally ran in minutes went to hours. The procedure does consolidation work and has to query a table with about 800 million records and this table was modified as part of the schema update (table swap). We found that when we made a copy of the procedure and executed it the normal execution time returned but the original procedure performance did not improve. The developer executed an
alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. I'm not clear what resolved our issues but I'm more concerned how to keep it from happening again. The procedure didn't have an explicit recompile statement so I'm not sure it was implicitly recompiled and the cache plan replaced.
Again, I am just trying to understand the mechanics of how altering the procedure resolved the issue so I can plan accordingly for any schema changes.
I appreciate your input!