Calculando un grado de paralelismo “razonable”

Un parámetro de configuración que genera habitualmente discusiones de lo más controvertidas es el grado máximo de paralelismo (max degree of parallelism). En este artículo vamos a ver las recomendaciones generales y también una aproximación a cómo podemos calcular este valor de una forma más empírica para nuestras consultas.

Para sistemas con la carga más típica que nos solemos encontrar (mayor parte de OLTP y algunas consultas analíticas) las recomendaciones generales son un buen punto de partida:

• En sistemas NUMA, no configurar un grado de paralelismo mayor que el número de cores físicos por nodo NUMA.
• No configurar el grado de paralelismo máximo por encima de 8.

También es importante que en el caso que nuestra instancia vaya a ser utilizada por software de terceros revisemos las recomendaciones del fabricante al respecto. Por poner un ejemplo habitual, Dynamics AX recomienda un grado de paralelismo 1. Al tratarse de un parámetro a nivel de instancia y no de base de datos deberemos buscar un valor de compromiso pensando en todas las aplicaciones que vayamos a ejecutar. En casos extremos tendremos que aislar en instancias independientes las distintas aplicaciones o, si disponemos de Enterprise Edition, podremos utilizar Resource Governor para controlar este parámetro. También es importante revisar el algoritmo utilizado por SQL Server para obtener el valor de paralelismo a utilizar que podemos encontrar en el siempre interesante blog de CSS SQL Escalation Services: http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx

En otros casos nos puede interesar ajustar el grado de paralelismo más óptimo para un conjunto reducido de consultas. Para ello tendremos que tener en cuenta tanto la capacidad de aprovechamiento del paralelismo como también el grado de concurrencia que van a tener dichas consultas. Dicho de otra forma, una consulta que se ejecute en una ventana de mantenimiento y sin concurrencia podrá ser más “avariciosa” en el consumo de recursos que una que se ejecute durante la jornada laboral con  una concurrencia media de 5 consultas concurrentes. Una posible técnica para evaluar cuál sería ese grado de paralelismo apropiado sería lanzar la consulta con distintos grados de paralelismo y medir los consumos de recursos, esperas, etc. que se generan. Por ejemplo podemos utilizar una sesión de xevents para capturar las esperas por sesión y una traza de Profiler para capturar las ejecuciones con distinto grado de paralelismo.

Imaginemos que queremos analizar el comportamiento de la siguiente consulta lanzada sobre “tablas engordadas” de AdventureWorks (Script de Adam Machanic disponible en http://sqlblog.com/blogs/adam_machanic/attachment/39106.ashx):

SELECT
    BP.ProductID,
    cnt = COUNT_BIG(*)
	FROM dbo.bigProduct AS BP
	JOIN dbo.bigTransactionHistory AS BTH
		ON BTH.ProductID = BP.ProductID
	GROUP BY BP.ProductID
	ORDER BY BP.ProductID

Un script que nos puede servir para capturar las esperas para distintos grados de paralelismo (de 8 a 1 en este ejemplo) sería el siguiente:

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'eventos_session')
    DROP EVENT SESSION eventos_session ON SERVER
GO
DECLARE @create nvarchar(max) = '
CREATE EVENT SESSION eventos_session ON SERVER
ADD EVENT sqlos.wait_info
    (WHERE sqlserver.session_id = ' + convert(nvarchar(max),@@spid) + ')
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS); '
EXEC (@create)
GO
declare @iterar int = 5
declare @query nvarchar(max) = '
IF OBJECT_ID(''tempdb..#temp'') IS NOT NULL DROP TABLE #temp
SELECT
    BP.ProductID,
    cnt = COUNT_BIG(*)
	into #temp
	FROM dbo.bigProduct AS BP
	JOIN dbo.bigTransactionHistory AS BTH
		ON BTH.ProductID = BP.ProductID
	GROUP BY BP.ProductID
	ORDER BY BP.ProductID
'
declare @sql nvarchar(max) 
declare @maxdop int = 8 
declare @fecha datetime
declare @fecha2 datetime

IF OBJECT_ID('tempdb..#esperas') IS NOT NULL DROP TABLE #esperas

create table #esperas (
maxdop int,
duracion_ms int,
wait_type varchar(max),
total bigint,
total_wait_time_ms bigint,
total_resource_wait_time_ms bigint,
total_signal_wait_time_ms bigint
)

