Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Object Execution Statistics Report in query Expand / Collapse
Author
Message
Posted Wednesday, November 11, 2009 5:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:42 PM
Points: 22, Visits: 1,105
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


  Post Attachments 
OER.JPG (22 views, 71.37 KB)
Post #817089
Posted Wednesday, November 25, 2009 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:42 PM
Points: 22, Visits: 1,105
still awaiting for a solution.
anybody out there please help...
Post #824375
Posted Thursday, July 26, 2012 5:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:13 AM
Points: 179, Visits: 836
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
Post #1335714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse