El motor de Integration Services de SQL Server es capaz de ejecutar trabajos en múltiples hilos para obtener el máximo rendimiento del hardware que soporta el servicio. En el Control Flow (run-time engine) tenemos la capacidad de generar varios threads de tareas (cuando estas no requieren precedencia):
En este ejemplo el motor lanzará ambos contenedores en paralelo. Podemos optimizar el motor del Control Flow utilizando la propiedad MaxConcurrentExecutables para controlar el número de tareas que lanzará en paralelo. En un paquete con tres tareas de transformación de datos (DFT) podríamos configurar esta propiedad estableciendo el valor a 3 para asegurarnos de que el motor lanza todas las DFT al mismo tiempo
 |
 |
Pero esto no asegura que los distintos árboles de ejecución que contiene cada DFT se ejecuten de forma paralela.
Hasta aquí nada nuevo, pero ¿qué pasa con el paralelismo dentro de un DFT?
Todd McDermid (blog | twitter) ha publicado en su blog un estudio para generar paralelismo de forma manual para optimizar el rendimiento de sus paquetes Integration Services. El mecanismo que se puede observar es utilizar un Multicast para utilizar un mismo flujo de datos para realizar distintos Lookups al mismo tiempo y realizando posteriormente un Join para volver a unir a un mismo flujo las columnas obtenidas en los distintos hilos. La conclusión es que la ganancia en la ejecución en paralelo de algunos componentes se pierde al hacer la unión. Échale un vistazo al post en el blog de Todd McDermid.
Balanced Data Distributor
Recientemente Microsoft ha publicado un nuevo componente para implementar en el flujo de datos de Integration Services 2008 llamado Balanced Data Distributor (Distribuidor de datos balanceado, BDD) que permite dividir el flujo de datos en múltiples hilos para posteriormente generar las transformaciones necesarias sobre cada hilo.
¿Cómo funciona?
El funcionamiento del componente es bastante simple, genera tantos hilos como salidas de datos conectes. Si configuras 4 salidas o pipelines, se encargará de dividir aproximadamente la carga de datos en 4. Ten en cuenta que no es posible controlar qué filas van a una pipeline de salida o a otro.
¿Y de qué sirve?
No se puede utilizar distintos transformaciones sobre los hilos que genera el BDD porque no tendríamos todos los campos agregados en cada hilo en el pipeline final. Pero podemos realizar los mismos en paralelo.¿Y se mejora el rendimiento? Si ejecutas el paquete en una máquina con más de 2 cores verás que sí.
¿Cuándo usarlo?
Este componente no es la solución a todos los problemas de performance que pueden darse en SSIS y se requiere hacer un ejercicio de comprensión sobre el hardware con el que estamos trabajando (Cores, RAM, Discos) y la cantidad de datos que vamos a mover. Los detrimentos en rendimiento se producen mayormente debido a los cuellos de botella que se generan sobre el sistema (fuente, transformaciones y destintos). Por tanto y cómo nos cuentan en el blog de SQL Performance se recomienda su uso cuando
- La cantidad de datos con la que vamos operar es cuantiosa o pesada
- El origen de datos consume más rápido de lo que el resto de transformaciones es capaz de manejar.
- Los datos no se requieren ordenados (recuerda que no hay forma de controlar como se dividen las filas en la salida del BDD)
Y acción!
Vamos a ilustrarlo con ejemplos (esta es la parte a la que queríamos llegar).
La máquina que ha sufrido la batería de pruebas es un HP EliteBook 8540w con 8 cores y 16GB. El objetivo de estas pruebas es básicamente intentar resolver posibles cuellos de botella en las transformaciones implementando el componente BDD.
Probaremos también mejorar congestionamientos en los destinos, paralelizando las inserciones, pero seguramente sería más representativo si tuviera una base de datos correctamente distribuida en distintos discos.
Paralelismo en Transformaciones
Las características del diseño utilizado son muy comunes:
- Leer de origen OLEDB (AdventureWorks), 100.000 filas.
- Lookup para obtener algún dato de referencia
- Derived column para generar nuevas columnas en las filas
- Inserción en destino OLEDB, Fast load.
El primer intento fue sin utilizar el BDD, para tener el tiempo “estándar”:
 |
 |
No está mal, 3.89 segundos para 100.000 filas
Vamos a introducir el componente BDD y paralelizar las transformaciones en 4 hilos:
 |
 |
Mejora sustancial, 2.11 segundos un 46% más rápido en su ejecución.
Ahora exprimimos el procesador y sacamos 8 hilos:
 |
 |
Parece que el rendimiento sigue aumentando. Un 52% más rápido que la ejecución sin BDD.
En resumen:
| Threads | Tiempo ejecución(mm:ss.fff) | % Mejora aprox. |
| 1 | 00:03.890 | – |
| 4 | 00:02.106 | 49% |
| 8 | 00:01.872 | 52% |
Paralelismo en inserciones
Siguiendo el diseño del test anterior, se ha eliminado el componente Union All, agregando una copia del componente de destino por cada hilo de proceso. Hay que mencionar que la tabla no contiene índices por lo que almacena los datos en estructura Heap, ahorrando carga de trabajo a SQL para la siguiente prueba.
En esta ocasión vamos a trabajar con un conjunto de un millón de filas, con la intención de que los tiempos de ejecución sean más significativos.
El diseño del paquete con 8 hilos quedaría como muestra la imagen:
Los tiempos de ejecución con este modelo fueron los siguientes:
| Threads | Tiempo ejecución(mm:ss.fff) | % Mejora aprox. |
| 1 | 02:12.087 | – |
| 4 | 00:43.930 | 66% |
| 8 | 00:39.733 | 70% |
Conclusión
Podemos argumentar que efectivamente se distribuye la carga de trabajo de las transformaciones e inserciones al generar varios hilos y se mejora el rendimiento aprovechando los recursos del sistema y realizando las tareas en paralelo.
Otras optimizaciones para el flujo de datos.
Hay bastante documentación en la red sobre cómo podemos optimizar el flujo de datos para distintos escenarios y resolver, o al menos aliviar, los distintos cuellos de botella que se pueden generar en nuestros sistemas. Algunas referencias:
Orígenes
http://msdn.microsoft.com/en-us/library/dd795221(v=sql.100).aspx
http://henkvandervalk.com/reading-as-fast-as-possible-from-a-table-with-ssis-part-ii
Transformaciones
http://msdn.microsoft.com/en-us/library/cc966529.aspx
http://blogs.msdn.com/b/mattm/archive/tags/lookup
Nota. Si estas utilizando el componente BDD y quieres eliminarlo del diseño del flujo de datos por cualquier motivo, desvincula antes los pipelines que tenga conectados. Si no lo haces el BIDS se viene abajo. He abierto un caso en Connect con esta incidencia. ¿Qué es una incidencia algo trivial? Sí, pero si no has guardado fastidia un rato.
Nota 2. Esto sí que es una curiosidad de la que me di cuenta por casualidad, si te fijas en la descripción del componente en sus propiedades:
Evaluates and directs rows in a datasets to multiple destinations.
¿Habrán utilizado el código del Conditional Split cómo base? 🙂
Pues ya sabéis… a balancear!
Saludos.
