GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Configurar MDOP y sus consecuencias de Rendimiento en SQL Server


Hace unos meses nos entregaron un par de máquinas con 48 CPUs y 72GB de RAM cada una, para montar un Cluster de SQL Server (Windows Server 2008 R2 y SQL Server 2008 R2, como Dios manda), en el que ejecutar una aplicación transaccional (es decir, un ERP, todo lo contrario a un Data WareHouse). Era la primera vez que trabajaba en SQL Server con una máquina con tal muestrario de procesadores, y evidentemente, tenía cierta curiosidad en si sería necesario realizar alguna configuración específica debida a tener que trabajar con tantas CPUs (¿muchas quizás?) en SQL Server. Pues resultó que SI.

Había varias cosas que me pitaban, al tener que realizar la configuración de un entorno de este tipo. Una de ellas era TEMPDB. ¿Cuántos ficheros le ponemos a TEMPDB? ¿48 ficheros de 1GB cada uno, por eso de tener un fichero por cada CPU? La verdad que 50GB de TEMPDB me parecía un poco exagerado sin tener información de cuanta TEMPDB necesitarían las nuevas instancias (y esto teniendo habilitado el Instant File Initialization, que sin tenerlo habilitado, ya sería todo un cachondeo). Al final, con ocho ficheros de 1GB cada uno, fue suficiente, y de hecho así fue como quedó finalmente configurado en Explotación. Tampoco busqué más información (no sé si habrá alguna recomendación o KB para este tema).

Este primer ejemplo, realmente no tenía demasiada importancia, simplemente es una de las dudas existenciales que pueden surgir al configurar una máquina con tantas CPUs para SQL Server. Tampoco tenía demasiada importancia el tema de la afinidad de CPU o de la afinidad de IO, ya que se trataban de máquinas dedicadas para SQL Server.

Dónde sí que encontramos cierta problemática, es en la configuración de MDOP (la opción max degree of parallelism del sp_configure). Con esto, sí que me encontré con algunos Problemas de Rendimiento en SQL Server.

MDOP en SQL Server: la teoría (como yo la entendí)

Conceptualmente, podemos entender MDOP (max degree of parallelism) como el máximo número de CPUs que puede llegar a utilizar cada consulta o sentencia en SQL Server. Por ejemplo, si nuestra máquina tiene 48 CPUs y todas ellas configuradas para ser utilizadas por SQL Server, si configuramos MDOP a 4 cada consulta podría llegar a utilizar 4 CPUs, lo cual hace que sea posible situaciones como ejecutar 12 consultas o sentencias simultáneamente, cada una de ellas paralelizada y utilizando 4 CPUs.

Vale. Más o menos, con esto podemos entender de qué va eso del MDOP en SQL Server. Ahora, ¿Cómo puedo saber qué valor de MDOP debo configurar en mi SQL Server?

Googleando por los Interneses, podemos encontrar la siguiente KB de Microsoft (que yo no conocía, hasta hace poco): Recommendations and Guidelines for 'max degree of parallelism' configuration option

Según esta KB, una recomendación general sería configurar MDOP a un valor de 8, dado que la máquina tiene más de 8 CPUs para SQL Server (en particular, tiene 48 CPUs).

Sin embargo, el objetivo de esta máquina era ejecutar las bases de datos de un ERP, en particular un entorno de Microsoft Dynamics (tanto Axapta 2009 como CRM4), y resulta, que tanto para Axapta como para CRM, la recomendación es utilizar MDOP configurado a 1.

Así que, el entorno quedó configurado con MDOP (max degree of parallelism) a 1.

Seguí googleando algo más sobre la configuración de MDOP (max degree of parallelism) en SQL Server, y básicamente lo que entendí fue que:

  • Para entornos transaccionales (como es el caso tradicional de los ERPs) y un SQL Server con múltiples CPUs, la configuración recomendada es de MDOP a 1, ya que suelen ejecutar muchas consultas y muy pequeñas, y con esta casuística sale más rentable evitar evaluar para cada consulta si es necesario paralelizarla o no, así como ahorrar el coste de paralelizar la susodicha.
  • Para entornos no transaccionales (como es el caso tradicional de los Data Warehouse) y un SQL Server con múltiples CPUs, la configuración recomendada es de MDOP a 0 (SQL Server seleccionará el número apropiado de CPUs a utilizar por cada consulta) o MDOP>1, ya que en este tipo de entornos suelen ejecutarse consultas costosas que afectan a un gran número de filas (ej: procesos de carga del DW, consultas de los informes, etc.), resultando rentable el coste de la paralelización.

