sa_performance_diagnostics System Procedure

Returns a summary of request timing information for all connections when the database server has request timing logging enabled.

Syntax

sa_performance_diagnostics( )

Result

Column Name Data Type Description
Number INTEGER Returns the connection ID (a number) for the current connection.
Name VARCHAR(255)

Returns the name of the current connection.

You can specify a connection name using the ConnectionName (CON) connection parameter.

The following names are used for temporary connections created by the database server:
  • INT:ApplyRecovery
  • INT:BackupDB
  • INT:Checkpoint
  • INT:Cleaner
  • INT:CloseDB
  • INT:CreateDB
  • INT:CreateMirror
  • INT:DelayedCommit
  • INT:DiagRcvr
  • INT:DropDB
  • INT:EncryptDB
  • INT:Exchange
  • INT:FlushMirrorLog
  • INT:FlushStats
  • INT:HTTPReq
  • INT:PromoteMirror
  • INT:PurgeSnapshot
  • INT:ReconnectMirror
  • INT:RecoverMirror
  • INT:RedoCheckpoint
  • INT:RefreshIndex
  • INT:ReloadTrigger
  • INT:RenameMirror
  • INT:RestoreDB
  • INT:StartDB
  • INT:VSS
Userid VARCHAR(255) Returns the user ID for the connection.
DBNumber INTEGER Returns the ID number of the database.
LoginTime TIMESTAMP Returns the date and time the connection was established.
TransactionStartTime TIMESTAMP Returns a string containing the time the database was first modified after a COMMIT or ROLLBACK, or an empty string if no modifications have been made to the database since the last COMMIT or ROLLBACK.
LastReqTime TIMESTAMP Returns the time at which the last request for the specified connection started. This property can return an empty string for internal connections, such as events.
ReqType VARCHAR(255) Returns the type of the last request. If a connection has been cached by connection pooling, its ReqType value is CONNECT_POOL_CACHE.
ReqStatus VARCHAR(255)

Returns the status of the request. It can be one of the following values:

  • Idle – The connection is not currently processing a request.
  • Unscheduled – The connection has work to do and is waiting for an available database server worker.
  • BlockedIO – The connection is blocked waiting for an I/O.
  • BlockedContention – The connection is blocked waiting for access to shared database server data structures.
  • BlockedLock – The connection is blocked waiting for a locked object.
  • Executing – The connection is executing a request.

The values marked with an asterisk (*) are only returned when logging of request timing information has been turned on for the database server using the -zt server option. If request timing information is not being logged (the default), the values are reported as Executing.

ReqTimeUnscheduled DOUBLE Returns the amount of unscheduled time, or NULL if the -zt option was not specified.
ReqTimeActive DOUBLE Returns the amount of time, in seconds, spent processing requests, or NULL if the -zt option was not specified.
ReqTimeBlockIO DOUBLE Returns the amount of time, in seconds, spent waiting for I/O to complete, or NULL if the -zt option was not specified.
ReqTimeBlockLock DOUBLE Returns the amount of time, in seconds, spent waiting for a lock, or NULL if the -zt option was not specified.
ReqTimeBlockContention DOUBLE Returns the amount of time, in seconds, spent waiting for atomic access, or NULL if the RequestTiming server property is set to Off.
ReqCountUnscheduled INTEGER Returns the number of times the connection waited for scheduling, or NULL if the -zt option was not specified.
ReqCountActive INTEGER Returns the number of requests processed, or NULL if the RequestTiming server property is set to Off.
ReqCountBlockIO INTEGER Returns the number of times the connection waited for I/O to complete, or NULL if the -zt option was not specified.
ReqCountBlockLock INTEGER Returns the number of times the connection waited for a lock, or NULL if the -zt option was not specified.
ReqCountBlockContention INTEGER Returns the number of times the connection waited for atomic access, or NULL if the -zt option was not specified.
LastIdle INTEGER Returns the number of ticks between requests.
BlockedOn INTEGER Returns zero if the current connection isn't blocked, or if it is blocked, the connection number on which the connection is blocked because of a locking conflict.
UncommitOp INTEGER Returns the number of uncommitted operations.
CurrentProcedure VARCHAR(255) Returns the name of the procedure that a connection is currently executing. If the connection is executing nested procedure calls, the name is the name of the current procedure. If there is no procedure executing, an empty string is returned.
EventName VARCHAR(255) Returns the name of the associated event if the connection is running an event handler. Otherwise, an empty string is returned.
CurrentLineNumber INTEGER Returns the current line number of the procedure or compound statement a connection is executing. The procedure can be identified using the CurrentProcedure property. If the line is part of a compound statement from the client, an empty string is returned.
LastStatement LONG VARCHAR Returns the most recently prepared SQL statement for the current connection.

The LastStatement value is set when a statement is prepared, and is cleared when a statement is dropped. Only one statement string is remembered for each connection.

If sa_conn_activity reports a non-empty value for a connection, it is most likely the statement that the connection is currently executing. If the statement had completed, it would likely have been dropped and the property value would have been cleared. If an application prepares multiple statements and retains their statement handles, then the LastStatement value does not reflect what a connection is currently doing.

When client statement caching is enabled, and a cached statement is reused, this property returns an empty string.
LastPlanText LONG VARCHAR Returns the long text plan of the last query executed on the connection. You control the remembering of the last plan by setting the RememberLastPlan option of the sa_server_option system procedure, or using the -zp server option.
AppInfo LONG VARCHAR Returns information about the client that made the connection. For HTTP connections, this includes information about the browser. For connections using older versions of jConnect or Sybase Open Client, the information may be incomplete.

The API value can be DBLIB, ODBC, OLEDB, ADO.NET, iAnywhereJDBC, PHP, PerlDBD, or DBEXPRESS.
LockCount INTEGER Returns the number of locks held by the connection.
SnapshotCount INTEGER Returns the number of snapshots associated with the connection.

Remarks

The sa_performance_diagnostics system procedure returns a result set consisting of a set of request timing properties and statistics if the server has been told to collect the information. Recording of request timing information must be turned on the database server before calling sa_performance_diagnostics. To do this, specify the -zt option when starting the database server or execute the following: 
CALL sa_server_option( 'RequestTiming','ON' );

Privileges

Requires the MONITOR system privilege.

Example

You can execute the following query to identify connections that have spent a long time waiting for database server requests to complete. 
SELECT Number, Name, 
      CAST( DATEDIFF( second, LoginTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS T, 
      IF T <> 0 THEN (ReqTimeActive / T) ELSE NULL ENDIF AS PercentActive 
FROM sa_performance_diagnostics() 
WHERE T > 0 AND PercentActive > 10.0 
ORDER BY PercentActive DESC;
Find all requests that are currently executing, and have been executing for more than 60 seconds: 
SELECT Number, Name, 
       CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime 
FROM sa_performance_diagnostics() 
WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0 
ORDER BY ReqTime DESC;
Parent topic: Alphabetical List of Catalog Stored Procedures