T SQL clear stored procedure cache

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.

DBCC FREEPROCCACHE

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

use AdventureWorks2014
GO
EXECUTE uspGetManagerEmployees @BusinessEntityID=2
GO
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
DB_NAME(DB_ID()) AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE objtype='Proc'
ORDER BY dbid,usecounts DESC

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.

use AdventureWorks2014
GO
EXECUTE uspGetManagerEmployees @BusinessEntityID=2
GO
EXECUTE Purchasing.uspVendorAllInfo
GO
USE master
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
DB_NAME(DB_ID()) AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE objtype='Proc'
ORDER BY dbid,usecounts DESC

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.

DBCC FREEPROCCACHE(0x05000500B350B70FB049B8000300000001000000000000000000000000000000000000000000000000000000)
GO
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
DB_NAME(DB_ID()) AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE objtype='Proc'
ORDER BY dbid,usecounts DESC

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *