Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does altering a stored procedure expire cached execution plans?

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?

like image 257
Kane Avatar asked Sep 06 '11 13:09

Kane


2 Answers

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.

like image 117
Martin Smith Avatar answered Sep 20 '22 22:09

Martin Smith


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.

like image 34
Aaron Bertrand Avatar answered Sep 23 '22 22:09

Aaron Bertrand