En este nuevo post vamos a ver como monitorizar el rendimiento de nuestros paquetes de SSIS almacenados en SSISDB usando Power Bi como herramienta de visualización.
Con la aparición de SQL Server 2012 Microsoft cambio la forma que teníamos de organizar los paquetes .dtsx y centralizo el almacenamiento de estos en el Catalogo de Integration Services. El objetivo del blog no es el cambio en la arquitectura pero si estas interesado puedes leer mas aquí.
Vale, como decía Jack el destripador, vamos por partes. ¿Qué necesitamos?.
- Generar un proceso de carga que lea los datos de las tablas que almacenan información de la ejecución de nuestros paquetes. Estas tablas se encuentran en la base de datos SSISDB.
- Volcar los datos en una nueva bbdd donde iremos almacenando la información y llevaremos a cabo una pequeña fase de transformación mediante el uso de vistas.
- Power BI. Una excelente herramienta que nos permitirá explotar relacionar la información ya tratada en las vistas y crear nuestros reports para de esta forma poder analizar el rendimiento de nuestros paquetes, pudiendo identificar posibles cuellos de botella o mejorar el proceso de carga, como una mayor paralelización de los mismos.
Generar proceso de carga
Para ello hemos usado SSIS y desarrollado un paquete que nos permite llevar a cabo esta tarea. El paquete luce así:
Transformación del dato
Una vez tengamos la información recogida en nuestra base de datos procederemos a modelar el dato y dejar el resultado del mismo en unas vistas. Nosotros hemos generado las siguientes que junto con las tablas nos servirán de base para crear nuestros informes:
Visualización en Power BI
En este punto ya tenemos nuestro dato preparado así que podemos abrir un power bi, traernos nuestras tablas y vistas y comenzar a relacionarlas:
Una vez terminadas de relacionar nuestras tablas ya podemos empezar a desarrollar nuestros informes. Os mostramos el aspecto de nuestro archivo
Esta herramienta nos ayudo a encontrar un problema de tiempos en la fase de extracción. Vimos que el paquete orquestador (paquete maestro que se encarga de sincronizar los orígenes con nuestro Staging) estaba tardando mucho mas de lo que debería.
Lanzando el proceso de monitorización y analizando después en power bi vimos los tiempos en los que estaba corriendo el orquestador, muy superiores a lo estimado.
Entrando en detalle de los paquetes que se estaban ejecutando en este orquestador, vimos que uno de ellos se llevaba mas del 95 % del tiempo total de ejecución.
El paquete en cuestión hace un truncado de la tabla destino para posteriormente hacer una inserción de los registros. Al ejecutarlo en Visual Studio comprobamos que los primeros 500.000 registros los insertaba muy rápido pero luego el rendimiento caía estrepitosamente.
Analizando tabla y paquete vimos que la tabla tenia una PK por lo que para cada lote que insertaba tenia que ordenar antes la tabla para verificar que los registros no estuvieran y una vez comprobado escribirlos en disco.
Los primeros registros los insertaba muy rápido porque no había muchos datos en la tabla por lo que la ordenación era rápida, pero conforme aumentaba el numero de registros insertados el rendimiento empeoraba.
Para solucionarlo decidimos darle los datos ordenados a SSIS y establecer tanto en origen como en destino que el dato venia ordenado por la PK.
Una vez hecho esto, el cambio en la ejecución se hizo notar…:)
La monitorización de SSISB es un factor importante a tener en cuenta en todos nuestros proyectos ya que nos permite obtener información del rendimiento de nuestros paquetes y nos ayuda a identificar aquellos procesos que no están funcionando como esperamos.
