February 5, 2015 at 2:15 pm
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
February 5, 2015 at 2:23 pm
Yes. Why risk not recompiling it?
Get the query plan id and remove all plans related to this proc from the cache using:
DBCC FREEPROCCACHE { plan_handle }
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2015 at 2:19 am
ScottPletcher (2/5/2015)
Why risk not recompiling it?
I would say this risk exists more times than we'd like to admit because we don't always know which SPs are dependent on which tables (unless you search them out).
Certainly what Scott has said is true. If you know the SPs there's no risk in recompiling them.
Some changes (like adding columns to a table) should not affect the SP unless someone did something that's a bad practice, like:
INSERT INTO AlteredTable -- no list of columns specified
SELECT *
FROM AnotherTable;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2015 at 2:27 am
No, you do not.
Altering a table changes the schema version of the table. As part of the pre-execution validation of a cached plan, the schema versions are checked to ensure that the version of the table schema the plan was compiled with matches the current version of the table schema. If they don't, the plan is invalid and gets recompiled.
It's one of the scenarios tested here: http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2015 at 7:20 am
GilaMonster (2/6/2015)
No, you do not.Altering a table changes the schema version of the table. As part of the pre-execution validation of a cached plan, the schema versions are checked to ensure that the version of the table schema the plan was compiled with matches the current version of the table schema. If they don't, the plan is invalid and gets recompiled.
It's one of the scenarios tested here: http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Thank you that article was very insightful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply