Does altering a stored procedure expire cached execution plans?

Yes. You can verify this by doing SELECT * FROM sys. Dm_exec_procedure_stats where object_id = object_id('YourProc', 'P') before and after.

From TechNet situations in which plans would be removed from cache include global operations like running DBCC FREEPROCCACHE to clear all plans from cache, as well as changes to a single procedure, such as ALTER PROCEDURE, which would drop all plans for that procedure from cache.

Whilst I do believe you (as you have a great reputation) is this documented on MSDN anywhere – Kane Sep 6 at 13:25 Yes it will be. Will find a link. – Martin Smith Sep 6 at 13:26 msdn.microsoft.Com/en-us/library/ms181055.

Aspx – Aaron Bertrand Sep 6 at 13:28 @Aaron - Doesn't explicitly mention ALTER PROC that I can see. – Martin Smith Sep 6 at 13:31 1 FYI I filed a bug against the documentation.Connect.microsoft.Com/SQLServer/feedback/details/687418 ... feel free to vote/comment. – Aaron Bertrand Sep 6 at 21:54.

Yes. Of course this is easy to test yourself: Create a procedure Execute it a few times Confirm it is cached by checking sys. Dm_exec_cached_plans Alter the procedure The row in sys.

Dm_exec_cached_plans is gone CREATE PROCEDURE dbo. Blat AS SELECT 1; GO EXEC dbo. Blat; GO 5 SELECT COUNT(*) FROM sys.

Dm_exec_cached_plans AS p CROSS APPLY sys. Dm_exec_sql_text(p. Plan_handle) AS s WHERE sql.

Text LIKE '%dbo. Blat%'; 1 ALTER PROCEDURE dbo. Blat AS SELECT 22; GO SELECT COUNT(*) FROM sys.

Dm_exec_cached_plans AS p CROSS APPLY sys. Dm_exec_sql_text(p. Plan_handle) AS s WHERE sql.

Text LIKE '%dbo. Blat%'; 0.

It does - but there may be other factors. Sometimes with severe performance problems, I've found that explicitly running DBCC FREEPROCCACHE can vastly improve performance of the system. Of course you can also explicitly clear the cache for a single sproc if you know it's having problems.

If so optimise for adhoc workloads could be an option. – Martin Smith Sep 6 at 13:55 To be honest I've seen this a few times, spent a lot of time on it the first time trying to resolve - had contractors in and everything. We never got to the route cause only discovered that after a lot of database updates sometimes it was necessary to call DBCC FREEPROCCACHE.

– TheCodeKing Sep 6 at 13:58.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.