You can use this T-SQL to get the plan handle for a sproc that has a bad execution plan:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, cp.cacheobjtype AS ObjectType, cp.plan_handle AS PlanHandle, deps.cached_time as CachedDate, deps.last_execution_time AS LastExecuted, deps.total_worker_time AS TotalWorkerTime, st.TEXT AS QueryText, qp.query_plan AS QueryPlan, GETDATE() AS TimeQueryExecuted FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st INNER JOIN sys.dm_exec_procedure_stats deps ON deps.plan_handle = cp.plan_handle WHERE cp.objtype = 'Proc' AND OBJECT_NAME(st.objectid,st.dbid) = 'usp_SomeSproceName'; |
Make sure, to get the proper plan handle text, that you direct the output to text, not a grid.
You can then call the command below to clear the execution plan. Be careful to include the plan handle otherwise you will clear the entire cache!
1 |
DBCC FREEPROCCACHE(0x020000009BCFFB3651C0FFA79B1265F370D260A4369076CB0000000000000000000000000000000000000000); |
Do NOT call this:
1 |
DBCC FREEPROCCACHE |
…as that will clear the entire proc cache and your system will be very slow until it has populated the cache with proc execution plans.
How to Drop One Plan from Cache
Views – 1874