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.

Answer 3

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.