sa_performance_diagnostics system procedure

sa_performance_diagnostics( )

Column name	Data type	Description
Number	INT	The connection ID number.
Name	VARCHAR(255)	The name of the connection.
Userid	VARCHAR(255)	The user ID for the connection.
DBNumber	INT	The database ID number.
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: 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.
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 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