Contents tagged with plan cache

  • 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 … more