SAP ASE versions 16.0 and later include the ability to configure, record, and list
threshold events.
To configure the amount of time a query spends using CPU, use the
sp_add_resource_limit cpu_time limit type.
To record events to the monThresholdEvent table, set the
sp_add_resource_limit
action parameter to 5 (see the Reference Manual:
Procedures).
The syntax
is:
sp_add_resource_limit name, appname, rangename, limittype, limitvalue [, enforced [, action [, scope ]]]
In this example, SAP ASE issues a warning when the CPU time of the query batch takes
longer than 120 seconds to execute, and records the events to
monThresholdEvent:
sp_add_resource_limit NULL, payroll, tu_wed_7_10,cpu_time, 120, 2, 5, 2
monThresholdEvent lets you record all violations for configured resource
limitations, and allows you to determine the thresholds for:
- tempdb usage per transaction
- Total runtime of a query or transaction
- Number of rows fetched or written by a query
- Number of rows read by a query
- Estimated plan cost of a query
- Total logical/physical I/Os of a query
- Total CPU time of a query