A todo esto, deberemos tener en cuenta un detalle muy importante: la opción MDOP (max degree of parallelism) es una configuración a nivel de TODA la Instancia de SQL Server. Dado que habitualmente, en una instancia conviven múltiples bases de datos, deberemos tener cierta precaución, ya que la configuración que utilicemos podría ser beneficiosa para unas bases de datos, y perjudicial para otras. Vamos, que un fin de semana de los de “Prueba y Error”, quizás no nos venga del todo mal para dejar bien ajustadito nuestro Tunning.

También es cierto, que en caso de necesidad, podemos utilizar la sugerencia de consulta MAXDOP para especificar a mano en nuestras propias consultas qué configuración de MDOP deseamos ejecutar, independientemente de la configuración de MDOP (max degree of parallelism) a nivel de instancia. Bueno, para mí es rizar el rizo, ya que todo lo que uno hace, luego lo hay que mantener, así que habrá que estudiar bien el coste y el beneficio obtenido en cada caso (es mi humilde opinión).

Otro tema a tener en cuenta, es el relacionado con los Eventos de Traza Hash Warnings y Sort Warnings. Estos eventos, en muchas ocasiones representan problemas de estimación de memoria producidos por la reutilización de Planes de Ejecución, por lo que suele se recomendable comprender el comportamiento de la concesión de memoria de consulta (Memory Grant), los eventos Hash Warning/Sort Warning y el paralelismo (DOP). El problema empeora cuando tenemos múltiples CPUs en SQL Server, ya que la estimación de uso de memoria de una consulta, será mayor cuantas más CPUs se utilicen en su ejecución. Es decir, exactamente la misma consulta, consumirá más memoria RAM si se ejecuta utilizando 8 CPUs, que en caso de ejecutarse utilizando 2 CPUs (por poner un ejemplo). Y nosotros tenemos 48 CPUs. Tela. De aquí, que en muchos casos nos interese configurar MDOP a 1, así como crear Trazas personalizadas en SQL Server para registrar la ocurrencia de los Hash Warning y Sort Warning, y así poder una información bastante valiosa.

Y hasta aquí, la parte teórica de la configuración de MDOP (max degree of parallelism) en SQL Server. Ahora, pasemos a algún detalle práctico.

MDOP en SQL Server: la práctica (como yo la viví).

La parte práctica de la configuración de MDOP (max degree of parallelism) en SQL Server, al principio no dio para mucho, la verdad. Configuras la opción con el sp_configure, ejecutas el RECONFIGURE de turno, y aquí paz y después gloria.

Con el entorno real en producción y la configuración de MDOP a 1 en SQL Server, la máquina estaba consumiendo aproximadamente entre el 10% y el 15% de CPU en hora punta. Vaya pepino.

Un buen día, a alguien se le ocurre cambiar MDOP a 24. No pasa na. A mí lo que me manden. Configuras la opción con el sp_configure, lanzas el RECONFIGURE, y ya lo tienes. Todo sigue igual, aunque claro, al hacer los cambios fuera de la ventana de usuarios (sin carga de trabajo) tampoco había mucho que ver.

De pronto llega la mañana, y los usuarios empiezan a trabajar. Y ZAS. En la primera hora de trabajo, el sistema se vuelve inservible. Timeouts en las páginas Web, la máquina de SQL Server consumía aproximadamente entre el 65% de CPU, etc.

Se aplica rápidamente la marcha atrás del cambio, dejando de nuevo la configuración de MDOP a 1 en SQL Server, y el sistema, instantáneamente, vuelve a ser funcional.

A mi si me lo cuentan, no me lo creo. Jamás habría imaginado que podría llegar a ser tan vital la configuración de MDOP en una máquina SQL Server con tantas CPUs. Más aún, teniendo en cuenta que SQL Server por defecto viene configurado con MDOP a 0.

Se hicieron más pruebas con la configuración del MDOP en SQL Server. Por ejemplo, con MDOP puesto a 4, el sistema parecía también funcional, pero en algunos momentos picos de trabajo, la CPU subía bastante, el sistema empezaba a funcionar especialmente lento, y devolviendo la configuración de MDOP a 1, se volvía a la vida de nuevo.

Esta es la experiencia que quería contar.

Despedida y cierre

Y estas han sido mis reflexiones sobre la configuración de MDOP (max degree of parallelism) en SQL Server con múltiples (un huevo) CPUs. Me ha parecido una experiencia interesante, y como siempre, he querido aprovechar para compartirla con el resto de la comunidad. Espero que la lectura resultase de interés.

 


[Fecha del Artículo (UTC): 19/09/2011]
[Autor: GuilleSQL]


Comentarios

infoxodie - 20/09/2011 (UTC)
Muy buen articulo, gracias!



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas