An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. Execution plans can tell you how a query will be executed, or how a query was executed. Ref: https://www.red-gate.com
Execute the below query to find the cached execution plan.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--====================================== -- Owner: https://www.ourtechideas.com/ --====================================== -- This will give you the list of execution plans cached SELECT PC.plan_handle as [Token for Plan] ,ST.text as [Query in Text Format] ,QP.query_plan [Plan in XML format, click on it to open] ,PC.cacheobjtype as [Chached Plan Type] ,PC.objtype as [Type] FROM sys.dm_exec_cached_plans PC CROSS APPLY sys.dm_exec_sql_text(plan_handle) as ST CROSS APPLY sys.dm_exec_query_plan(PC.plan_handle) QP; |
Now if you want to delete a particular execution plan from the cache then you will need copy the token from the result and pass to DBCC FREEPROCCACHE
1 |
DBCC FREEPROCCACHE |
Example
Before deleting the plan:
Then run
1 |
DBCC FREEPROCCACHE (0x050004007E7D3C25D042C1777500000001000000000000000000000000000000000000000000000000000000) |
And now to see if that got deleted, run the first query again.
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…