How to clean plan query cache

Hello everybody,

some sql notes:

dbcc freeproccache; this one will clean plan of cached of sql.

Another note of how to find Sql code and plan by id from dm_exec_cached_plans

create function SqlAndPlan(@handle varbinary(max))

returns table

as

return select sql.text, cp.usecounts,cp.cacheobjtype,

cp.objtype, cp.size_in_bytes,

qp.query_plan

 from

 sys.dm_exec_sql_text(@handle) as sql cross join

 sys.dm_exec_query_plan(@handle) as qp

 join sys.dm_exec_cached_plans as cp

 on cp.plan_handle = @handle;

then you can use it in the following way:

 select * from SqlAndPlan(0x06650D007852A00B40613C82000000000000000000000032)

And in order to see sql and plan simultanously you can use following view:

create view PlanCache

as

select sp.* from sys.dm_exec_cached_plans   as cp

cross apply SqlAndPlan(cp.plan_handle) as sp

No Comments

Add a Comment