2

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!

  • Well, do you have copies of the before and after execution plans? Is it possible there was blocking (perhaps due to the schema change)? Minutes -> hours is typically not "suddenly slow query" - it's usually more fundamental than that. – Aaron Bertrand Jul 1 '15 at 16:27
  • Hey Aaron, thanks for the response. unfortunately I don't have this info as I was brought in after the 'fix' was put into place. Would there ever need to be a need to recompile procs associated to a schema change? – Jeff A Jul 1 '15 at 16:33
  • schema change should invalidate all plans associated with a specific object and therefore force a recompile implicitly - but of the statement(s), not the entire procedure. In order to figure out why the query took that long when it did, you'd need to reproduce the scenario and examine / compare the plans, the wait statistics, and whether the query was blocked. There could be a dozen reasons it took a long time but it'd be tough to pick the right one after the fact. – Aaron Bertrand Jul 1 '15 at 16:37
  • that makes sense. I will ask that if this issue comes up again after the schema change that I get a heads up to copy the execution plan. Just odd to be isolated to this one proc as there has to be dozens that should have recompiled. thanks for your input much appreciated! – Jeff A Jul 1 '15 at 16:41
1

Had a situation where a production environment had DB schema updated

The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why.

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query.

From BOL - Recompiling Execution Plans :

enter image description here

  • I understand that the schema change should have invalidated the execution plan but still not sure why a single procedure may not have been recompiled. I know not having the execution plan before and after makes it difficult to know what exactly happened but at this point looking for suggestions on how to prevent this in the future. Should I force a recompile on all procs after a schema update or is that overkill for this one occurance? – Jeff A Jul 1 '15 at 17:07
  • If you do a schema change, SQL Server will automatically force a recompile - discarding all the plans associated with that object and regenerating a new plan. – Kin Shah Jul 1 '15 at 17:10
  • is there any situation that a procedures plans would not be discarded after a schema change? It appears that's what happened in this one case but unfortunately I do not the before and after execution plans – Jeff A Jul 2 '15 at 14:55
  • @JeffA No, unless you force sql server to use a specific plan using PLAN GUIDE which I dont see a reason to do. Most of the time the query optimizer is smart enough to come up with a good plan. – Kin Shah Jul 2 '15 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.