Typically, to find the most expensive statement as the candidate for tuning, sysquerymetrics provides CPU execution time, elapsed time, logical IO, and physical I/O as options for measure. For example, a typical measure is based on logical I/O. Use the following query to find the statements that incur too many IOs as the candidates for tuning:
select lio_avg, qtext from sysquerymetrics order by lio_avg
lio_avg qtext ----------- ------------------------------------------------------ 2 select c1, c2 from t_metrics1 where c1 = 333 4 select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2) 6 select count(t_metrics1.c1) from t_metrics1, t_metrics2, t_metrics3 where (t_metrics1.c2 = t_metrics2.c2 and t_metrics2.c2 = t_metrics3.c2 and t_metrics3.c3 = 0) 164 select min(c1) from t_metrics1 where c2 in (select t_metrics2.c2 from t_metrics2, t_metrics3 where (t_metrics2.c2 = t_metrics3.c2 and t_metrics3.c3 = 1)) (4 rows affected)
The best candidate for tuning can be seen in the last statement of the above results, which has the biggest value (164) for average logical IO.
