SQL Server stores the compiled plans of procedures being executed so as to increase procedure performance. A plan is compiled and saved first time a procedure is executed and is used for subsequent procedure executions. Many a times it is required to clean up the cache however it is not recommended to run on production environment as it can cause severe performance issues. A TSQL to clear stored procedure cache is given below.

Let’s analyze the procedure cache by running the below query.

The above query first executes a procedure uspGetManagerEmployees so that it’s compiled and saved in plan cache and then queries the plan cache. The plan cache details are saved in sys.dm_exec_cached_plans view. The output from above query is shown below. The plan for procedure is compiled and is available in plan cache.

1_tsql clear stored procedure cache

Let’s execute the DBCC FREEPROCCACHE and query the cache again.

2_tsql clear stored procedure cache

The plan cache is cleared and there isn’t any compiled plan for procedure uspGetManagerEmployees.

Starting from SQL Server 2008, the freeproccache command can remove a particular stored procedure plan from the plan cache. Let’s executes two different procedure so that we have there planned cached.

The above query executes uspGetManagerEmployees and uspVendorAllInfo and then queries the plan cache. The output from above query is shown below.

3_tsql clear stored procedure cache

The DBCC FREEPROCCACHE takes plan_handle as parameter and removes that particular procedure from the plan cache.

The output from the above query is shown below. The plan for the procedure uspGetManagerEmployees is cleared from the plan cache.

4_tsql clear stored procedure cache

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook