Pull logical reads SP's

Hi ,

I need to pull data from Spotlight repository like what gets displayed in 'Workload analysis page' ( sorted by Logical reads , CPU ) and store it somewhere for custom reporting . 

Can someone please provide the query to get this info ? 

Is there any other way of directly running T-SQL and getting Top 50 SP's with high logical reads ? 

Also tried running this SP and no results . But when I view it on Spotlight client , i see the data . can someone please help .

EXEC [dbo].[spotlight_rep_top_sql]
@start_date = @start,
@end_date = @end,
@domain_name = @domain,
@monitored_object_name =@monitored_object_name ;

I am using the below SQL and getting this message : " SQL Analysis is not enabled for this monitored object "

declare @start datetime
declare @end datetime
declare @domain varchar(50)
declare @monitored_object_name varchar(100)

set @start = dateadd(day, -1, getdate())
set @end = getdate()

select @domain=domain_description from [dbo].[spotlight_domains]

	select @monitored_object_name = mo.monitored_object_name 
		from [dbo].[spotlight_monitored_objects] as mo
		inner join [dbo].[spotlight_domains] as d
			on mo.domain_id = d.domain_id
			and d.domain_description = @domain

EXEC [dbo].[spotlight_rt_get_aggregated_workload_analysis]
			@start_date = @start,
			@end_date = @end,
			@domain_name = @domain,
			@monitoredobject = @monitored_object_name ;

No Data
  • Hi,
    Spotlight doesn't store the data of Workload Analyzer drilldown into the Spotlight Statistics Repository database. This screen does have an Export icon located on the top-right corner, shaped like a disk, where you can export the reported top SQL to a file. Another drilldown you might be interested in is the Query Execution Statistics tab of the Sessions drilldown. This screen also has export capability.

    Hope that helps.

    Spotlight Support Team