En la actualización para la Cumulative Update 1 del documento DQS Performance Best Practices podíamos encontrar las recomendaciones hardware así como las estimaciones de tiempo que el grupo de producto daba como orientación a la hora de ejecutar los diferentes procesos que puede llevar a cabo SQL Server 2012 Data Quality Services. Además, encontramos información acerca de los factores de mejora de estos procesos en el caso hipotético de que doblemos el número de núcleos de proceso (cores), siempre dependientes de la arquitectura general de la que dispongamos, conocido como el scale-up factor.Sin embargo, aunque tenemos datos sobre scale-up factor disponibles para todos los procesos que aprovechan el paralelismo de la máquina en DQS (que al estar basado en la arquitectura SQL Server son prácticamente todos, exceptuando la creación y publicación de bases de conocimiento y la importación / exportación de dominios y bases de conocimiento), no tenemos datos claros para el caso del componente DQS Cleansing de SSIS 2012.En el documento se nos remite a la paralelización “manual” del flujo de datos que queremos limpiar, dividiendo los datos con una transformación Conditional Split y haciendo trabajar a múltiples componentes DQS Cleansing en paralelo. Se da una estimación de un factor de mejora de entre 2,5 y 3.Vamos a ver, con ejemplos y añadiendo consideraciones más allá de la adición de nuevos procesadores o hilos de trabajo, hasta dónde podemos incrementar el rendimiento del componente DQS Cleansing  dentro de un flujo de SSIS 2012.

Primero mediremos el rendimiento en un flujo secuencial y luego iremos viendo alternativas en paralelo. Todos los ejemplos que se presentan se ejecutan sobre una máquina virtual con Windows Server 2012, SQL Server 2012 SP1 CU3, 6 cores lógicos y 12 GB de RAM asignada.

Flujo secuencial

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Simplemente habremos mapeado nuestra fuente de datos (en este caso, una tabla de log de conexiones a un router de una biblioteca, de donde queremos limpiar información sobre los paquetes de información intercambiados, nombres de usuario, direcciones IP, etc. que devuelve 385.737 filas) a un sólo componente DQS Cleansing.

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

La base de conocimiento usada es muy simple, solo tiene una regla para el título (ha de ser mayor de 10 caracteres) y una validación del código ISSN mediante una expresión regular

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Se ha puesto un ejemplo que usa sentencias T-SQL para la validación (la regla de longitud del título) y otro que utiliza CLRs (la expresión regular). Por lo general, cuanto más complejas sean las reglas de la base de conocimiento, más tiempo le tomará a DQS evaluar los registros, aunque esto varía mucho dependiendo de los casos y discutirlo se escapa del ámbito de este post.

Durante la ejecución podemos ver diversos aspectos interesantes:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Lo primero es que los 6 procesadores virtuales de nuestra máquina virtual se están infrautilizando. De hecho, sólo el procesador 2  parece que ha sufrido un pico considerable de trabajo al iniciar la ejecución del paquete y haber llegado filas al componente DQS Cleansing.

Además, en nuestro paquete habíamos incrementado las variables DefaultBufferMaxRows y DefaultBufferSize para intentar maximizar los datos que cupiesen en cada buffer de SSIS y así que se mandasen paquetes más grandes de datos al servidor DQS para su análisis. Ahora que, después de la Cumulative Update 1, la mayoría de procesos se reescribieron para que utilizasen CLRs (a pesar de un mayor requerimiento de memoria RAM), esto debería resultar en un mayor rendimiento, ya que aún tenemos margen de memoria hasta los 12 GB asignados a la máquina virtual.

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Pero, cuando vemos la pestaña de Progress tenemos algo bastante interesante:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Aunque, como veíamos en la primera imagen de la ejecución, el buffer tenía 16.103 filas (el máximo antes de pasar del límite de 100MB impuesto por DefaultBufferSize y la variable interna MaxBufferSize), DQS sólo acepta bloques como mucho de 10.000 filas, por lo que hasta que no resuelve y devuelve las primeras 10.000 filas no coge las siguientes 6.103 y las analiza. Nos da información sobre el chunk o pedazo de buffer que ha cogido y analizado y finalmente, sobre el total del buffer que tenía pendiente, antes de seguir con el siguiente.

Podríamos pensar que alineando el tamaño de los buffers siempre a 10.000 filas conseguiríamos mejor rendimiento, para que DQS no gastase un envío de filas que fuese de menos que su máximo. Sin embargo, para el caso secuencial, esto no es así por cómo Integration Services trata los buffers. El coste de CPU y de memoria que supone crear un nuevo buffer de exactamente 10.000 filas sería mayor que la pérdida de fuerza de trabajo que supone no mandarle a DQS un bloque todo lo grande que puede manejar. Por tanto, en términos de rendimiento es preferible que DQS coja menos filas (ese pico que “sobra” en el buffer) pero que ya están cargadas en memoria en un buffer ya creado.

