SQL statement extract using spotlight_rt_get_batch_data / sqlfullconnectionslist

I'm querying 'sessions' playback data using the SpotlightStatisticsRepository..spotlight_rt_get_batch_data stored procedure as follows

EXEC spotlight_rt_get_batch_data
@start_date = '2021-05-10',
@end_date = '2021-05-11',
@table_name ='sqlfullconnectionslist',
@column_list= 'active,spid,currentcommand,planhandle,dbname,hostname,lastbatchtime,logintime,netaddress,ntdomain,ntuser,programname,sqluser',
@monitoredobject_list = N'xxxxxxxx_sqlserver'

This works great with the exception that the sql statement associated with each spid isn't available as an output column.

You can see the SQL statement for each spid in the Spotlight app by rolling back to the time you are interested in and clicking on the spid in 'Session details' , however this is a long winded process if you are looking for a specific SQL statement.

Does anyone know of a way to extract historical SQL statements ?

Thanks

John

0 Gita Sharifi over 4 years ago

Hi John,

To reduce the risk of the Repository database size, the sqlfullconnectionslist collection doesn't include SQL statements.

Gita
Spotlight Support
- 0 john barraclough over 4 years ago in reply to Gita Sharifi
  
  Hi Gita
  
  Thanks for your reply.
  
  The SQL statements are available via the application front end, is there any way of querying the SQL statements from within SSMS?
  
  Regards
  
  John
  - 0 Gita Sharifi over 4 years ago in reply to john barraclough
    
    Hi John,
    
    The queryexecutionstatistics collection includes the textdata field holding SQL statements of the top SQL shown in the Query Execution Statistics dashboard. You'll need to enable the Statistics Repository collection of the data first.
    
    I'll message you directly as well.
    
    Gita
    Spotlight Support