Interview Question of the Week #033 – How to Invalidate Procedure Cache of SQL Server?

Question: How to Invalidate Procedure Cache in SQL Server?

Answer: DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Remember not to run this on a production server without proper planning as once you run this DBCC command it will invalidate all the stored procedure plans. This means if the user runs this during pick time, many Stored Procedures will execute at the same time and create a resource bottleneck.

Freeing the procedure cache would cause an ad-hoc SQL statement to be recompiled rather than reused from the cache. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Similarly, we also have following two DBCC commands as well. Just like other DBCC command mentioned above do not run following commands on the production server or you may face performance issues.

DBCC FREESYSTEMCACHE
Releases all unused cache entries from all caches. The SQL Server 2005 Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries.

DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)