Does altering a stored procedure expire cached execution plans?

Question

Does executing a ALTER PROCEDURE statement for a stored procedure cause all cached execution plans for that stored procedure to become invalid and expire in SQL Server 2008 / 2005?

Answer 1

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.

Answer 2

Yes. Of course this is easy to test yourself:

1. Create a procedure
2. Execute it a few times
3. Confirm it is cached by checking sys.dm_exec_cached_plans
4. Alter the procedure

5. 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
   

But if your procedure has dynamic SQL, the main Proc plan will disappear, but child plans (Adhoc / Prepared) will remain.

CREATE PROCEDURE dbo.what
AS
BEGIN
  DECLARE @sql nvarchar(max) = N'SELECT x FROM dbo.flange;';
  EXEC sys.sp_executesql @sql;
END
GO

DBCC FREEPROCCACHE;
GO
EXEC dbo.what;
GO
SELECT objtype, c = COUNT(*) 
  FROM sys.db_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
  WHERE t.text LIKE N'%flange%'
  GROUP BY objtype;
GO

Results:

objtype  c
-------  ----
Adhoc    1
Proc     1

Now, alter the procedure (but in such a way that it still produces the same SQL):

ALTER PROCEDURE dbo.what
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql nvarchar(max) = N'SELECT id FROM dbo.flange2;';
  EXEC sys.sp_executesql @sql;
END
GO

The query above yields:

objtype  c
-------  ----
Adhoc    1

Of course this is not a permanent state - other queries and other memory pressure on the system will dictate how long those ad hoc queries stay in the cache.