How to clear the Proc Cache in SQL Azure

To Clear pro cache in Azure, you can not use regular DBCC commands like

DBCC FREESYSTEMCACHE

DBCC FREEPROCCACHE

DBCC FLUSHPROCINDB

In Azure, you have to use Alter database command which will force all queries to recompile in next run. But there is no supported command available to clear the data buffer cache in Azure.

Script to clear the pro cache is below:

/**************************************************/

/*No. of plans available in cache*/

select count(*) from sys.dm_exec_cached_plans;

--> Output: 837

/* To clear the procedure cache in the current database, which means that all queries will have to recompile.s*/

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

--> Output: Command(s) completed successfully.

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

select count(*) from sys.dm_exec_cached_plans;

--> Output: 562

/*list available plans*/

select * from sys.dm_exec_cached_plans;

/**************************************************/

You can not clear the data buffer cache in Azure