Transient monitoring data

Because monitoring tables often contain transient data, take care when joining or using aggregates in your queries: results from these operations may be different if a query plan requires a table to be queried multiple times. For example:

select s.SPID, s.CpuTime, s.LineNumber, t.SQLText
from master..monProcessStatement s, monProcessSQLText t
where s.SPID=t.SPID
and s.CpuTime = (select max(CpuTime) from master..monProcessStatement)

This example queries monProcessStatement twice; first to find the maximum CpuTime, and then to match the maximum. When Adaptive Server performs the second query, there are three potential outcomes returned from monProcessStatement:

• The statement performs more work, consuming more CPU, and having a CpuTime value greater than the previous maximum, so there is no match in the where clause, and the query returns no results.

• The statement finishes executing before the second query executes, yielding no results unless another statement used exactly the same amount of CPU as the previously obtained maximum.

• The statement does not use any additional CPU, and its value of CpuTime still matches the maximum. Only this scenario produces the expected results.

Sybase recommends that you save data from the monitoring tables to a temporary table or repository before you analyze it. Doing so freezes the data and eliminates the potentially undesirable results due to transient data or the stateful nature of the historical monitoring tables.