Toad World® Forums

sys.dm_exec_connections in historic info in Spotlight

Hi

I’m specifically looking for whether num_writes is recorded as part of the sys.dm_exec_connections DMV in spotlight historic data, i.e. that i can go back to one week ago and see which query had the highest num_writes?

I’ve looked through this forum but there are really not many posts. Neither on the blog. Is this the best resource for SQL spotlight?

The wiki is empty and the documentation on the Dell site is really thin… so struggling to find any information beyond a “getting started guide”

thanks

Hi,

I’ll look into your request and get back to you shortly.

Gita

In addition, i can see from this manual that it’s pretty much recommended to query the database to get anything the beyond the sproc/APIs

http://documents.quest.com/spotlight-on-sql-server-enterprise/11.6/reporting-and-trending-guide/query-the-spotlight-statistics-repository/table-definitions/spotlight_timestamps

So is there a list anywhere of what DMVs are captured.

Also, importantly, is there any guide on how to query the playback data directly in the SQL database?

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.

Gita
Visit our Support Portal site
QS_ConnectionList.sql (3.91 KB)

Hi Gita

thanks for the quick and informative reply! much appreciated.

I’ll open a ticket for my further questions re the usage of sys.dm_exec_connections and querying the Spotlight Statistics Repository