SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Object Execution Statistics Report in query


Object Execution Statistics Report in query

Author
Message
Shaun-884394
Shaun-884394
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 1121
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
Attachments
OER.JPG (44 views, 71.00 KB)
Shaun-884394
Shaun-884394
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 1121
still awaiting for a solution.
anybody out there please help...
Ness
Ness
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1663 Visits: 974
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search