Tag: cached plan

How do we cleanup plan cache in SQL Server?

At times, we may need to cleanup plan cache in SQL Server. I have come across situations where I need to flush the plans from the cache for our testing purposes
to troubleshoot performance issues related to procedure/test environment.However, there is caution for this to use at production boxes as it may hurt the performance badly.

There are three ways to clean up plan cache as per our requirement.

1. DBCC FREEPROCCACHE

– This DBCC command will clear/clean the entire cached plans from the SQL Server for the instance
– I personally used for my test environment to clean up the entire plan before do a load test and followed by a single user test execution(this to generate fresh plan without affecting the actual load test)


--Clean up the entire plans for the instance
DBCC FREEPROCCACHE with NO_INFOMSGS
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


2. DBCC FLUSHPROCINDB

– This DBCC command will flush the plans for a database
– Database id is a mandatory parameter for FLUSHPROCINDB execution
– This way we will make sure the clean up will not have any impact on other databases


--Clean up the plans for a database 
DBCC FLUSHPROCINDB (8) -- 8 is the databaseid of Dec_ProdStats
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


3. DBCC FREEPROCCACHE with plan handle as parameter

– This is to clean up the cache only for the plan handle supplied

	
--Identify the plan handle
SELECT cp.plan_handle, st.[text],*
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE object_name(objectid)='test'--test is a sample procedure name
GO
--Clean up the plan for TEST procedure
DBCC FREEPROCCACHE(0x05000C001B67A4684061C3B9010000000000000000000000)
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid

Hope you enjoyed this one, see you soon with another one. Meanwhile you can share any feedback or your thoughts here.

Advertisements

How to find index usage from cached plan in SQL Server

Today, I would like to share a script to identify the usage of index from cached plan in SQL Server. This would help us to identify the places (procs/functions etc) where the index is being used. One interesting use-case is to identify the index usage that has been introduced recently during the performance optimization.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE
(
	DatabaseName,SchemaName,TableName,IndexName,
	QueryText,QueryPlan,CacheObjectType,ObjectType
)
AS
(
SELECT	
        RelOp1.op.value(N'@Database', N'varchar(128)') AS DatabaseName,
	RelOp1.op.value(N'@Schema', N'varchar(128)') AS SchemaName,
	RelOp1.op.value(N'@Table', N'varchar(128)') AS TableName,
	RelOp1.op.value(N'@Index', N'varchar(128)') AS IndexName,
	cp.TEXT AS QueryText,cp.query_plan AS QueryPlan,
	cp.cacheobjtype AS CacheObjectType,cp.objtype AS ObjectType
FROM (SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' ) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') RelOp1 (op)
)
SELECT	QueryPlan,QueryText,CacheObjectType,	ObjectType,
		DatabaseName,	SchemaName,TableName,	IndexName
FROM CachedPlansCTE
WHERE CacheObjectType = N'Compiled Plan'
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search for a particular index*/
and (IndexName like '%Indexname%')
/* if you need to search ONLY for SELECT queries*/
and (QueryText not like '%insert%')
and (QueryText not like '%update%')
*********************************************************
*/
OPTION (MAXDOP 1)

See Also:
How to identify Missing Index from Cached Plan in SQL Server
How do you find cached plan for a procedure in SQL Server