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

Interview Question of the Week #033 - How to Invalidate Procedure Cache of SQL Server? icon-clean 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.

Solarwinds

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)

Solarwinds
, ,
Previous Post
Interview Question of the Week #032 – Best Practices Around FileGroups
Next Post
Interview Question of the Week #034 – What is the Difference Between Distinct and Group By

Related Posts

2 Comments. Leave new

  • Are you saying that in some way removing a procedure’s plan from the cache executes the stored procedure ?

    Reply
  • Are you saying that removing a procedure’s plan from cache executes the stored procedure ? That doesn’t seem reasonable.

    Reply

Leave a Reply

Menu