When we were working on performance baseline or performance improvement we might be clearing down the procedure cache to get the exact statistics of the stored procedure. Many of them are already aware how to do it in hosue SQL Server. We might be using anyone of the below command to clear the procedure cache.
- DBCC FREESYSTEMCACHE
- DBCC FREEPROCCACHE
- DBCC FLUSHPROCINDB
If you are working with SQL Azure then above commands wont work to clear down the plan or procedure cache.
Microsoft added new TSQL statement in SQL Azure database as well as in SQL Server 2016 you can use the below command to clear out the procedure cache. Clear the procedure cache , run the procedure again to get the statistics.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;
- SQL Azure database
- SQL Server 2016 and Above