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

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

• -zt server option
• sa_performance_statistics system procedure
• sa_server_option system procedure

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;