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.
