En SQL Server podemos ajustar el paralelismo desde diversos puntos de vista, para conseguir diferentes resultados en función de nuestras necesidades. Una cosa que debemos recordar siempre es que aunque SQL Server posee reglas para auto-optimizarse en función del hardware detectado al iniciarse, es cierto tambien que debemos realizar ajustes al mismo en función de cómo estemos utilizando la instancia, esto es, qué tipo de peticiones le hacemos al motor relacional.
Dicho esto, SQL Server nos ofrece las siguientes posibilidades a la hora de afinar el paralelismo:
- A nivel Hardware
- A nivel de instancia
- A nivel de conexión
- A nivel de sentencia T-SQL
NOTA: En este post, vamos a introducirnos en los significados de cada una de dichas configuraciones, para ir familiarizándonos de cara a los futuros posts que tratarán sobre el tema.
A nivel hardware
SQL Server es capaz de detectar el hardware en el que está corriendo de forma que se ajusta de forma diferente para aprovechar todas sus caracteristicas en cada momento.
De forma muy genérica, es capaz de distinguir entre correr en un hardware SMP, cuya arquitectura de alto nivel sería esta:
Y detectar si está corriendo en Hardware NUMA (Non-Uniform Memory Access), cuya arquitectura de alto nivel seria esta:
El hardware NUMA es bastante común en entornos críticos donde se requiere alto rendimiento, pero debido a sus beneficios y reducción de costes es cada vez mas frecuente encontrarlos en equipos “estandard” empresariales. En este tipo de arquitecturas, se dispone de varios nodos NUMA, cada uno compuesto por sus propios bancos de memoria, CPU y controladora, estando todos los nodos interconectados entre si. Típicamente el SO abstrae de la gestión de memoria y CPU a las aplicaciones que corren en dicho hardware, pero si se quiere que la aplicación explote al máximo las prestaciones de la máquina hay que realizarle ciertos “ajustes”. Una de las razones mas importantes es que si existe por ejemplo un thread corriendo sobre el NODO1 que debe acceder a una página de memoria alojada en otro nodo (digamos NODO2), típicamente se tarda entre 3 y 4 veces el tiempo que se tardaría en acceder a dicha página, si dicha página se encontrase en el NODO1. Por tanto, si la aplicación supiese que corre en hardware NUMA y se hiciera lo posible por tratar de almacenar la memoria relacionada cerca entre si dentro de los mismos nodos NUMA, se estaría favoreciendo el rendimiento enormemente (recordemos entre 3 y 4 veces la penalización por acceso al exterior). Este concepto es el que denominaremos a partir de ahora como DATA LOCALITY y trata de resumir la esencia de buscar siempre, que el dato se encuentre lo mas cerca del origen que lo pide, tanto él como los que futuramente puedan ser accedidos.
Dado que SQL Server es una aplicación diseñada para aprovechar arquitecturas NUMA, nosotros podemos obviamente modificar los parámetros hardware del sistema para que SQL Server se autoajuste en función del nº de nodos disponibles, por ejemplo. Obviamente este concepto de “afinamiento” es méramente anecdótico y se ha añadido para recordar al lector que SQL Server posee un mecanismo de auto-afinamiento que en este caso aprovecha el hardware existente favoreciendo DATA LOCALITY.
Además, entre otras cosas incrementa el nº de hidden schedulers (como por ejemplo lazywritter) de forma que SQL Server creará 1 por cada nodo NUMA disponible.
*Imagen donde se puede observar el contenido del fichero ERRORLOG indicando cómo se han detectado 3 nodos NUMA hardware en el sistema y el nº de procesos LAZY WRITTER creados (uno por cada nodo numa)
A nivel de instancia
A nivel de instancia es donde mas podemos afinar el comportamiento de SQL Server. Tenemos las siguientes posibilidades:
- 1.Máscaras de afinidiad
- 2.Degree of parallelism
- 3.cost threshold for parallelism
- 4.max worker threads
Máscaras de afinidad
A la hora de configurar las máscaras de afinidad, podemos trabajarlo en 3 niveles:
1. Afinidad de procesador
La afinidad de procesador se usa para indicar qué CPU podrán ser accesibles por SQL Server. Además, podremos vincular hilos a determinados procesadores. El resultado real de desactivar CPU es que SQL Server desactivará los schedulers asociados y por tanto efectívamente no utilizará dichas CPU
¿Para qué querríamos hacer esto?
Como sabemos, podemos tener mas de una instancia SQL Server instalada en la misma máquina. En estos escenarios, para evitar peleas por el recurso CPU que entorpezcan el rendimiento general del sistema, es una buena práctica des-solapar las CPU de ambas instancias.
Por ejemplo, si tenemos 8 CPU y 2 instancias instaladas, dejaríamos activadas las CPU 0-3 para la instancia 1 y las CPU 4-7 para la instancia 2.
Para más información: http://msdn.microsoft.com/en-us/library/ms187104.aspx
2. Afinidad de E/S
Toda operación de E/S debe ser finalizada (cálculo de checksum, número de bytes transferidos, número de página correcto,…) y para ello se realiza un consumo de CPU. Este tipo de operaciones es realizado por el proceso denominado LAZY WRITTER por un scheduler oculto. Configurando la afinidad de E/S podremos indicar qué CPU podrán ser accesibles por el scheduler oculto que realiza estas tareas.
¿Por qué querríamos hacer esto?
Al igual que el afinamiento de CPU, el afinamiento de E/S está pensado para mejorar el rendimiento en entornos donde la presión de E/S es alta. De esta forma, podemos limitar a determinadas CPU el gasto computacional asociado a operaciones de finalización de E/S.
Tened presente, que la idea en este caso es precisamente evitar que los schedulers normales y los schedulers ocultos de finalización de E/S compitan por los mismos recursos. Esto quiere decir que lo que buscamos es esto:
No queremos esto:
Ya que estaríamos haciendo competir por la CPU a los schedulers normales que se encuentran realizando tareas sql, y a los schedulers ocultos como el de finalización de E/S.
La propia GUI de SSMS te impide las malas configuraciones evitando que puedas marcar la misma CPU para afinidad de procesador y afinidad de E/S, lo cual es algo de agradecer:
*Ejemplo cualquiera de configuración de afinidad CPU y afinidad E/S mediante asistente
Para más información: http://msdn.microsoft.com/en-us/library/ms189629.aspx
3. Afinidad de red
Existe un método avanzado para optimizar sistemas SQL Server consistente en vincular un determinado puerto de comunicaciones, con un determinado NODO numa. Para realizar esta configuración, obviamente debemos disponer de hardware NUMA y obviamente con mas de 1 NODO.
La idea es que consitamos un aumento de rendimiento a base de facilitar DATA LOCALITY mediante cierta “inteligencia” por nuestra parte:
Imagen que muestra afinidad de red vinculado 4 puertos a 4 nodos NUMA
¿Por qué querríamos hacer esto?
La idea que pretendemos llevar a cabo es favorecer estadísticamente que los datos utilizados por determinadas conexiones se encuentren siempre en el mismo NODO numa ( o al menos que estadísticamente sea mucho mas probable).
Lo que se hace es vincular un determinado puerto (por ejemplo en la imagen el puerto 8000) a un determinado nodo NUMA (en dicho ejemplo al NODO 1). Luego, en las aplicaciones, redirigiremos la cadena de conexión para indicar que nos conectaremos a la instancia SQL Server mediante el puerto 8000, consiguiendo que dicha aplicacion siempre utilice de entrada el nodo NUMA 1 y por tanto, que los datos de páginas a acceder se encuentren mas probablemente en los bancos de memoria de dicho NODO.
En entornos críticos, aplicando este tipo de configuraciones se puede llegar a obtener aumentos de rendimiento interesantes rondando el 20%.
Para más información: http://support.microsoft.com/kb/299641
Degree of parallelism
El grado de paralelismo es posiblemente la configuración mas comunmente utilizada a la hora de afinar el grado de paralelismo de SQL Server. En este caso, estamos hablando del grado de paralelismo a nivel de instancia.
Su configuración indica el máximo número de CPUs que un determinado worker puede utilizar, esto es, el nº de hilos que un determinado operador puede abrir como máximo en ejecución.
No debe confundirse con la afinidad de CPU, ya que en este caso no estamos desactivando CPU, estamos limitando el nº de hilos disponibles para resolver una consulta. Además, su valor es determinado en tiempo de ejecución y no afecta para nada al plan cache.
Su valor además puede ser especificado a nivel de workload group dentro de una configuración de Resource Governor. Haciendo esto, estaremos configurando la forma de proceder en términos de hilos por operador, para determinadas conexiones a SQL Server.
Para más información: http://msdn.microsoft.com/en-us/library/ms188611(v=sql.105).aspx
Cost threshold for parallelism
Utilizado para modificar el nº de planes de ejecuciones paralelos generados en el sistema, es una de las configuraciones mas desaprovechadas por los DBA de SQL Server, quizás por su escasa divulgación y porque requiere un conocimiento del negocio en el que está corriendo SQL Server para poder explotarla con éxito.
La idea es que un sistema OLTP puro no debería tener ejecuciones paralelas, ya que implica que SQL Server ha determinado un coste elevado para su resolución, pero a su vez, SQL Server finalmente tambien acaba resolviendo operaciones no OLTP ( las tan temidas consultas gigantescas a la BBDD para devolver informes 🙂 ). La idea por tanto es jugar con este parámetro, para aumentar o disminuir el nº de operaciones paralelas que se ejecutan. Reconozco que es un parámetro un tanto “peligroso” en tanto en cuanto no se posee un control absoluto de lo que va a suceder en el sistema a ciencia cierta (cambias estadísticamente el nº de planes a generarse, pero no sabes cuales serán ya que no los puedes vincular a ellos, como con “degree of parallelism”). Es por ello que solo debes tocarlo si estas seguro de lo que deseas conseguir y siempre y cuando seas capaz de medir el impacto de su modifición para saber si vas en la dirección correcta.
Su valor es utilizado por SQL Server cuando se está evaluando el plan de ejecución a utilizar, de forma que cambiar su valor influirá en cómo se ejecutará el plan de ejecución.
Pseudo código donde se utiliza “threshold for parallelism” para obtener plan de ejecución
Para más información: http://msdn.microsoft.com/en-us/library/ms188603(v=sql.105).aspx
Max worker threads
Este parámetro sirve para configurar el número de subprocesso de trabajo disponibles para los procesos de SQL Server. Modificar este parámetro puede optimizar el rendimiento cuando un gran número de conexiones se abren contra la instancia SQL Server ya que generalmente se crea un subproceso de sistema independiente para cada solicitud, pero gracias a esto se pueden agrupar varias solicitudes a determinados grupos de trabajo.
Esta característica, al igual que la característica “threshold for parallelism” se considera avanzada y solo recomendable aplicarla si se poseen conocimientos profundos de SQL Server y de cómo se puede beneficiar el escenario concreto donde se aplicará.
Para hacerse una idea, puedes ver la siguiente tabla donde se obtienen los valores de max worker threads que SQL Server configura dependiendo del entorno donde se está ejecutando y su versión de binarios
Para más información: http://msdn.microsoft.com/es-es/library/ms187024(v=sql.105).aspx
A nivel de conexión
A nivel de conexión SQL Server nos ofrece la posibilidad de configurar el grado de paralelismo mediante el uso de Resource Governor. De esta forma, podemos configurar SQL Server para que dependiendo de nuestra función de enrutado de resource governor, se comporte diferente en términos de paralelismo.
¿Para qué querríamos hacer esto?
Bueno, pues podríamos forzar a que todas las operaciones de reindexado de índices, backups, recálculo de estadísticas…por defecto funcionaran al máximo DOP posible con el fin de optimizar los recursos.
Otra posibilidad tambien podría ser predefinir que todas las conexiones de una aplicación que utiliza SQL Server como OLTP puro, utilicen un DOP de 1.
Como vemos, nos ofrece unas posibilidades bastante mas que interesantes a la hora de optimizar sistemas utilizados por aplicaciones heterogéneas.
Para más información: http://technet.microsoft.com/es-es/library/bb895232(v=sql.105).aspx
A nivel de sentencia T-SQL
A nivel de sentencia T-SQL tenemos la cláusula MAXDOP con la que podemos especificar el valor “degree of parallelism” que queremos que tenga la sentencia concreta a la que se lo apliquemos. Como hemos visto, el grado de paralelismo puede especificarse a nivel de instancia, de conexión (con resource governor) y ahora de sentencia; la precedencia siempre es la que posea el nivel de granuldaridad mas bajo. De esta forma, sea cual sea la configuracion DOP de instancia y Resource Governor, si una sentencia posee MAXDOP=8, dicha sentencia utilizará como mucho 8 hilos por operador, independientemente de los valores configurados a nivel de instancia y/o resource governor.
¿Por qué querríamos configurar una consulta con un MAXDOP distinto?
Imaginemos que a la instancia le hemos configurador DOP = 1, por lo que por defecto, todas las peticiones serán en serie, luego configuramos resource governor con un workload group para que tenga DOP = 4 una determinada aplicación,….¿qué pasa si queremos lanzar desde dicha aplicación cliente una agregación de datos masiva y nuestra máquina tiene 32 CPU? Esta la podríamos realizar con MAXDOP=0 y daría igual el resto de configuraciones, siempre sería capaz de crear si le hiciera falta hasta 32 hilos por operador.
Para más información: http://msdn.microsoft.com/en-us/library/ms181714.aspx
Stay tuned for more news on our blog and subscribe to our newsletter if you want to receive our new posts in your mail, get course discounts… ?
