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 set
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:

  • 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.
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 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' );
Permissions

DBA authority required

Side effects

None

See also
Examples

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;