Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘query plan’

Drop certain query plan

Posted by Simon Cho on 07/02/2014

DECLARE @ObjName varchar(255)
SET @ObjName = ‘abc’
SELECT c.usecounts
, c.cacheobjtype
, c.objtype
, c.size_in_bytes
, t.text
, p.query_plan
, DB_NAME(p.dbid) AS DBNAME
, p.encrypted
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE usecounts > 1 and t.text like ‘%’+@ObjName+’%’
AND t.text not like ‘%sys.dm_exec_cached_plans%’
GO

–DBCC FREEPROCCACHE (plan_handle);
–ex) DBCC FREEPROCCACHE (0x05000C00F01C44754021DDDA040000000000000000000000);
GO

Posted in Common | Tagged: , , | Leave a Comment »