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 ;

  • 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

Reply Children