En el capítulo anterior de esta serie veíamos que el componente DQS Cleansing de SSIS 2012 puede optimizarse usando técnicas de paralelización dentro del flujo de datos. En éste veremos como podemos exprimir aún más nuestro paquete de Integration Services con dos nuevas alternativas de diseño.
Flujo paralelo con el Balanced Data Distributor
Desde Microsoft, a partir de la versión 2008 R2 de SQL Server Integration Services, se ha ido publicando un componente llamado Balanced Data Distributor que no forma parte del producto en su versión RTM pero que es de libre descarga y está especialmente diseñado para este tipo de casos en los que se quiere paralelizar una tarea. Simplemente tendremos que conectar N (en este caso de nuevo 6, de nuevo por alineación con los cores) componentes a las salidas del Balanced Data Distributor y ejecutar el paquete.
Con las mismas modificaciones que hicimos en el post anterior de esta serie sobre las propiedades EngineThreads y MaxConcurrentExecutables, vemos que también tenemos los diferentes cores trabajando a la vez:
Aquí sí estamos tratando con el máximo de filas posibles en cada envío al servidor DQS, 10.000 filas. Esto es a pesar de encontrarnos con el mismo plan de ejecución que en caso del uso del operador módulo (con 6 sub-paths). Esto es porque el motor de ejecución de Integration Services sólo “ve” 6 componentes en paralelo que sabe que pueden ejecutarse en diferentes hilos, pero no conoce los detalles del componente y que sería más optimo generar buffers nuevos para que leyesen más filas de golpe. De la creación de los buffers para cada componente y por tanto de utilizarlos de manera mas eficiente se encarga el Balanced Data Distributor.
La ejecución de este paquete tarda
Esto nos da una mejora sobre el secuencial de 2654 / 907 = 2,93, ligeramente superior a la obtenida con la paralelización usando el operador módulo.
Flujo con orígenes paralelos
En este caso vamos a simular la división de datos en N flujos de datos (1 por core) desde el mismo origen de datos. De nuevo usaremos la cláusula ROW_NUMBER() de TSQL para dividir los datos. Por ejemplo:
Repetiríamos para cada componente, cambiando la condición para cubrir los 6 casos posibles (de 0 a 5)
Con esta estructura del flujo de datos conseguimos el siguiente plan de ejecución
De esta manera conseguimos paths propios para cada rama desde el origen de datos hasta el componente DQS Cleansing. Por tanto, es el propio motor de SSIS el que se encarga de lidiar con la generación de hilos y nuevos buffers. Así, aunque requerimos de una arquitectura de flujo de datos algo más compleja, al evitar incluir componentes extra tenemos los buffers y los hilos de ejecución controlados de manera nativa por SSIS, lo que mejora el rendimiento.
En el anterior post habíamos comentado la mejoría de rendimiento cuando trabajamos con buffers más grandes por la ventaja que supone para Integration Services el hecho de generar menos buffers nuevos. Cuando trabajamos en paralelo este principio se pierde ya que hacer envíos óptimos (de 10.000 filas exactamente) al servidor DQS resulta en menos envíos totales.
Por ejemplo, para enviar 30.000 filas en bloques de 10.000 necesitaríamos 3 buffers y un solo envío (en paralelo). Las mismas 30.000 filas con buffers de 15.000 necesitarían 2 buffers pero 2 envíos (10.000 x 2 en el primero y 5.000 x 2 en el segundo). Si cogemos el ejemplo de la ejecución de este paquete, con buffers alineados con el envío (10.000 filas por buffer) tarda
Con buffers más grandes y envíos sub-óptimos (con buffers de 16.000 filas, por ejemplo) tenemos
Esto es, casi un 50% más lento.
Como podemos ver en los test, esta arquitectura de diseño es la más rápida de todas las que hemos planteado con diferencia (más de 6 veces más rápida), pero hay que tener en cuenta que si ejecutamos el paquete SSIS en la misma máquina que aloja el servidor de DQS, esta alternativa puede derivar en una competencia de recursos entre SSIS (CPU y memoria necesarias para crear nuevos buffers y gestionar los diferentes hilos) y DQS, que recordemos es un proceso integrado dentro de SQL Server (sqlservr.exe). Esto nos puede llevar a agotar la memoria y eventualmente, a que Integration Services empiece a paginar buffers al disco duro, con el consiguiente decrecimiento en el rendimiento del paquete.
Conclusiones
En una pequeña tabla de tiempos podemos ver como mejora el rendimiento del mismo proceso de limpieza de datos con las diferentes opciones:
| Opción | Tiempo | Mejora |
| Secuencial | 44 minutos 14 segundos | |
| Paralelo | 15 minutos 44 segundos | 2,82 X |
| Paralelo BDD | 15 minutos 7 segundos | 2,93 X |
| Orígenes paralelos | 6 minutos 41 segundos | 6,61 X |
Mediante la paralelización del flujo de Integration Services podemos conseguir un incremento muy importante del rendimiento del componente DQS Cleansing, mayor incluso del que se plantea en la documentación oficial del producto, pero para ello hay que conocer bien y aplicar de manera correcta ciertos principios de paralelización y buffering en SSIS. Sin estas modificaciones en el paquete no podremos aprovechar al máximo la potencia de la herramienta.
Sin embargo, hay que tomar estas mediciones como lo que son, una muestra de que las alternativas mostradas funcionan y es posible mejorar el rendimiento del componente DQS Cleansing. Los resultados concretos de la aplicación de estás arquitecturas puede variar en función de multitud de factores. Desde la complejidad y riqueza de la base de conocimiento en DQS, los tipos de datos involucrados, el resto de componentes del paquete SSIS en el cual se encuentran nuestros componentes DQS, etc.
Además, como en cualquier tarea de mejora del rendimiento, no debemos olvidar la arquitectura que subyace en la máquina donde estemos ejecutando nuestros paquetes y sólo con un testeo exhaustivo de todas las opciones y modificaciones podremos llegar a conclusiones válidas y fiables.
Feliz tuning 🙂
Referencias
Diving into the SSIS execution engine
Integration Services: Performance Tuning Techniques
1 comment