000035802 - How to run a SQL Server Profiler Trace to troubleshoot RSA Archer performance issues

Document created by RSA Customer Support Employee on Jul 6, 2020
Version 1Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000035802
Applies ToRSA Product Set: Archer
RSA Product/Service Type: Platform
RSA Version/Condition: All
IssueHow to run a SQL Trace to troubleshoot RSA Archer performance issues.  

For some Archer users, opening or saving Archer content records is very slow for some applications, but not all. This can be caused by a bad data query plan associated with a SQL stored procedure. Running a SQL Trace may help identify the stored procedure(s).

NOTE: Running a SQL trace requires elevated rights on the SQL Server. Please reach out to your SQL DBA to run the trace.
Resolution
  1. Run the following SQL command to get session id from a list of users logged into Archer.

    SELECT s.sessionId, s.userId AS UserId, lc.user_domain AS Domain, u.user_username AS UserName, u.user_display_name AS DisplayName
    , uv.xuserconttype_value AS DefaultEmail
    , ut.user_type_name AS UserType
    , uv.sec_prm_name AS SecurityParameter
    , CONVERT(varchar,(GETUTCDATE() - s.create_date),108) AS TimeIn
    , CONVERT(varchar,(GETUTCDATE() - s.session_Timestamp),108) AS TimeIdle
    , CAST(CASE WHEN GETUTCDATE() > s.session_Expires THEN 'Expired' ELSE CONVERT(varchar,(s.session_Expires - GETUTCDATE()),108) END AS varchar) AS TimeLeft
    , s.create_date AS LoginTime, s.session_Timestamp AS LastActivity, s.session_Expires AS Expires
    , uv.logged_in AS IsLoggedIn, s.is_impersonated AS IsImpersonated
    FROM tblSession s WITH ( NOLOCK )
    LEFT JOIN tblUser u ON u.user_id = s.userId
    LEFT JOIN viewUserList uv WITH ( NOLOCK ) ON uv.user_id = s.userId
    LEFT JOIN tblUserType ut WITH ( NOLOCK ) ON ut.user_type_id = u.user_type_id
    LEFT JOIN tblLdapConfig lc WITH(NOLOCK) ON lc.config_id = u.ldap_config_id
    WHERE uv.logged_in = 1 AND uv.is_impersonated = 0 AND u.user_type_id IN (1,3)


    User-added image
 


  1. Copy the sessionId from results.



  2. From SQL Management Studio, open SQL Server Profiler.



  3. From the Events Selection tab, enable the Show all columns option.



  4. From the Events Selection tab, select the Stored Procedures RPC: Completed event and all columns.



User-added image



  1. Click the Column Filters button and select TextData.



  2. Expand the Like option and paste the session id within percent signs: %sessionid%



User-added image



  1. Confirm the end user is ready to reproduce the issue.



  2. Click the Run button.



  3. Reproduce the issue while the trace is running.



  4. After reproducing the issue, wait one minute to make sure all traffic was captured.



  5. Stop the trace.



  6. Save the trace to a trace file (.trc).



  7. In the results, check the Duration column for any rows running a long time.  NOTE: the time is in milliseconds.



  8. The TextData value will contain the name of the SQL stored procedure.



  9. From SQL Management Studio, open a new query window.



  10. For each stored procedure taking a long time, clear the bad data query plan by running the following SQL command after replacing "stored procedure name".



    sp_recompile 'stored procedure name'


Attachments

    Outcomes