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 ;