Performance is something every Developer struggles with at some point or other. In order to do optimization we can see query plan. This article will demonstrate as how we can create the function that will show us the query plan and how we can use it to see plans of objects that are highly used

Below will be the function:

create function QueryPlan(@plan_handle varbinary(max))

returns table

as

return select sql.text, cp.usecounts

,cp.objtype ,cp.size_in_bytes, qp.query_plan

from

sys.dm_exec_sql_text(@plan_handle) as sql cross join

sys.dm_exec_query_plan(@plan_handle) as qp

join sys.dm_exec_cached_plans as cp

on cp.plan_handle=@plan_handle

Now we can iterate through the plans having highest usecounts to view query plans

declare @i as int

declare @iw as nvarchar(300)

set @i=1

while @i<=5

begin

set @iw=(select plan_handle from(select plan_handle , ROW_NUMBER() over(order by usecounts desc) rn from sys.dm_exec_cached_plans )

A where rn=@i)

select * from QueryPlan(0x0500FF7F28E2113CB800892E000000000000000000000000)

set @i=@i+1

end