Just like most people’s goal towards incorporating more raw food into their daily diet, accessing and manipulating raw data is very much a coveted functionality.
The Spotlight Statistics Repository database is a repository for historical data collected by Spotlight used for the existing 50+ built-in application reports. These reports are found under the Reports tab of the left-hand side panel of Spotlight.
However, if obtaining the raw data from this database and having the control to slice and dice the data freely is what you are itching for, then you are in the right place. This blog is to serve as a catchall document on how to query data from Spotlight repository database.
To know precisely what type of data Spotlight stores in the Spotlight Statistics Repository database, open and download the attached document. Next, follow these steps to query the data from any of the listed collections.
Step 1: Configure Spotlight to start storing historical data
a. From the Configure tab, click the Diagnostic Server icon located on the toolbar. Then, select the Spotlight Statistics Repository option
b. Enable the feature. Enter an instance name to create the database in, along with instance credentials.
c. Click Create. Re-enter the instance credentials if necessary and configure database files if needed. Click Create and OK.
Step 2: Obtain the list of connection names and collection time ranges
a. If you just applied the above steps, then allow Spotlight to collect historical data for at least an hour, preferably a day.
b. In SSMS, connect to the same instance as above and run the query below against Spotlight Statistics Repository database.
select so.monitored_object_name,
min(st.timecollected) as 'start', max(st.timecollected) as 'end'
from
spotlight_timestamps st
join spotlight_monitored_objects so on st.monitored_object_id = so.monitored_object_id group by so.monitored_object_name
c. The suffix names for each Object Name field represents the type of connection. For example, the ’_sqlserver’ suffix name corresponds to SQL Server connections. Windows connections do not have a suffix name. Note the start and end collection dates for each connection.
Step 3: Retrieve data from Spotlight Statistics Repository database
a. Plug in the following values in the script below:
Connection Name = The value of ‘monitored_object_name’ column from step 2b results
Collection Name = The value of ‘SSR Collection Name’ column from the attached file
Start and End Dates = The desired date range of the collected data shown within the ‘start’ and ‘end’ dates from step 2b results
b. Run the script to retrieve the data:
declare @table_name NVARCHAR(255) = N'collection name' -- Enter a value from ‘SSR Collection Name’ column of the attached Excel file
declare @start_date datetime = N'yyyy-mm-dd' --Enter a start date
declare @end_date datetime = N'yyyy-mm-dd' --Enter an end date
declare @monitoredobject_name NVARCHAR(100) = N'connection name' -- Enter a value from ‘monitored_object_name’ column obtained from the query results above, ensure to include the suffix names (_sqlserver) if applicable
declare @domain_names NVARCHAR(255) declare @column_names NVARCHAR(1000)
select @column_names = COALESCE(@column_names + ',', '') + sn.statistic_name
FROM [spotlight_stat_names] as sn
join [spotlight_stat_classes] as sc
on sc.statistic_class_id = sn.statistic_class_id
where sc.statistic_class_name = @table_name
EXEC [dbo].[spotlight_rt_get_batch_data]
@start_date = @start_date,
@end_date = @end_date,
@domain_name_list = @domain_names,
@monitoredobject_list = @monitoredobject_name,
@table_name = @table_name,
@column_list = @column_names
GO
*If script returns no data, verify all variable values are entered correctly.
Additional Notes:
To change the interval collection times of a collection, follow these steps to configure the scheduling settings:
- From the Configure tab, click Scheduling icon from the toolbar. Once the background scheduling window is open, select a Connection or Template from the drop down list.
- Scroll down the list of collections to locate the designated collection and edit it.
- Disable the Factory Settings option. Change the time interval setting under the Store Data section.
- Use the ‘Apply Configure To’ button to apply the change settings to other connections. Use the ‘Save as Template’ button to save settings to a template.
I look forward to your feedback and questions.
Download the latest version of Spotlight on SQL Server Enterprise here.
