November 11, 2009 at 5:22 am
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
;with sql_handle_convert_table(row_id,schema_name/*,t_sql_handle,t_display_option,t_display_optionIO,t_sql_handle_text*/
,t_SPRank,t_obj_name,t_obj_type,t_SQLStatement,t_execution_count,t_plan_generation_num,t_last_execution_time,t_avg_worker_time
,t_total_worker_time,t_last_worker_time,t_min_worker_time,t_max_worker_time,t_avg_logical_reads,t_total_logical_reads
,t_last_logical_reads,t_min_logical_reads,t_max_logical_reads,t_avg_logical_writes,t_total_logical_writes,t_last_logical_writes
,t_min_logical_writes,t_max_logical_writes,t_avg_logical_IO,t_total_logical_IO,t_last_logical_IO,t_min_logical_IO
,t_max_logical_IO
)
as
(
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]
, execution_count
, plan_generation_num
, last_execution_time
, ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time]
, total_worker_time/1000.0
, last_worker_time/1000.0
, min_worker_time/1000.0
, max_worker_time/1000.0
, ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads]
, total_logical_reads
, last_logical_reads
, min_logical_reads
, max_logical_reads
, ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes]
, total_logical_writes
, last_logical_writes
, min_logical_writes
, max_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
select
t_obj_name
,sum(t_avg_worker_time) as [avg cpu time]
from sql_handle_convert_table
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
http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx
November 25, 2009 at 2:07 am
still awaiting for a solution.
anybody out there please help...
July 26, 2012 at 5:44 am
You could always try using SQL profiler when you open the report to capture the sql behind it and reverse engineer what you need 🙂
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply