Query Plan for highest Usecounts

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


return select sql.text, cp.usecounts

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


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


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


Tags: , , , , , , , ,

SQL Server | SQL Tricks