Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_migrate_drop_proxy_tables system procedure Next Page

sa_performance_diagnostics system procedure

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


sa_performance_diagnostics( )

Result set
Column nameData typeDescription
NumberINTThe ID number of the connection.
NameVARCHAR(255)The name of the connection.
UseridVARCHAR(255)The user ID for the connection.
DBNumberINTThe ID number of the database.
LoginTimeTIMESTAMPThe date and time the connection was established.
TransactionStartTimeTIMESTAMPThe 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.
LastReqTimeTIMESTAMPThe time at which the last request for the specified connection started.
ReqTypeVARCHAR(255)The type of the last request.

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 a worker thread.

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

ReqTimeUnscheduledDOUBLEThe time spent unscheduled.
ReqTimeActiveDOUBLEThe time spent waiting to process requests.
ReqTimeBlockIODOUBLEThe time spent waiting for I/O to complete.
ReqTimeBlockLockDOUBLEThe time spent waiting for a lock.
ReqTimeBlockContentionDOUBLEThe time spent waiting for atomic access.
ReqCountUnscheduledINTThe number of times waited for scheduling.
ReqCountActiveINTThe number of requests processed.
ReqCountBlockIOINTThe number of times waited for I/O to complete.
ReqCountBlockLockINTThe number of times waited for a lock.
ReqCountBlockContentionINTThe number of times waited for atomic access.
LastIdleINTThe number of ticks between requests.
BlockedOnINTIf the current connection isn't blocked, this is zero. If it is blocked, the connection number on which the connection is blocked due to a locking conflict.
UncommitOpINTThe number of uncommitted operations.
CurrentProcedureVARCHAR(255)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
EventNameVARCHAR(255)The name of the associated event if the connection is running an event handler. Otherwise, the result is NULL.
CurrentLineNumberINTThe 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.
LastStatementLONG VARCHARThe most recently prepared SQL statement for the current connection.
LastPlanTextLONG VARCHARThe long text plan of the last query executed on the connection.
AppInfoLONG VARCHARInformation about the client that made the connection. For HTTP connections, this includes information about the browser. For connections using older versions of jConnect or Open Client, the information may be incomplete. The API value can be DBLIB, ODBC, OLEDB, or ADO.NET.
LockCountINTThe number of locks held by the connection.
SnapshotCountINTThe number of snapshots associated with the connection.

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 prior to 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' );

DBA authority required

Side effects


See also

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,
      ReqTimeActive / T AS PercentActive
FROM  dbo.sa_performance_diagnostics()
WHERE 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,
FROM  dbo.sa_performance_diagnostics()
WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0