Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

sa_performance_diagnostics system procedure

Returns a summary of request timing information for all connections when the database server has request timing logging enabled.

sa_performance_diagnostics( )

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

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

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