Before Service Pack 5, AR System server enabled you to gather performance statistics on the server. (See “Gathering baseline data” on page 14 of the BMC Remedy Action Request System 7.6.04 Optimizing and Troubleshooting Guide.) With Service Pack 5, you can also gather statistics about the longest running SQL and API calls. Finding out which calls are causing delays can help you troubleshoot performance issues.
To implement this function, the following changes were made:
- The Server Statistics configuration options were moved from the Advanced tab of the AR System Administration: Server Information form to a new Server Statistics tab. (For information about the previous Advanced tab, see page 123 of the BMC Remedy Action Request System 7.6.04 Configuration Guide.)
- The following new forms save information about the longest elapsed time for calls:
- Server Statistics: Longest APIs
- Server Statistics: Longest SQLs
- The following options were added to the ar.cfg (ar.conf) file:
- API-SQL-Stats-Control: A bit mask value indicating options selected
- API-SQL-Stats-Max-Saved-Long-Operations: Maximum number of longest API and SQL operations saved in memory. The default is 20 of each type. The highest value allowed is 100.
- API-SQL-Stats-Flush-To-DB-Interval: Time, in minutes, between flushing the longest operations from memory to the forms. A value of 0 (the default) means that there is no automatic flushing.
- API-SQL-Stats-Minimum-Elapsed-Time: Minimum elapsed time an operation must have to qualify for recording. The default is 5000 milliseconds (or 5 seconds).
- API-SQL-Stats-Control: A bit mask value indicating options selected
- The following options were added to the
ARGetServerInfoC API call:
- AR_SERVER_INFO_STATS_APISQL_CONTROL: An integer bitmask indicating the control options that were chosen. 1 specifies "Display to console ."
- AR_SERVER_INFO_STATS_APISQL_MAX_SAVED: An integer that indicates the maximum number of longest API and SQL operations that can be saved in memory
- AR_SERVER_INFO_STATS_APISQL_INTERVAL: An integer that indicates the time in minutes between flushing the longest operations saved in memory to the database. A value of 0 indicates that no automatic flushing occurs.
- AR_SERVER_INFO_STATS_APISQL_MIN_TIME: An integer that indicates the minimum elapsed time (in milliseconds) for an operation to qualify for saving
- AR_SERVER_INFO_STATS_GET_INCOMPLETE_API: A character value containing a formatted list of the current incomplete API calls
- AR_SERVER_INFO_STATS_GET_LONGEST_API: A character value containing a formatted list of the current longest API calls saved in memory
- AR_SERVER_INFO_STATS_GET_INCOMPLETE_SQL: A character value containing a formatted list of the current incomplete SQL calls
- AR_SERVER_INFO_STATS_GET_LONGEST_SQL: A character value containing a formatted list of the current longest SQL calls saved in memory
To gather statistics on the longest running SQLs and APIs
- On the AR System Administration: Server Information form, click the Server Statistics tab.
In the API/SQL Performance Tracking section, complete the following fields:
Enable Console Display
When selected, displays API and SQL events in the console when an automatic save is triggered (by the time specified in the Auto Save and Purge Interval (min) field) and when you click one of the Save buttons:
This is a debugging aid that is available if you are running the server from a command window. (This option is not available when you are running the server on Windows as a service because there is no console.)
The default is off (not selected).
Number of Saved Operations
Defines the maximum number of completed longest operations that can be saved in memory. The number applies to both API and SQL lists of operations.
The default is 20. If you decrease this number, you must restart the server for the change to take effect. (A restart is not required if you increase the number.) Although 20 is the recommended value to control the overhead of statistics management, you can increase this number up to 100.
Auto Save and Purge Interval (min)
Defines the interval (in minutes) for when the longest operations saved in memory are flushed to the corresponding forms in the database and then purged
The default is 0 (no interval), and no automatic save and purge is performed. If you change the interval, the new interval is used if it is less than the time remaining on the old interval. Otherwise, the new interval is used after the current interval expires.
Minimum Elapsed Time (mSec)
Sets the minimum duration of the longest API and SQL events saved in the memory buffer in milliseconds. Any event shorter than this value is discarded.
If an event is at the minimum or a greater number of milliseconds, it qualifies to be saved. Consequently, if the event is not one of the longest operations in the list, it is not saved. (The maximum is defined by the Number of Saved Operations field.)
The default is 5000 milliseconds. If you enter 0, all events (up to the limit set in the Number of Saved Operations field) are saved.
To manually flush the statistics and view the results
On the AR System Administration: Server Information form, click the Server Statistics tab.
Click the appropriate button for the type of statistics you want to gather:
Save Completed API
Save Completed SQL
Save Pending API
Save Pending SQL
The Save Pending API and Save Pending SQL buttons record the API calls and SQL commands that are currently in process. The in process events are not subject to the Minimum Elapsed Time setting.
The Save Completed API and Save Completed SQL buttons record the longest API and SQL operations that are currently saved in memory. After flushing to the corresponding forms in the database, the currently saved operations in memory are cleared.
To view the statistics:
Open the appropriate form by entering one of the following URLs:
http://<midTierServer>/arsys/forms/<ARSystemServer>/Server Statistics: Longest APIs
http://<midTierServer>/arsys/forms/<ARSystemServer>/ Server Statistics: Longest SQLs
Enter search criteria, and click Search.
Information gathered in the statistics forms
The Server Statistics: Longest SQLs form or the Server Statistics: Longest APIs form contains the following information:
Text name of the API associated with the event (or INTERNAL)
User name associated with the event (or SYSTEM)
ID of the thread associated with the event
Error code that resulted from the event. If there is no error, 0 is the error code.
Time that the event started
Name of the server where the event occurred. All servers in a server group share the same form.
(API only) Additional details about the API call (for example, the form name on entry-related calls)
(SQL only) First 1000 characters of the SQL command issued to the database.
If the API parameters or SQL command exceed 1000 characters, the entire string is contained in the field.
Elapsed Time (mSec)
Time (in milliseconds) required to complete the event. For SQL events, this time represents the statement execution only. It does not include any subsequent record retrieval time.
Text name of the type of client used to send the API (or Unidentified Client)
Unique ID of the event RPC, or 0 (if generated internally)
Origin of the event recording:
Queue Delay (mSec)
(API only) Time (in milliseconds) in the thread queue before processing started
Data Retrieval (mSec)
(SQL only) Reserved for future use
Date and time the entry was added to the form (not the event time)