How to query Spotlight’s Statistics Repository database for historical transactions?

The pursuit of historical data and its analysis is a significant part of the functionality that Spotlight on SQL Server users have come to expect and continue to seek improvements upon.  In the past, users have expressed the need for ad-hoc query capability in order to review historical data.

The ability to review historical monitoring data via a query or a report is a capability that Spotlight users are often requesting.  The direct use of Spotlight’s repository database as a source of this historical data is a viable solution.

In the Spotlight application, the SQL Activity drilldown shows all transactions since the last data collection in the Sessions data grid along with the Top SQL statements in that grid. However, the data (in real-time or playback mode) is as of that point of time while the user might also need to see this data in a different context including a historical view.  A use case might be to locate a specific DDL taking place a few days ago that caused data integrity issue.  In other instances, there might be a need to see a trend of transactions against a specific database or application during a specific time frame.  In such scenarios users can capture the data from Spotlight’s repository database.

By default, Spotlight doesn’t collect data historically for space volume of the repository database. Therefore, it’s first necessary to use the Scheduling option in order to enable the collection of data so Spotlight can start collecting this data.

In order to enable the collection of data and get details on how to query Spotlight repository database to capture historical transactions, these steps should be followed:

1. Enable collection of historical data in Spotlight.

In Spotlight application, from Configure toolbar click Scheduling icon. Choose the monitored connection name from top drop-down list. From list of collections, scroll to Top 'Session List’ and edit this collection. Disable ‘Factory Settings’ option and enable ‘Store reporting data’ option. If needed change the collection interval time which is by default every 5 minutes. Keep in mind increasing the internal frequency will impact the data volume size of the repository database. Repeat the same changes for ‘Top SQL Statement’ collection if needed.

2. Locate Spotlight Statistics Repository database.

From Configure toolbar, select Diagnostic Server | Spotlight Statistics Repository option. Ensure feature is enabled and confirm name of instance and database name.

3. Using SSMS run queries from Statistics Repository database.

a. Obtain name of monitored connection:
exec spotlight_get_monitored_objects

 b. Obtain list of table names:
exec spotlight_get_tables 'sqlserver_spotlight'

c. Obtain list of columns for ‘sqlconnectionlist’ table:
exec spotlight_get_table_columns 'sqlserver_spotlight','sqlconnectionlist'

d. Modify query statement with these values: 1) start_date and end_date  2) monitoredobject_list from step ‘a’ above. Change the included table columns list as needed.

e. Run query to obtain all captured transactions:
EXEC   spotlight_rt_get_batch_data2
       @start_date = N'jan 1 2016',
       @end_date = N'jan 1 2017',
       @table_name ='sqlconnectionlist',
       @column_list=
'active,blockedbyspid,cpurate,currentcommand,currentcpu,
currentio,currentlogicalreads,currentreads,currentwaittime,
currentwaittype,currentwrites,dbname,hostname,hostprocess,
iorate,lastbatchtime,lastwaitresource,lastwaittype,logintime,
memoryusage,netaddress,netlibrary,ntdomain,ntuser,opentransactioncnt,
planhandle,programname,requestid,secondssincelastbatch,sessionkey,
spid,sqlstatement,sqluser,status,systemprocess,totalcpu,
totalio,totallogicalreads,totalreads,totalwrites',
       @monitoredobject_list = N'name of monitored host from step a'

f. Modify query statement with these values:1) start_date and end_date 2) monitoredobject_list from step ‘a’ above. Change the included table columns list as needed.

g. Run query to obtain captured Top SQL transactions:
EXEC   spotlight_rt_get_batch_data2
      @start_date = N'jan 1 2016',
      @end_date = N'jan 1 2017',
      @table_name ='topsqlstatements',       
      @column_list=
'avg_wait_time,avgclr,avgcpu,avgduration,avgphysicalreads,avgreads,
avgwrites,creationtime,database_id,database_name,executioncount,
key_column,last_wait_time,lastclr,lastcpu,lastduration,lastexecutiontime,
lastphysicalreads,lastreads,lastwrites,max_wait_time,maxclr,maxcpu,maxduration,
maxphysicalreads,maxreads,maxwrites,min_wait_time,minclr,mincpu,minduration,
minphysicalreads,minreads,minwrites,plan_generation_num,textdata,total_wait_time,
totalclr,totalcpu,totalduration,totalphysicalreads,totalreads,totalwrites,user_id,user_name',
     @monitoredobject_list = N'name of monitored host from step a'

 

Download a free trial of Spotlight on SQL Server or drop us a note at our Spotlight User Forum Community.

About the Author
Gita Sharifi
Gita Sharifi has been with Quest since 2006 working as a Senior Technical Support Engineer supporting database performance monitoring tools. Prior to joining Quest Gita has held positions in Oracle PL...