The Spotlight Playback does show past activities but the SQL Activity shows "No data" in the Sessions tab when clicking on the "Rewind" button.
It's also not possible to get a report of the session activity for a certain time range as well.
Spotlight does not store session data by default as it will be too taxing on the Diagnostic Server and very costly on the amount of data to process and store on the Spotlight Statistics Repository (SSR) database and Playback database.
However, when certain alarms are fired off (e.g. high CPU usage) SQL Activity can get collected and stored session information. Hence without setting a schedule, spurts of SQL Activity session data might be present at certain times.
A. If there is a need for continuous SQL Activity data collection fro Sessions please set the scheduling.
1. Go to Configure | Scheduling.
2. In the "Select connection to configure," choose the SQL Server instance(s).
3. In the list of schedules, choose Full Session List.
4. Check "Override the default settings for the collection 'Full Session List'.
5. Choose either to enable "Collect data when Spotlight is not connected" or "Store reporting data in the Spotlight Statistics Repository" or both for Playback and Reporting purposes.
NOTE: Session List collection is very taxing. If setting the schedule to collect for all databases, please be aware that the Playback and Repository database can get large quickly. Make sure to adjust the collection time and data purge time accordingly.
B. Run the attached script to pull data from SSR database. Please modify the timeframe @start_date, @end_date and the parameter @monitoredobject_list in the script.
NOTE: The value of @monitoredobject_list comes from the column monitored_object_name of the table spotlight_monitored_objects.