while (@maxdop >= 1)
begin
	ALTER EVENT SESSION eventos_session ON SERVER STATE = START;

	set @fecha=getdate()
	set @sql= @query + ' OPTION (RECOMPILE, QUERYTRACEON 8649, MAXDOP ' + convert(varchar(max),@MAXDOP) + ')' 
	exec(@sql)
	set @fecha2=getdate()

	IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t

	SELECT
		CAST (target_data AS XML) xml
		into #t
	FROM sys.dm_xe_sessions AS s 
	JOIN sys.dm_xe_session_targets AS t 
		ON t.event_session_address = s.address
	WHERE s.name = 'eventos_session'
	AND t.target_name = 'ring_buffer'

	INSERT INTO #esperas
	select 
		@maxdop,
		datediff(ms, @fecha, @fecha2) duracion_ms,
		table2.wait_type,
		count(*) total,
		SUM (convert(bigint,table2.duration)) AS total_wait_time_ms,
		SUM (convert(bigint,table2.duration))-SUM (convert(bigint,table2.signal_duration)) AS total_resource_wait_time_ms,
		SUM (convert(bigint,table2.signal_duration)) AS total_signal_wait_time_ms
		FROM
			-- Credit to Jonathan Kehayias for the xevent extraction query template
			(  
				SELECT  XEvent.query('.') XEvent
				from #t t
				CROSS APPLY t.xml.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
			) table1
			CROSS APPLY
			(  SELECT *
			   FROM
			   (   -- Get the Event Name
				   SELECT
					   'event_name' AS 'name',
					   RTRIM(LTRIM(XEvent.value('(event/@name)[1]', 'nvarchar(max)'))) AS value
				   UNION ALL
				   -- Get the Event Timestamp
				   SELECT
					   'timestamp' AS 'name',
					   RTRIM(LTRIM(XEvent.value('(event/@timestamp)[1]', 'nvarchar(40)'))) AS value
				   UNION ALL
				   -- Get the Event data columns
				   SELECT
					   RTRIM(LTRIM(XData.value('@name[1]','nvarchar(max)'))) AS 'name',
					   RTRIM(LTRIM(COALESCE(NULLIF(XData.value('(text)[1]','nvarchar(max)'), ''),
								 XData.value('(value)[1]','nvarchar(max)')))) AS 'value'
				   FROM XEvent.nodes('event/data') AS Data (XData)
				   UNION ALL
				   -- Get the Event action columns
				   SELECT
					   XData.value('@name[1]','nvarchar(max)') AS 'name'
					   ,COALESCE(NULLIF(XData.value('(text)[1]','nvarchar(max)'), ''),
								 XData.value('(value)[1]','nvarchar(max)')) AS 'value'
				   FROM XEvent.nodes('event/action') AS Data (XData)   ) AS tab
			   PIVOT
			   (   MAX(value)
				   -- Specify columns for PIVOT Operation
				   FOR name IN
					   (   [event_name], [timestamp], [wait_type], [opcode], [duration], [signal_duration]
					   )
			   ) AS pvt
			) AS table2
		WHERE table2.[opcode] = 'End'
		and table2.wait_type in ('SOS_SCHEDULER_YIELD','CXPACKET')
		GROUP BY table2.wait_type

	ALTER EVENT SESSION eventos_session ON SERVER STATE = STOP;
    
	-- Bajamos de 2 en dos y probamos por último en serie, maxdop=1
	SET @maxdop = @maxdop -2
	if @maxdop = 0
		set @maxdop=1
