Métricas de Procesamiento de Consultas en Adaptive Server Enterprise

Introducción

Adaptive Server Enterprise (ASE) 15.0 introdujo las métricas de procesamiento de consultas (QP Metrics). Las QP Metrics identifican y comparan valores métricos empíricos de ejecución de consultas.

Este documento brinda una descripción de alto nivel de las métricas de procesamiento de consultas o QP Metrics, incluyendo temas como ejecución, acceso y uso de las métricas.

¿Qué son las QP Metrics?

Las QP Metrics identifican y comparan valores métricos empíricos de ejecución de consultas. Cuando una consulta es ejecutada, es asociada con un conjunto de métricas definidas que son la base para la comparación de métricas de procesamiento de consultas.

Las métricas capturadas incluyen:

• Tiempo de ejecución de CPU (CPU execution time) - el tiempo, en milisegundos, que se toma la ejecución de la consulta.
• Tiempo transcurrido (Elapsed time) - el tiempo de CPU, en milisegundos, y el tiempo que se toman el análisis, compilación y optimización de la consulta. El tiempo transcurrido es registrado después de que se compila el plan de consulta.
• L/E Lógica (Logical IO) - el número de lecturas lógicas.
• L/E Física (Logical IO) - el número de lecturas físicas.
• Conteo (Count) - el número de veces que una consulta ha sido ejecutada.
• Conteo de Terminaciones (Abort count) - el número de veces que una consulta es abortada por el gobernador de recursos debido a la ejecución de un límite de recursos.

Cada métrica tiene tres valores: mínimo, máximo y promedio. No se incluye Conteo y Conteo de Terminaciones.

Ejecución de las QP Metrics

Las métricas para sentencias ad hoc son capturadas en un catálogo del sistema, mientras que las métricas para sentencias dentro de un procedimiento almacenado son guardadas en el caché de procedimientos. Cuando el procedimiento almacenado o sentencia en el caché de sentencias es vaciado, las métricas respectivas capturadas son escritas al catálogo del sistema.

Usted puede ejecutar y usar las QP Metrics a nivel de servidor o a nivel de sesión. A nivel de servidor, use sp_configure con la opción enable metrics capture. Por ejemplo, el siguiente comando habilita la captura de métricas a nivel de servidor:

sp_configure 'enable metrics capture' ,1
go

A nivel de sesión, use set metrics_capture { on | off }. Por ejemplo, el siguiente comando habilita la captura de métricas para la sesión actual:

set metrics_capture on
go

Acceso a las QP Metrics

Las métricas de procesamiento de consultas son capturadas en el grupo de ejecución predeterminado, que es el grupo 1, en cada base de datos. Use sp_metrics 'backup' para mover métricas almacenadas, del grupo de ejecución predeterminado a un grupo de respaldo. El acceso a las métricas de ejecución se hace a través de una sentencia select sobre la vista sysquerymetrics.

Usted también puede usar una sentencia DML (Data Manipulation Language) para ordenar la información de métricas y para identificar las consultas específicas para su evaluación.

Uso de las QP Metrics

Use la información producida por las QP Metrics para identificar:

• Regresiones en el rendimiento de consultas
• La consulta más "costosa" de un lote de consultas en ejecución
• Las consultas más frecuentemente ejecutadas

Cuando usted tiene información sobre las consultas que pueden estar causando problemas, usted puede afinar las consultas para aumentar la eficiencia.

Por ejemplo, identificar y afinar una consulta "costosa" puede se más efectivo que afinar las más "baratas" en el mismo lote.

Usted también puede identificar las consultas que se corren más frecuentemente, y afinarlas para aumentar la eficiencia.

Afinar basándose en métricas de procesamiento puede involucrar operaciones adicionales de Lectura/Escritura (L/E), así que puede haber una penalidad desde el punto de vista de rendimiento. Sin embargo, los beneficios mencionados arriba deben ser considerados. También, cuando el uso de métricas de procesamiento de consultas es contrastado con la información disponible en las tablas MDA, cabe mencionar que con las métricas es posible obtener datos agregados históricos sobre una consulta y almacenarlos en un catálogo del sistema. La información en las tablas MDA es transitoria.

¿Qué usar? ¿QP metrics o tablas de monitoreo?

Tanto las QP Metrics como las tablas de monitoreo (tablas MDA) tienen su lugar para almacenar información estadística. Sin embargo, usted puede usar QP Metrics en lugar de tablas de monitoreo para obtener información histórica agregada a partir de un catálogo persistente, en lugar de tener información transitoria de las tablas de monitoreo.

Para mayor información sobre las tablas de monitoreo o tablas MDA, visite ésta página.

La vista sysquerymetrics

La vista sysquerymetrics, presente en todas las bases de datos, presenta métricas agregadas históricas de procesamiento de consultas para consultas individuales y de manera persistente. Adicionalmente a las tablas de monitoreo, es posible usar la información de métricas de rendimiento de éste catálogo.

La siguiente tabla contiene una lista de las columnas de la vista sysquerymetrics, así como su descripción.

Tabla 1 - Campos en la vista sysquerymetrics

Los valores promedio en ésta vista son calculados usando ésta fórmula:

