sa_performance_diagnostics system procedure

sa_performance_diagnostics( )

Column name	Data type	Description
Number	INT	Returns the ID number of the connection.
Name	VARCHAR(255)	Returns the name of the current connection. You can specify a connection name using the ConnectionName (CON) connection parameter. See 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 See Temporary connections.
Userid	VARCHAR(255)	Returns the user ID for the connection.
DBNumber	INT	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. See -zt dbeng12/dbsrv12 server option.
ReqTimeUnscheduled	DOUBLE	Returns the amount of unscheduled time, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqTimeActive	DOUBLE	Returns the amount of time spent processing requests, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqTimeBlockIO	DOUBLE	Returns the amount of time spent waiting for I/O to complete, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqTimeBlockLock	DOUBLE	Returns the amount of time spent waiting for a lock, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqTimeBlockContention	DOUBLE	Returns the amount of time spent waiting for atomic access, or NULL if the RequestTiming server property is set to Off. See -zt dbeng12/dbsrv12 server option.
ReqCountUnscheduled	INT	Returns the number of times the connection waited for scheduling, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqCountActive	INT	Returns the number of requests processed, or NULL if the RequestTiming server property is set to Off. See -zt dbeng12/dbsrv12 server option.
ReqCountBlockIO	INT	Returns the number of times the connection waited for I/O to complete, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqCountBlockLock	INT	Returns the number of times the connection waited for a lock, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
ReqCountBlockContention	INT	Returns the number of times the connection waited for atomic access, or NULL if the -zt option was not specified. See -zt dbeng12/dbsrv12 server option.
LastIdle	INT	Returns the number of ticks between requests.
BlockedOn	INT	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	INT	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	INT	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. See -zp dbeng12/dbsrv12 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. For more information about the values returned for other types of connections, see AppInfo (APP) connection parameter.
LockCount	INT	Returns the number of locks held by the connection.
SnapshotCount	INT	Returns 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 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' );

DBA authority

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;

sa_performance_diagnostics system procedure

Syntax

Result set

Remarks

Permissions

Side effects

See also

Examples