END
GO 

select * from #esperas

En el script básicamente componemos la sesión de eventos extendidos de forma dinámica (para poder filtrar por la sesión actual) y luego iteramos ejecutando la consulta con distintos niveles de paralelismo máximo. Como salida de este script obtendremos una salida similar a esta:

Como vemos en el caso de planes paralelos tenemos entre 2 y 4 segundos de tiempos “perdidos” en sincronización entre threads y el propio tiempo del thread 0, “padre” o controlador del resto. Sería ideal que pudiéramos obtener desglosada esta espera de CXPACKET de forma que nos quedáramos solo con el tiempo de sincronización entre threads excluyendo el tiempo del thread padre.

Por otra parte con Profiler podemos obtener las distintas ejecuciones, sus tiempos y consumos agregados de CPU:

Si comparamos las duraciones reales con las ideales podemos ver como la “brecha” es menor cuando el paralelismo es 2 que cuando éste es 4,6 u 8.

Si comparamos el consumo de CPU por thread vemos un comportamiento similar entre el valor ideal (reparto perfecto y sin costes de sincronización) y la realidad:

También podemos observar el porcentaje de mejora (reducción en % de la duración) vs el porcentaje de aumento de consumo de recursos (aumento en % de milisegundos de CPU) :

Llegados a este punto deberíamos plantearnos cuantos cores tenemos disponibles, cuanta concurrencia de este tipo de consultas vamos a tener y cuantos cores queremos dejar libres para el resto de carga del servidor. En este caso no tenemos “regresiones” ya que aunque aumentemos el paralelismo hasta 8 siempre obtenemos alguna mejora. En algunos casos, normalmente cuando excedemos el tamaño del nodo NUMA en lo que al número de cores respecta, podemos encontrarnos con “saltos” donde pasar de por ejemplo 8 cores a 9 suponga un aumento en el tiempo de respuesta. En general la curva de la tendencia es similar a la que tenemos aunque con un grado de “aplanamiento” distinto en la reducción del tiempo total de ejecución. Existen consultas que, en un hardware concreto, escalan muy bien hasta el 100% de los cores disponibles y otras que a partir de unos pocos cores ya no resultan más eficientes o incluso se vuelven menos eficientes cuantos más threads se involucran.

En resumen, si esta consulta concreta se fuese a ejecutar sin concurrencia y con todos los recursos disponibles para su ejecución optaríamos por el grado de paralelismo 8, ya que es el que obtiene un mejor tiempo de respuesta. Sin embargo si el grado de concurrencia fuese a ser notable, por ejemplo 10 queries concurrentes en un sistema con 32 cores, optaríamos por un grado de paralelismo de 2, con el que se podría llegar a consumir hasta 20 cores en total. Si necesitáramos consumir menos de esos 20 cores concurrentemente o el número de queries a soportar fuese mayor optaríamos por el plan serie por su mayor eficiencia desde el punto de consumo de CPU.

Por último no podemos olvidar que existen factores adicionales que tenemos que tener en cuenta ya que pueden darnos resultados muy distintos durante las pruebas y cuando estemos en un escenario de producción. Un caso habitual que nos encontramos en nuestras consultorías es el impacto de mezclar cargas analíticas con OLTP. Los continuos “picotazos” de CPU que las consultas OLTP demandan de los distintos cores hacen que el tiempo total y las diferencias de tiempos entre threads se acrecienten, haciendo los planes paralelos mucho menos eficientes que en un servidor sin carga. En esos casos una buena herramienta es forzar el particionado de la carga mediante resource governor, mapeando nodos numa a puerto TCP, etc.  de forma que dediquemos un conjunto de cores del servidor para la carga OLTP exclusivamente y el resto para las consultas pesadas de tipo analítico.