Podemos verlo en los tiempos totales de ejecución.

Maximizando el tamaño de los buffers aunque no se alinee con los pedazos que DQS puede manejar como máximo:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Limitando el tamaño de los buffers a 10.000 filas exactamente:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Ha tardado 10 minutos más sólo por la penalización derivada de tener que generar más buffers de menos tamaño. En segundos tenemos que 3255 / 2654 = 1,22 –> se ha ejecutado un 22% más lento.

La mejor opción en este caso sería tener buffers de 10.000 * N filas (cuando N es entero y N > 1), para que cogiendo las filas de un mismo buffer, generando los menos posibles, DQS aprovechase cada captura de datos al máximo, pero esto siempre va a depender de los datos con los que tratemos en nuestro flujo y cómo de ancha sea cada fila. Otra razón más para volver a pensar en qué tipos de datos son más adecuados cuánto tamaño ocupan

Flujo paralelo con operador módulo

Para dividir nuestro flujo de datos en N ramas (con aproximadamente el mismo número de filas) y aplicar N componentes DQS Cleansing necesitaremos una clave por la que separar dicho flujo. Para conseguir esto podremos crear la clave nosotros mismos mediante la cláusula ROW_NUMBER() de T-SQL y usar el operador % para dividir el flujo en N ramas.

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Una vez tengamos el identificador podremos usarlo para calcular el módulo y dividir el flujo:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Lo haremos entre 6 porque en nuestro caso tenemos 6 cores asignados a la máquina virtual. Para un rendimiento lo más optimo posible deberíamos alinear el divisor de la operación módulo a los N cores que tengamos (ID % N).

Con un Conditional Split daríamos salida a cada rama

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Y finalmente tendríamos un flujo parecido a este:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Para comprobar que SSIS va a paralelizar este flujo, agregamos el evento PipelineExecutionTrees al log de Integration Services para este flujo de datos

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

y vemos que, efectivamente, Integration Services va a construir 6 sub-paths (cada uno puede ser ejecutado en hilos separados) para cada una de las ramas que salen del Conditional Split.

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Además, durante la ejecución vemos que todos los cores están siendo utilizados:

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

En este caso concreto teníamos 6 paths únicos y uno con 6 sub-paths, lo que hace un total de 12 hilos totales. Para conseguir que todos se puedan ejecutar a la vez, tendremos que modificar la propiedad EngineThreads (que por defecto tiene un valor de 10) del flujo de datos. SSIS podrá iniciar todos los hilos que estima que le son necesarios sólo si esta esta propiedad es mayor que su estimación. Establecerla por encima de esta estimación no tendrá mayor efecto, ya que actúa como cota superior.

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

También tendremos que modificar la propiedad MaxConcurrentExecutables del Control Flow para que el paquete SSIS permita un total de hilos en el paquete superior al que hay por defecto (el número de procesadores lógicos +2, que en nuestro caso serían 6+2 = 8)

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Con esta aproximación tenemos un tiempo de ejecución de

Optimizando el componente DQS Cleansing en SSIS 2012 (parte 1 de 2)

Pasamos de un tiempo de 44 minutos a 15 minutos. Si lo calculamos en segundos, serían 2654 / 941 = 2,82, dentro del rango que se nos daba como estimación desde el grupo de producto de DQS.

A pesar de esta mejora, detectamos que está repartiendo las filas entre los diferentes componentes DQS Cleansing de manera sub-óptima, ya que los sub-paths pueden gestionarse en hilos separados pero no tienen por qué tener buffers propios. De tal manera, SSIS divide cada buffer entre los 6 subpaths y manda N filas a cada uno, infrautilizando la capacidad del componente, pues ya hemos visto que DQS Cleansing puede lidiar con más filas en cada envío (un máximo de 10.000).

En el siguiente post de esta serie veremos dos alternativas más para paralelizar nuestros flujos de datos con componentes DQS Cleansing.

Feliz tuning 🙂

 

0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

Cómo refrescar un dataset de Power BI al finalizar el proceso de ETL

Power BI dispone de ciertas herramientas de administración: APIs administrativas, un SDK .NET y un módulo de PowerShell con cmdlets que permiten a los administradores ir más allá de lo que el portal de Power BI Admin ofrece. Vamos a ver las diferencias entre la API y los cmdlets y después mostraremos cómo usarlos en un caso práctico: resfrescar el dataset tras un evento, en este caso al finalizar la carga del datawarehouse.