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.

To know how to query any data set from the Spotlight Repository database, see How to Query the Spotlight Statistics Repository Database blog which serves as a catch-all to ad-hoc query needs.

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.

Anonymous
  • We are looking at the historical data through our working in our areas at our companies. It is sure that in the coming week there will be some competition about this. This is the way the world turns. Many people move on and they try new things. They wanna try to something else etc. We get back to technology again and look at the great work that all of these companies are doing. All industries want to talk about with respect to yahoo is some executive leaving. In today's business environment, The obsession to streamlines business processes has seen many retailers are looking for MDD to help them adopt MS Dynamics for retail systems. Basically, the system optimizes the management process by making it easier. In this article, we are going to look at how to hire developers in Lithuania, How to successfully implement it, and why it is important to have the right team when implementing a retail CRM solution. Because this type of developer is good knowledge and different working process. We should focus on this type of information because it will be good for the future. For more detail, You can click here. With the help of this, You can get good platform.

  • I'd like to connect with you if I can. You can reach me by contacting our support team from this page.

  • Thanks Gita , for your response.  product Version is 12.1.0.9019 , but I am unable to find the table 'sqlfullconnectionslist', and I have  used ' EXEC      spotlight_rt_get_batch_data .....'

  • Hi there, if you're using versions 12.x and later of Spotlight, change the script and run it against spotlight_rt_get_batch_data instead of spotlight_rt_get_batch_data2. Secondly, you'll need to change the table_name parameter value to 'sqlfullconnectionslist'. There's been a few changes in the database structure recently and I'll need to update the blog accordingly. If you're using versions 11.7 or lower and script still returns Null, double check the monitoredobject_list values and ensure it matches step 3a returned values.  Let me know if that works out for you.

Related Content