08 mayo 2009

Analysis Services Dynamic Management Views (DMV)

Hasta ahora el poder monitorear nuestro servidor de Analysis Services había sido todo un reto. No todos encontramos muy fácil el usar los Contadores de Rendimiento (Performance Counters) u obtener información dentro de los arhivos de log del servidor. Por otro lado a veces el comprar una herramienta de terceros para lograr este fin puede no ser muy costo/efectivo cuando lo que necesito en la mayoria de los casos es ver cuáles son los procesos (consultas) ejecutándose actualmente y, para en algún caso, eliminar los procesos que me estén bloqueando el servidor o tomando mucho tiempo en obtener un resultado.

Para nuestro beneficio Analysis Services 2008 viene al rescate introduciendo los Dynamic Management Views (DMV) ó Vistas de Administración Dinámica, un concepto idéntico al que existe en SQL Server desde la versión 2005 pero que esta vez no está limitada al motor relacional sino a nuestro servidor OLAP.

Si no han tenido la oportunidad de usar los DMV de SQL Server 2005, su uso es bastante sencillo. Los DMV están representados por una serie de vistas (views) predefinidas a nivel de la instancia a las cuales les podemos aplicar un SELECT * FROM <vista>. Dichas vistas están conectadas directamente al motor (de SQL o SSAS) para brindarnos información dinámica acerca de las conexiones existentes, las sesiones abiertas, las consultas ejecutadas y su estado, los objetos utilizados y muchas otras estadísticas interesantes que aplican al instante en el cual se ejecuta la consulta (por esto lo de “dinámica”).

De acuerdo a la documentación de Analysis Services 2008, los DMV nos ayudan a:

  • Monitorear los recursos de manera optimizada
  • Ejecutar un mejor análisis de los recursos
  • Utilizar la información expuesta por un recurso del servidor
  • Identificar quién ejecuta qué consulta y por cuánto tiempo
  • Optimizar la carga y uso de los recursos

Importante: Para ejecutar las consultas sobre los DMV pueden hacerlo al menos de 2 formas:

1. Abriendo un nuevo query de tipo MDX dentro del Management Studio.
2. Creando un Linked Server en SQL Server que apunte a su instancia de Analysis Services. A partir de este punto pueden ejecutar las consultas usando un OPENQUERY dentro de Transact-SQL.

La opción # 1 es la más sencilla para hacer consultas Ad-Hoc.
La opción # 2 es la apropiada si queremos llamar los DMV desde nuestros propios reportes o aplicaciones.

Los DMV que ayudan al monitoreo del servidor existen dentro del schema “$SYSTEM_” y tienen como prefijo la palabra “DISCOVER_” en el nombre de la vista. Alguno de los principales DMV para monitoreo son:

  • DISCOVER_CONNECTIONS
  • DISCOVER_SESSIONS
  • DISCOVER_COMMANDS
  • DISCOVER_COMMAND_OBJECTS
  • DISCOVER_OBJECT_ACTIVITY
  • DISCOVER_OBJECT_MEMORY_USAGE
  • DISCOVER_PERFORMANCE_COUNTERS

En total existen 27 tablas DISCOVER_ en esta versión de SSAS. Si quieren obtener un listado de todas las tablas pueden usar el query que se muestra a continuación (lo pueden correr como un MDX query en el Management Studio).

SELECT TABLE_NAME
FROM $system.dbschema_tables
WHERE TABLE_SCHEMA = '$SYSTEM'
AND LEFT(TABLE_NAME,8) = 'DISCOVER'
ORDER BY table_name





Pueden referirse a la documentación de SQL para obtener la descripción de cada DMV y la información que contienen: http://msdn.microsoft.com/en-us/library/bb934105.aspx.



Existen algunos DMV que tienen algunas restricciones para ser ejecutados. Por ejemplo DISCOVER_PERFORMANCE_COUNTERS requiere que se indique cuál es el contador que se quiere consultar. Aquí el ejemplo:




SELECT *
FROM SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
PERF_COUNTER_NAME = '\MSOLAP$SQL2K8:Cache\Current KB')





La palabra reservada SYSTEMRESTRICTSCHEMA requiere 2 parámetros: el nombre del DMV y el filtro que queremos aplicar. En este caso le estamos pasando como filtro el nombre del Performance Counter que queremos obtener.



Si alguna vez tienen un usuario creativo que realizó un query al cubo para traer el catálogo completo de productos con más de 1 millón de productos y ya lleva más de 20 minutos esperando respuesta, probablemente ustedes quieran cancelar ese proceso y poder dejar que otros usuarios de mayor jerarquía como su gerente general pueda ver el resumen de las ventas. Una forma sencilla aquí sería ejecutar un query para verificar las sesiones en ejecución buscando las sesiones activas y el nombre del usuario que realizó el query. Algo como:




SELECT * FROM $SYSTEM.DISCOVER_SESSIONS
WHERE SESSION_USER_NAME = 'UsuarioDePocaImportancia'
AND SESSION_STATUS = 1





Una vez que identifiquen el query que causa el problema en el conjunto de resultados, basta con obtener el SESSION_SPID y ejecutar un comando de XMLA para “matar” el proceso:




<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SPID>42016</SPID>
</Cancel>





Como les comenté anteriormente, si necesitan incluir los resultados de los DMV en un reporte o una aplicación, lo que primero deben hacer es crear un Linked Server en su instancia de SQL Server. Esto debido a que por ejemplo en el caso del "Query Editor de Visual Studio, éste no puede interpretar los resultados del DMV si sometemos el query como MDX. El LinkedServer nos permitirá realizar las consultas desde SQL usando Transact SQL.



En mi caso he creado un LinkedServer llamado SSAS2008 que apunta a mi instancia de Analysis Services 2008 llamada SQL2K8. El LinkedServer requiere además que se le especifique una base de datos destino, en este caso estoy usando Adventure Works aunque para el caso de los DMV, esto es irrelevante:



image



Ahora ya pueden someter consultas usando OPENQUERY:




-- Ejemplo 1
SELECT * FROM OPENQUERY(SSAS2008,'SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS')
GO
-- Ejemplo 2
SELECT * FROM OPENQUERY(SSAS2008,'SELECT * FROM $SYSTEM.Discover_Object_Memory_Usage')
ORDER BY OBJECT_MEMORY_SHRINKABLE DESC
GO





Finalmente, he construido un par de ejemplos en Reporting Services 2008 que usan los DMV Connections y Sessions. Esto puede ser un buen inicio para quienes quieran crear su versión personalizada para el monitoreo de SSAS por mucho, mucho menos dinero. Los reportes usan un Shared Data Source, recuerden cambiar la información de su servidor aquí. También deben de cambiar el nombre de su LinkedServer dentro de cada DataSet de los reportes. El screenshot a continuación muestra el reporte con información de las sesiones existentes incluyendo filtros por UserName, ConnectionID y Status de la sesión:



image



Pueden descargar el proyecto de Reporting Services 2008 desde el siguiente link:






Alan.

4 comentarios:

Unknown dijo...
Este comentario ha sido eliminado por el autor.
Edwin dijo...

Creo que esto lo usaré muy a menudo!!!! Gracias por el tip!! Espero pasarme a 2008 muy pronto...

Carlos Bercero dijo...

Exceleeeennnnte.

Esto tiene una utilidad tremenda a la hora de optimziar los cubos.

Unknown dijo...

data analysis reporting services

SQIAR (http://www.sqiar.com/solutions/technology/tableau) is a leading Business Intelligence company.Sqiar Consultants Provide Tableau Software Consultancy To small and Medium size of organization.