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