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.

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

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.

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.

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