(SQL) Tip of the Day: Clear the Proc Cache in SQL Azure

  • Article

Today’s Tip…

You can clear an Azure SQL Database plan cache using this new option similar to running DBCC freeproccache on an on premises.

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

https://msdn.microsoft.com/en-us/library/mt629158.aspx

-- run this script against a user database, not master

-- count number of plans currently in cache

select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache

select count(*) from sys.dm_exec_cached_plans;

-- list available plans

select * from sys.dm_exec_cached_plans;