new_avg = (old_avg * old_count + new_value )/ (old_count + 1) = old_avg + round((new_value - old_avg)/(old_count + 1))

Este es un ejemplo de información contenida en la vista sysquerymetrics:

select * from sysquerymetrics
go

uid         gid         hashkey     id          sequence    exec_min 
exec_max    exec_avg    elap_min    elap_max    elap_avg    lio_min 
lio_max     lio_avg     pio_min     pio_max     pio_avg     cnt         abort_cnt 
qtext
----------- ----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- ----------- ----------- ----------- ----------- 
-------------------------------------------------------------------------
          1           1   106588469   480001710           0           0 
          0           0          16          33          25           4
          4           4           0           4           2           2          0 
select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2)

El ejemplo de arriba muestra un registro para una sentencia SQL. El texto de la sentencia es select distinct c1 from t_metrics1 where c2 in (select c2 from t_metrics2). Esta sentencia ha sido ejecutada dos veces hasta ahora (cnt = 2). El tiempo mínimo transcurrido es 16 milisegundos, el tiempo máximo transcurrido es 33 milisegundos, y el promedio de tiempo transcurrido es 25 milisegundos. Todos los tiempos de ejecución son 0, y es posible que esto se deba a que el tiempo de ejecución de CPU haya sido menor a 1 milisegundo. El máximo de operaciones físicas de L/E es 4, lo cual es consistente con el máximo número de operaciones lógicas. Sin embargo, el mínimo de operaciones físicas es 0 porque hay datos ya en caché para la segunda ejecución. El número de operaciones lógicas es 4, ya que éste debe ser estático así los datos estén o no en memoria.

Ejemplos

Usted puede usar las QP Metrics para identificar consultas específicas que puedan ser afinadas y para identificar posibles regresiones en el rendimiento.

Identificar la sentencia más costosa

Típicamente, para encontrar la sentencia más costosa candidata para afinamiento, sysquerymetrics provee tiempo de ejecución de CPU, tiempo transcurrido, L/E lógica, y L/E física como opciones de medida. Por ejemplo, una medida típica está basada en L/E Lógica. Use una consulta como la siguiente para encontrar la sentencia que incurra en demasiadas operaciones de L/E como la candidata para el afinamiento:

select lio_avg, qtext
from sysquerymetrics
order by lio_avg
go

lio_avg     qtext
----------- ------------------------------------------------------
          2 select c1, c2 from t_metrics1 where c1 = 333
          4 select distinct c1 from t_metrics1 where c2 in (select ...
          6 select count(t_metrics1.c1) from t_metrics1, t_metrics2, ...
        164 select min(c1) from t_metrics1 where c2 in (select ...
(4 rows affected)

La mejor candidata para afinamiento puede ser observada en la última sentencia del resultado de arriba, la cual tiene el mayor valor para lio_avg.

Identificar la sentencia más frecuentemente usada

Si una sentencia es usada frecuentemente, su afinamiento puede mejorar el rendimiento. Identifique las consulta más frecuentemente usada usando una sentencia select con order by:

select elap_avg, cnt, qtext
from sysquerymetrics
order by cnt
go

elap_avg    cnt         qtext
----------- ----------- --------------------------------------------------
          0           1 select c1, c2 from t_metrics1 where c1 = 333
         16           2 select distinct c1 from t_metrics1 where c2 in ...
         24           3 select min(c1) from t_metrics1 where c2 in (select...
         78           4 select count(t_metrics1.c1) from t_metrics1, t_metrics2,...
(4 rows affected)

Identificar una posible regresión de rendimiento

En algunos casos, cuando un servidor es actualizado a una nueva versión, las QP Metrics pueden ser útiles para comparara el rendimiento. Para identificar consultas que puedan tener alguna degradación, use el siguiente procedimiento:

1. Copie las QP Metrics del servidor original a un grupo de respaldo:

sp_metrics 'backup', backup_group_ID
go

2. Cambie al nuevo servidor y habilite las QP Metrics:

sp_configure 'enable metrics capture',1
go

3. Compare las QP Metrics entre los reportes del servidor original y nuevo para identificar cualquier consulta que pueda tener problemas de regresión.

Borrando las QP Metrics

Use sp_metrics 'flush' para vaciar todas las métricas agregadas de memoria al catálogo del sistema. Las métricas agregadas para todas las sentencias en memoria con reinicializadas (sus valores son puestos en cero).

Para remover las QP Metrics del catálogo del sistema, use sp_metrics 'drop', gid, id.

El procedimiento sp_metrics

El procedimiento sp_metrics permite realizar copias de respaldo, borrar y vaciar las QP Metrics, que son siempre capturadas en el grupo de ejecución predeterminado, que es el grupo 1 de cada base de datos.

Sintaxis

sp_metrics ['backup' backup_group_ID | 'drop', 'gid' [, 'id'] | 'flush' | 'help', 'command']

Conclusiones

Las QP Metrics de ASE identifican y comparan valores métricos empíricos de ejecución de consultas que sirven para identificar regresiones en el rendimiento de consultas,  la consulta más "costosa" de un lote de consultas en ejecución, o las consultas más frecuentemente ejecutadas.

Más información de las QP Metrics en el manual Query Processor disponible en la documentación de ASE.