Existen algunos tips que permiten mejorar el rendimiento y reducir el tiempo de procesado en nuestras bases de datos de Analysis Services. Hay unas cuantas páginas dedicadas a ello y resúmenes por ahí que marcan unas líneas a seguir (y otras que no!). En este post nombro un ejemplo y de que manera podemos medir esa posible mejora de forma objetiva basándonos en el uso de los contadores de rendimiento del sistema y de Sql Server Profiler.
Ejemplo de mejora: Evitar tablas de hechos con demasiados campos.
Imaginaros la tabla de hechos con las cabeceras de venta. Si esta tabla tiene 100 campos y solo necesitamos 10, el número de registros contenidos en cada página de 8k de datos será mucho menor que si solo se almacenaran los campos sensibles. ¿Como podemos mejorar este aspecto?
- Partición vertical de la tabla en dos diferentes. Si tenemos la posibilidad estaría bien poder hacer una partición vertical y aislar esos 10 campos que necesitamos para dejar el resto en una tabla adicional. Sin tener en cuenta el tamaño de los campos, podríamos reducir el número de páginas de datos a recorrer en un 10%. Aunque esto es lo ideal, no siempre es posible.
- Tabla derivada. En la linea de lo anterior, podríamos mantener una tabla derivada sobre la que procesar los hechos.
- Crear una Vista indizada. Con la vista hacemos la partición pero restringimos de alguna manera la tabla original e incluso podríamos ralentizar un poco la inserción de datos si es una tabla transaccional muy concurrida. No es mala opción pero hay que medir las ventajas y los inconvenientes.
Medir el procesamiento con los contadores de rendimiento de Windows Server
La idea es trabajar sobre una linea base, una foto inicial de como está todo y después de cada cambio realizar otra monitorización para comparar las diferencias y poder medir bien que hemos cambiado y donde está la mejora o el empeoramiento. Al igual que con el motor de base de datos de Microsoft SQL Server, para monitorizar el procesado de cubos podemos usar el monitor de rendimiento del sistema para consultar los contadores de rendimiento propios de Analysis Services. En este otro post explico como se hace para la base de datos. Para la parte de Analysis Services, algunos contadores que podemos usar los siguientes:
- MSOLAP: Processing
Rows read/sec:
Registros que se leen por segundo en el procesado.
- MSOLAP: Proc Aggregations
Temp File Bytes Writes/sec: Ratio por segundo de escrituras en los ficheros temporales. Esto ocurre cuando se excede el límite de memoria. Cuanto más tienda a cero mejor.
Rows created/Sec : Ratio por segundo de creación por segundo. Cuanto más elevado mejor.
Current Partitions : Particiones procesadas actualmente. No sirve más que para controlar que estamos procesando y que los contadores no estén "contaminados" (por ejemplo si es mayor que 1 y no queremos procesado paralelo).
- MSOLAP: Threads
Processing pool idle threads : Puede variar según la versión, en la 2012 se separan los hilos de procesado de los de E/S. Sirve para monitorizar el paralelismo de tareas.
Processing pool job queue length : Puede variar según la versión, en la 2012 se separan los hilos de procesado de los de E/S. Sirve para monitorizar el paralelismo de tareas.
Processing pool busy threads : Puede variar según la versión, en la 2012 se separan los hilos de procesado de los de E/S. Sirve para monitorizar el paralelismo de tareas.
- MSSQL: Memory Manager
Total Server Memory : Total de memoria que está usando actualmente el motor relacional de la base de datos.
Target Server Memory : Total de memoria CONFIGURADO que debería usar el motor de base datos. Si el contador anterior es inferior a este hay algún problema y alguien se está comiendo memoria que teóricamente hemos asignado a la base de datos.
- Logical Disk
Avg. Disk sec/Transfer – All Instances - Processor:
% Processor Time – Total : % CPU consumida actualmente.
- System:
Context Switches / sec : Cambios de tarea en la cola de la cpu. Un valor bajo que tiende a 0 puede significar que algo está monopolizando el 100% de la cpu.
Controlar el uso del motor de base de datos relacional
Como complemento a lo que comento en el apartado anterior, también deberíamos analizar como van las consultas SQL que tiramos sobre la base de datos relacional. Podemos complementar lo anterior con la captura de actividad desde Sql Server Profiler. Como plantilla podemos usar la de Tunning y le añadimos estos dos eventos:
- Performance/Showplan XML Statistics Profile
- TSQL/SQL:BatchCompleted
De ellos necesitamos las columnas TextData, Reads, DatabaseName, SPID y Duration. Con ellos podemos detectar cuales son las consultas que más se demoran, podemos ver su plan de ejecución y mirar los índices que pueden mejorar los tiempos en la recuperación de los datos.
En cuanto a la captura con SQL Server Profiler, es bueno saber que podemos filtrar eventos para solo mostrar lo imprescindible. En este otro post explico como funciona la captura y como filtrar para afinar la captura.
En conclusión...
... creo que conocer la manera de monitorizar el procesado de nuestros cubos nos permite ver que podemos mejorar, ya sea algo del diseño o maneras de hacer las cosas. Como comento arriba para poder empezar deberíamos comenzar con una captura inicial y tras cada posible mejora deberíamos volver a monitorizar para cuantificar la mejora. Pienso que la mayoría de contadores pueden ser evolutivos (los comparamos con anteriores) y creo que no hay un rango definido. Simplemente sabiendo lo que miden y con un poco de lógica sabremos cuando va a mejor o a peor (el contador). El único problema que podemos tener en todo esto es que podamos hacerlo de forma proactiva, el lugar de forma reactiva cuando se nos solape con otros procesos... y entonces tengamos un problema.