Returns a summary of request timing information for all connections when the database server has request timing logging enabled.
sa_performance_diagnostics( )
Column name | Data type | Description |
---|---|---|
Number | INT | The ID number of the connection. |
Name | VARCHAR(255) | The name of the connection. |
Userid | VARCHAR(255) | The user ID for the connection. |
DBNumber | INT | The ID number of the database. |
LoginTime | TIMESTAMP | The date and time the connection was established. |
TransactionStartTime | TIMESTAMP | 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 | The time at which the last request for the specified connection started. |
ReqType | VARCHAR(255) | The type of the last request. |
ReqStatus | VARCHAR(255) |
The status of the request. It can be one of the following values:
|
ReqTimeUnscheduled | DOUBLE | The time spent unscheduled. |
ReqTimeActive | DOUBLE | The time spent waiting to process requests. |
ReqTimeBlockIO | DOUBLE | The time spent waiting for I/O to complete. |
ReqTimeBlockLock | DOUBLE | The time spent waiting for a lock. |
ReqTimeBlockContention | DOUBLE | The time spent waiting for atomic access. |
ReqCountUnscheduled | INT | The number of times waited for scheduling. |
ReqCountActive | INT | The number of requests processed. |
ReqCountBlockIO | INT | The number of times waited for I/O to complete. |
ReqCountBlockLock | INT | The number of times waited for a lock. |
ReqCountBlockContention | INT | The number of times waited for atomic access. |
LastIdle | INT | The number of ticks between requests. |
BlockedOn | INT | If 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. |
UncommitOp | INT | The number of uncommitted operations. |
CurrentProcedure | VARCHAR(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 |
EventName | VARCHAR(255) | The name of the associated event if the connection is running an event handler. Otherwise, the result is NULL. |
CurrentLineNumber | INT | 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 | The most recently prepared SQL statement for the current connection. |
LastPlanText | LONG VARCHAR | The long text plan of the last query executed on the connection. |
AppInfo | LONG VARCHAR | 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 Open Client, the information may be incomplete. The API value can be DBLIB, ODBC, OLEDB, or ADO.NET. |
LockCount | INT | The number of locks held by the connection. |
SnapshotCount | INT | The 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
None
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, 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, CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime FROM dbo.sa_performance_diagnostics() WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0 ORDER BY ReqTime DESC; |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |