Two grids in Spotlight that can show logical and physical writes of queries in history: SQL Activity and Top SQL Statements, both accessed by the Sessions drilldown.
For SQL Activity matrix we use sys.dm_exec_sessions and sys.dm_exec_requests DMVs. For Top SQL Statements, we use dm_exec_query_stats. I’m attaching both scripts for you.
Both grids can be configured to keep history in your Playback database which is by default 7 days. But please keep in we don’t keep history for these collections by default due to possible cost issues against your monitored instance. So, enabling them should be done with caution.
As for querying the Playback database, the data in this database is encrypted and so not designed to run custom queries against. The mentioned Reporting and Trending guide does have a section on how to query Spotlight Statistics Repository which is a subset of the Playback database data.
If you need any further details, please go here to open a new ticket with our support team or I can create one for you.
Visit our Support Portal site
QS_ConnectionList.sql (3.91 KB)