Default language.

Enabling and viewing statistics about long-running SQL and API calls

The  enables you to gather performance statistics on the server. You can also gather statistics about the longest running SQL and API calls. 

The  adds exception logging, where API and SQL performance exceptions are recorded in a new log file, arexception.log.

Finding out which calls are causing delays can help you troubleshoot performance issues.

For details about the performance exception logging feature, see Viewing-exception-logs-for-SQL-and-API-calls.

Best practice
We recommend that you use the AR System Management Console to set the server statistics options. For more information, see Setting-global-level-and-local-level-configurations-for-a-server-group.

To gather statistics about the longest running SQLs and APIs

Important

This form shows the local level value of the configuration. If a local value does not exist, the form displays the global level configuration. If you modify the value on this form, the local level configuration value is modified.

For example, if a configuration shows global level value and you modify the value by using this form, the local level value gets created for the configuration.

  1. On the AR System Administration: Server Information form, click the Server Statistics tab.

    Server Statistics form from SDD - cropped.png

  1. In the API/SQL Performance Tracking section, complete the following fields:

    Field

    Description

    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:

    • Save Completed API
    • Save Completed SQL
    • Save Pending API
    • Save Pending SQL

    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).

    Enable Exception Logging

    When selected, enables exception logging, where all API and SQL calls that exceed the value specified in the Minimum Elapsed Time (mSec) field are immediately written to the arexception.log file.

    The default is 5000 ms (5 seconds)

    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); no automatic save and purge are performed. If you change the interval, the new interval is used if it is less than the time remaining for 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 5,000 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

  1. On the AR System Administration: Server Information form, click the Server Statistics tab.

  2. 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. 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 are cleared from memory.

  3. To view the results:
    1. 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
    2. 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:

Field

Contents

API name

Text name of the API associated with the event (or INTERNAL)

User

User name associated with the event (or SYSTEM)

Thread ID

ID of the thread associated with the event

Result Code

Error code that resulted from the event. If there is no error, 0 is the error code.

Start Time

Time that the event started

Server Name

Name of the server where the event occurred.  All servers in a server group share the same form.

Parameters

(API only) Additional details about the API call (for example, the form name on entry-related calls)

SQL Command

(SQL only) First 1,000 characters of the SQL command issued to the database

Extended Data

If the API parameters or SQL command exceed 1,000 characters, the entire string is contained in this 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.

Client Type

Text name of the type of client used to send the API (or Unidentified Client)

RPC ID

Unique ID of the event RPC, or 0 (if generated internally)

Create Mode

Origin of the event recording:

  • Auto—Longest API or SQL event that was recorded at an Auto Save and Purge Interval (the option selected on the Server Statistics tab on the AR System Administration: Server Information form).
  • Demand—Longest API or SQL event that was recorded from an on-demand request
  • In Process—Incomplete API or SQL event that was recorded from an on-demand request

Queue Delay (mSec)

(API only) Time (in milliseconds) in the thread queue before processing started

Data Retrieval (mSec)

(SQL only) Reserved for future use

Create Date

Date and time the entry was added to the form (not the event time)

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*