Displaying Execution Plans
--Step1: To clear the plans from plan cache
dbcc freeproccache
select * from syslogins
go
SELECT * FROM SYSLOGINS
go
select * from syslogins
--Step2: To display the plans
SELECT qp.query_plan, CP.usecounts, cp.cacheobjtype, cp.size_in_bytes, cp.usecounts, SQLText.text
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
dbcc freeproccache
select * from syslogins
go
SELECT * FROM SYSLOGINS
go
select * from syslogins
--Step2: To display the plans
SELECT qp.query_plan, CP.usecounts, cp.cacheobjtype, cp.size_in_bytes, cp.usecounts, SQLText.text
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
WHERE Text like 'Select%'