000036149 - How to get SQL Server information for troubleshooting performance issues in RSA Adaptive Authentication (On Premise)

Document created by RSA Customer Support Employee on Mar 24, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000036149
Applies ToRSA Product Set: Adaptive Authentication (OnPrem)
RSA Product/Service Type: Database
RSA Version/Condition: 7.x
ResolutionWhen troubleshooting SQL Server performance issues related to SQL Server databases, it is useful to grab the following data:
  1. Application Server Logs
    • App Server log files for the problematic period (catalina logs, stderr, stdout)
  2. AA Server logs
    • AA Server logs during the problematic period (aa_server.log, aa_server.alarm.log, aa_server.forensic.log,  and aa_server.audit.log, aa_re_offline.log)
  3. The output (preferably in excel format) of the below mentioned query (For MSSQL):

select a.sql_handle, a.creation_time, a.last_execution_time, a.execution_count, a.total_physical_reads,
a.last_physical_reads, a.min_physical_reads, a.max_physical_reads, a.total_logical_writes, a.last_logical_writes,
a.min_logical_writes, a.max_logical_writes, a.total_elapsed_time, a.last_elapsed_time, a.min_elapsed_time,
a.max_elapsed_time, b.text
from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text (sql_handle) b
where convert(varchar,a.creation_time,120) >= '<from_date_time>'
and convert(varchar,a.creation_time,120) <= '<to_date_time>'
order by a.max_elapsed_time desc

  • Replace "<from_date_time>" and "<to_date_time>" with the date time in 'yyyy-mm-dd hh24:mi:ss' format.
  • "<from_date_time>" should be at least 1 hour before the issue date time.
  • "<to_date_time>" can be the date time when the output is extracted.
  • This output should be taken at least within 30 - 60 mins after the issue is reported. Otherwise the data from the memory would be flushed.