Adaptive Server using sp_metrics to
manage sysquerymetrics data. The data most
currently collected is stored in sysquerymetrics with
a global ID (GID) of 1. If you use the sp_metrics...backup parameter
to save previous data, you must use a GID greater than 1 that is
not already in use. To find the next available GID, select the max(gid) from sysquerymetrics and
add one to the value.
To enable metrics capture, enter:
set metrics_capture on
To flush the metrics, enter:
sp_metrics 'flush'
To select a GID (if this returns a NULL value or 1, use a value of 2 or higher), enter:
select max(gid)+1 from sysquerymetrics
To back up the data, enter:
sp_metrics 'backup', '3'
To turn off metrics capture, enter:
set metrics_capture off
To analyze the data, enter:
select * from sysquerymetrics where gid = 3
To drop the data, enter:
sp_metrics 'drop', '2', '5'
See the Reference Manual: Commands for more information about sp_metrics.
You can filter the data in sysquerymetrics by deleting data whose value is less than a predetermined value. You must enable allow updates before you delete data.
When you drop metrics, the begin range and end range must exist. For example, in this query, which attempts to drop the metrics from a group that starts with 3:
sp_metrics 'drop', '2', '5'
The drop fails because group 2 does not exist.
sysquerymetrics can consume a large amount of space in the system segment. However, to reduce the amount of space used:
Run sp_metrics capture.
At the end of the capture period, issue:
Select max(gid) from sysquerymetrics sp_metrics 'backup', 'gid'
Start the next capture period.
Filter the previous results. For example, you can delete the rows that have an looming less than 10,000.
Repeat this sequence 10 to 15 times.
