You can use sysquerymetrics to support regression testing after changing the optimization goals, parallel resources, partitioning style for a table, or employing other Adaptive Server 15.0 features such as function-based indexes. The steps are:
Enable sp_metrics capture for the initial run. These metrics are be used for reference.
Run the application’s module.
Back up these reference metrics by setting their GID to 2.
Make any configuration changes to improve the metrics
Repeat steps 1 – 3, backing up to the next higher GID each time.
Identify queries that were impacted by joining sysquerymetrics on the hashkey. For example:
select r.hashkey, r.exec_avg, m.exec_avg, r.elap_avg, m.elap_avg, r.lio_avg, m.lio_avg, r.pio_avg, m.pio_avg, r.qtext from sysquerymetrics r, sysquerymetrics m where r.gid=2 and m.gid=<#> -- substitute # of current gid and r.hashkey=m.hashkey and ((r.exec_avg + (r.exec_avg * 0.1) < m.exec_avg) or (r.elap_avg + (r.elap_avg * 0.1) < m.elap_avg) or (r.lio_avg + (r.lio_avg * 0.1) < m.lio_avg) or (r.pio_avg + (r.pio _avg * 0.1) < m.pio_avg)
You can change the query to reflect the values you consider a regression, including adding a tolerance factor of 5. The example above includes a 10% tolerance factor to the reference times to avoid impacting the checkpoint process.
The example above has only one user, so it does not add r.uid = m.uid. You can
change can be changed in the real application if multiple users
executing the same module are to be tested).
