Una de las novedades de SQL Server 2014 es la posibilidad de, en cierta manera, monitorizar planes de ejecución «vivos» mientras se ejecutan. La realidad es bastante más tozuda ya que la información no la podemos obtener de forma sencilla sino que debemos explotar la información existente combinada con el plan de ejecución estimado.

Supongamos que tenemos la siguiente consulta que ejecuta en la sesión 57:

select count_big(*) 
from sys.objects s1
cross join sys.objects s2
cross join sys.objects s3
cross join sys.objects s4
cross join sys.objects s5

Si mostramos el plan de ejecución podemos ver los identificadores de nodo de los distintos operadores. Por ejemplo en rojo indico los de los operadores de la rama de la parte superior:

dm_exec_query_profiles

Si ejecutamos la consulta con la opción SET STATISTICS PROFILE ON al terminar la ejecución podemos ver, entre otros datos, el total de filas y las estimadas por cada nodo. Si queremos obtener esta información antes de que la consulta finalice podemos utilizar la DMV sys.dm_exec_query_profiles y lanzar desde otra sesión una consulta como esta:

SELECT  
node_id,physical_operator_name operador, SUM(row_count) filas_reales, SUM(estimate_row_count) AS filas_estimadas, min(first_row_time) primera_fila, max(last_row_time) ultima_fila, 
convert(decimal(16,2),SUM(row_count)*100.0/SUM(estimate_row_count)) porcentaje
FROM sys.dm_exec_query_profiles p
WHERE p.session_id=57
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

dm_exec_query_profiles1

Como hemos añadido un porcentaje calculado en base al número de filas estimadas la cifra puede superar el 100% si el número de filas reales supera el número estimado. Esto podemos corregirlo con una consulta adicional que limite al 100% el valor máximo y además aproveche para calcular el número de filas por segundo que pasan por cada operador:

SELECT node_id,operador,filas_reales,filas_estimadas,
case when porcentaje<100 then porcentaje else 100 end porcentaje,
primera_fila, ultima_fila, 
case when filas_reales > 0 and ultima_fila-primera_fila > 0 then filas_reales*1.0/(ultima_fila-primera_fila)*1000.0 else 0 end filas_segundo
from 
(
	SELECT  
	node_id,physical_operator_name operador, SUM(row_count) filas_reales, SUM(estimate_row_count) AS filas_estimadas, min(first_row_time) primera_fila, max(last_row_time) ultima_fila, 
	convert(decimal(16,2),SUM(row_count)*100.0/SUM(estimate_row_count)) porcentaje
	FROM sys.dm_exec_query_profiles p
	WHERE p.session_id=57
	GROUP BY node_id,physical_operator_name
) a
ORDER BY node_id;

dm_exec_query_profiles2

Finalmente podemos formatear un poco el resultado y añadir una media de porcentajes para tener una idea aproximada del porcentaje global de ejecución:

WITH estado (node_id,	operador,	filas_reales,	filas_estimadas,	porcentaje,	primera_fila,	ultima_fila,	filas_segundo)
AS
(
	SELECT node_id,	operador,	filas_reales,	filas_estimadas,	porcentaje,	primera_fila,	ultima_fila,	convert(decimal(16,2),filas_segundo) filas_segundo
	from 
	(
		SELECT node_id,operador,filas_reales,filas_estimadas,
		case when porcentaje<100 then porcentaje else 100 end porcentaje,
		primera_fila, ultima_fila, 
		case when filas_reales > 0 and ultima_fila-primera_fila > 0 then filas_reales*1.0/(ultima_fila-primera_fila)*1000.0 else 0 end filas_segundo
		from 
		(
			SELECT  
			node_id,physical_operator_name operador, SUM(row_count) filas_reales, SUM(estimate_row_count) AS filas_estimadas, min(first_row_time) primera_fila, max(last_row_time) ultima_fila, 
			convert(decimal(16,2),SUM(row_count)*100.0/SUM(estimate_row_count)) porcentaje
			FROM sys.dm_exec_query_profiles p
			WHERE p.session_id=57
			GROUP BY node_id,physical_operator_name
		) a
	) b
)
SELECT estado.*, (SELECT AVG(case when porcentaje>0 then porcentaje else null end) from estado) media_porcentaje
from estado
ORDER BY node_id;

dm_exec_query_profiles3

Llegados a este punto, toca hablar del lado oscuro de esta característica, de sus riesgos. Para que la DMV sys.dm_exec_query_profile nos devuelva datos es necesario que el servidor esté procesando los planes de ejecución reales de forma directa o indirecta. Concretando un poco, tenemos varios posibles escenarios:

  • Que la sesión que ejecuta la consulta tenga previamente activada la opción SET STATISTICS PROFILE o SET STATISTICS XML. Esta es la opción menos intrusiva ya que únicamente nos afecta a la consulta que queremos monitorizar. Desgraciadamente es la opción que no suele poderse implementar ya que implica cambios en el código de las aplicaciones.
  • Configurar una sesión de eventos extendidos que capture todos los eventos de tipo sqlserver.query_post_execution_showplan y los derive a un fichero o target en memoria.
  • Configurar una sesión de eventos extendidos que capture los eventos de tipo sqlserver.query_post_execution_showplan filtrados y los derive a un fichero o target en memoria.
  • Configurar una traza de profiler en cliente donde se seleccionen eventos como el Showplan Statistics Profile o Showplan XML Statistics Profile filtrados.
  • Configurar una traza de profiler filtrada en lado servidor equivalente a la anterior.

Es importante que se tenga en cuenta que aunque el filtro elimine todos los registros, por ejemplo añadiendo un filtro por session_id=65535, el servidor seguirá gestionando todos esos eventos y pagando el precio por ello. Como muestra de este impacto a continuación muestro el resultado en consumo de CPU y en batches por segundo de una carga para cada uno de estos casos:

rendimiento

Como podemos ver la diferencia de throughput es muy sustancial, pasando de 739 batches por segundo en el primer caso a 548 en el segundo mejor de los casos (26% de penalización) y bajamos a 185 en el peor (75% de penalización). Esto traducido a un entorno de producción puede representar una saturación o caída de rendimiento tan elevada que cause interrupción del servicio, incidencias de los usuarios, etc.

En conclusión, esta nueva funcionalidad de SQL Server 2014 puede ser muy conveniente en aquellos entornos donde el coste de capturar los planes de ejecución no sea elevado y la información del «grado de avance» de las consultas sea significativamente útil. Por ejemplo aquellos servidores que procesen queries de tipo analítico, o ejecuten procesos ETL, o lancen informes podrían soportar esta sobrecarga sin un impacto significativo. En aquellos servidores tipo OLTP cuya carga sea elevada, que procesen un número de batches por segundo elevado, el uso de esta característica deberá evaluarse con muchísima precaución para evitar causar problemas.

 

 

Rubén Garrigós