We know that we can get the Object Execution Report in SSMS.
I wish i could generate similar kind of report in a query itself.
I was able to generate Avg. CPU Time(ms.) through query.
The query used for this was the same query which Object Execution Report uses.
here's the query used
Select top 100 percent
ROW_NUMBER() OVER (ORDER BY s3.name, s1.sql_handle)
,case when sch.name is null then '' else '['+sch.name+'].' end as schema_name
-- , sql_handle
-- , sql_handle as chart_display_option
-- , sql_handle as chart_display_optionIO
-- , master.dbo.fn_varbintohexstr(sql_handle)
, dense_rank() over (order by s2.objectid) as SPRank
, s3.name as [Obj Name]
, s3.type as [Obj Type]
, (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]
, ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time]
, ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads]
, ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes]
, ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/execution_count) as [avg_logical_IO]
, total_logical_writes + total_logical_reads
, last_logical_writes +last_logical_reads
, min_logical_writes +min_logical_reads
, max_logical_writes + max_logical_reads
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
inner join sys.objects s3 on ( s2.objectid = s3.object_id )
left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
order by s3.name, s1.sql_handle
--select * from sql_handle_convert_table order by row_id
,sum(t_avg_worker_time) as [avg cpu time]
group by t_obj_name
order by 2 desc
As i said i was able to generate Avg. CPU Time(ms.).
It would be nice if someone could help me in generating other columns of the report as well.
You can download the Standard Reports report files from here
http://blogs.msdn.com/sqlrem/attachment/732910.ashx details mentioned here