Hay diversas formas de extraer información de Analysis Services para monitorizar y analizar que está sucediendo en nuestra instancia cuando queremos optimizar algún proceso o resolver problemas: DMVs, XEvents y nuestro querido Profiler. En esta entrada vamos a centrarnos en este último, SQL Profiler.
Como bien sabéis, a la hora de configurar la traza seleccionamos los eventos y columnas que deseamos capturar y podemos volcar esta información a un fichero o una tabla SQL.
Imaginaros un escenario en el que tenemos que registrar la actividad de nuestra instancia a través de SQL Profiler y guardar la información en una tabla para luego montar un informe a través de Excel + PowerPivot (o reporting, o lo que sea)
La información que se guarda en tabla tiene una estructura similar a la siguiente:
Fijaros que las columnas EventClass y EventSubClass guardan un identificador. Y llegamos a la cuestión que queremos abordar en esta entrada del blog: ¿de dónde sacamos la relación de identificador/clase de evento?
Si hacéis una búsqueda por internet veréis que hay algún artículo, algo vetusto, facilitando una relación de todos los ID y clase de evento, e ID y sub clase de evento:
Lo que queremos es que seáis capaces de generaros vuestra propia tabla de referencia para eventos y sub eventos, utilizando la versión adecuada del motor de SQL ya que los eventos difieren entre versiones, normalmente se añaden nuevos eventos a medida que se aumenta la versión de SQL, y otros caen en la obsolescencia.
Para hacerlo solo necesitamos el xml de definición de traza que hay para cada versión, y un motor de SQL capaz de trabajar con datos XML.
Localiza el XML con la definición de eventos correspondiente a la traza que has capturado. La ruta habitual es %ProgramFiles%Microsoft SQL ServerMSAS11.MSSQLSERVEROLAPbinResources1033
A tener en cuenta que la última carpeta en la ruta es la que identifica el idioma. El LocId 1033 corresponde al idioma inglés.
1) El primer paso es crearnos una tabla en la que incorporaremos el contenido XML de ese fichero de definición:
create database TraceDefinitions go use traceDefinitions go Create table TraceDefinitions ( RowId int not null identity(1,1) ,XmlData XML NOT NULL ,ServerVersion int not null )
2) Una vez tenemos la estructura, podemos incorporar el contenido del fichero con la siguiente sentencia TSQL:
INSERT INTO TraceDefinitions(XmlData, ServerVersion) SELECT * , 120 FROM OPENROWSET( BULK 'C:Program FilesMicrosoft SQL ServerMSAS12.MSSQLSERVEROLAPbinResources1033tracedefinition120.xml', SINGLE_BLOB) AS ImportSource
3) Finalmente, construimos una consulta utilizando XQuery para extraer los valores contenidos dentro de nuestro campo de tipo XML
SELECT
pref.value('(TYPE/text())[1]', 'varchar(50)') as EventType,
pref.value('(NAME/text())[1]', 'varchar(50)') as EventTypeName,
pref.value('(DESCRIPTION/text())[1]', 'varchar(150)') as EventTypeDescription,
sref.value('(ID/text())[1]', 'varchar(4)') as EventClassID,
sref.value('(NAME/text())[1]', 'varchar(255)') as EventClass,
sref.value('(DESCRIPTION/text())[1]', 'varchar(255)') as EventClassDescription,
nref.value('(ID/text())[1]','varchar(4)') as EventSubClassID,
nref.value('(NAME/text())[1]','varchar(255)') as EventSubClass
FROM
[TraceDefinitions] CROSS APPLY
XmlData.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY') AS EventosCat(pref)
outer APPLY pref.nodes('EVENTLIST/EVENT') AS Eventos(sref)
outer APPLY sref.nodes('EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS') SubEventos(nref)
WHERE ServerVersion=120
A partir de aquí, podemos incorporar esta consulta como tabla de referencia, para vincular los eventos que hemos capturado en la traza e identificar fácilmente de qué clase de evento se trata.
Espero que os sea de utilidad
