Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Get a Descriptive Name for SQL Server Analysis Services Profiler Event Class and Event Subclass


By:   |   Last Updated: 2012-05-02   |   Comments (5)   |   Related Tips: > Analysis Services Performance

Problem

A customer provides an SQL Server Analysis Services trace file to analyze. You save this file into a trace table and execute T-SQL code to analyze the results. Rather than getting descriptive values for the EventClass and EventSubclass data, you get numerical values. How do you resolve these numerical values to meaningful descriptive names?  Check out this tip to learn more.

Solution

Microsoft provides the description for the EventClass and EventSubclass in a tracedefinitionNNN.xml file, where NNN is the current database version. This file is located in the installations MSQL.x\OLAP\bin\Resources\1033 directory. There are several solutions to this issue, both of which require using the trace definition file. One solution is quick and dirty, but not very flexible and can be pretty cumbersome. The other solution is more difficult, but provides a better long-term and reusable approach.

Solution 1 - CASE Expression

This first solution uses a CASE expression to map the numerical value for each EventClass to its corresponding descriptive name. This same approach is also applied to the EventSubclass as demonstrated in the code sample below.

SELECT RowNumber, 
      'EventClass' = 
      CASE 
         WHEN EventClass = 11 THEN 'Query Subcube'
         WHEN EventClass = 81 THEN 'QueryDimension'
       ELSE
         CAST(EventClass AS VARCHAR(10)) 
       END,
       'EventSubclass' = 
       CASE        
         WHEN EventClass = 11 AND EventSubclass = 1  THEN 'Cache data'
         WHEN EventClass = 11 AND EventSubclass = 2  THEN 'Non-cache data'
         WHEN EventClass = 81 AND EventSubclass = 1  THEN 'Cache data'
         WHEN EventClass = 81 AND EventSubclass = 2  THEN 'Non-cache data'
       ELSE
         CAST(EventSubclass AS VARCHAR(10)) 
       END,
       TextData, 
       StartTime, 
       Duration, 
       CAST(CPUTime/1000.0 AS float) AS 'CPUTime(sec)'
FROM dbo.my_as_trace
ORDER BY RowNumber;

This approach is cumbersome, because it requires iteratively searching the trace definition file to find the correct description for the given EventClass and EventSubclass. Care must be taken when searching for both the EventClass and EventSubclass since these values are not guaranteed to be unique within the tracedefinition XML file. As the above example demonstrates, the <EventSubclass> sub elements are unique within a given <EventClass> element, but not unique across <EventClass> elements. This solution is good if the Analysis Services trace contains only a small number of EventClass and EventSubclass.

Solution 2 - Shred the Trace Definition XML

This next solution requires shredding the trace definition XML. Before executing the code block below, you need to make two changes. First, you need to copy and paste the content of the trace definition XML file to the @tracedef variable. Second, you need to escape the single quote by searching and replacing each single quote with two single quote in the XML content. These are:

  • 'YYYY-MM-DD' ''YYYY-MM-DD''
  • 'YYYY-MM-DD HH:MM:SS' ''YYYY-MM-DD HH:MM:SS''
  • object's object''s
Executing the T-SQL code below will create a temporary table, ##tracetable, containing four columns for the event id, event name, event subclass id, and event subclass name.

 DECLARE @tracedef xml
SET @tracedef='<replace with content from tracedefinition xml file>'
SELECT T1.evt.value('./ID[1]', 'int')               AS event_id,
       T1.evt.value('./NAME[1]', 'varchar(max)')    AS event_name, 
       T2.subevt.value('./ID[1]', 'int')            AS subevent_id, 
       T2.subevt.value('./NAME[1]', 'varchar(max)') AS subevent_name
INTO ##tracetable
FROM @tracedef.nodes(
    '/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT') AS T1(evt)
  CROSS APPLY T1.evt.nodes(
    './EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS') 
    AS T2(subevt)
GO

Finally, the query to analyze the Analysis Services trace data in solution 1 is modified to join to this temporary table to retrieve the event name and event subclass name.

SELECT 
    RowNumber, 
    t2.event_name    AS EventClass, 
    t3.subevent_name AS EventSubclass,
    TextData, 
    StartTime, 
    Duration, 
    CAST(CPUTime/1000.0 AS float) AS 'CPUTime(sec)'
FROM dbo.my_as_trace t1
 INNER JOIN 
    (select distinct event_id as event_id, event_name as event_name from ##tracetable) t2
    ON t1.EventClass = t2.event_id
 INNER JOIN ##tracetable t3
    ON (t1.EventClass = t3.event_id and t1.EventSubclass = t3.subevent_id)
ORDER BY RowNumber;
Next Steps


Last Updated: 2012-05-02


next webcast button


next tip button



About the author
MSSQLTips author Peter Tran Peter Tran is a Senior Performance Tech Lead @ PROS Revenue Management;(NYSE: PRO), where he focuses on server-side (Java, JDBC, database) performance on SQL Server.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 13, 2013 - 4:25:39 PM - Peter Tran Back To Top

Thanks for the update on the query and spelling issue.


Wednesday, February 13, 2013 - 1:51:15 PM - Scott Cameron Back To Top

MS has fixed the spelling error, so no more need to do the find/replace


Wednesday, February 13, 2013 - 1:50:04 PM - Scott Cameron Back To Top

The SQL code to load ##tracetable excludes events that don't have a corresponding subevent, e.g. Event ID = 17 Error or Event ID = 17 Query Cube Begin.

The code to join the trace events to the event names drops trace events where the subevent is null.

This SQL will load all events:

;
with Events as
 (SELECT
  EventId = T1.evt.value('./ID[1]', 'int')
  ,EventName = T1.evt.value('./NAME[1]', 'varchar(200)')
  ,EventDescription = T1.evt.value('./DESCRIPTION[1]', 'varchar(300)')
 FROM
  @tracedef.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT') AS T1(evt)
 )
,SubEvents as
 (SELECT
  EventId = T1.evt.value('./ID[1]', 'int')
  ,EventName = T1.evt.value('./NAME[1]', 'varchar(200)')
  ,EventDescription = T1.evt.value('./DESCRIPTION[1]', 'varchar(300)')
  ,SubEventId = T2.subevt.value('./ID[1]', 'int')
  ,SubEventName = T2.subevt.value('./NAME[1]', 'varchar(max)')
  ,SubEventDescription = T2.subevt.value('./DESCRIPTION[1]', 'varchar(max)')
 FROM
  @tracedef.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT') AS T1(evt)
  CROSS APPLY T1.evt.nodes('./EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS') AS T2(subevt)
 )
select
 e.EventId
 ,e.EventName
 ,e.EventDescription
 ,se.SubEventId
 ,se.SubEventName
 ,se.SubEventDescription
into
 dbo.SSASEventSubEvent
from
 Events e
 left outer join SubEvents se
  on e.EventId = se.EventId
order by
 e.EventId
 ,se.SubEventId

This SQL will select all trace records:

select
 *
from
 dbo.SSASTrace t
 left outer join dbo.SSASEventSubEvent e
  on t.EventClass = e.EventId
  and coalesce(t.EventSubclass, -1) = coalesce(e.SubEventId,-1)

 


Friday, October 12, 2012 - 3:11:00 PM - Peter Tran Back To Top

Thanks! Wow...that's crazy they have that spelling mistake. How hard is it to copy the file over?  :) Thank you for pointing this out.


Wednesday, October 10, 2012 - 10:42:27 AM - Ian Clarke Back To Top

This is brilliant and a great time-saver. Thanks a million! I did notice an issue with SSAS 2012. There is a spelling mistake in the trace definition file so in the query to extract the classes you need to change './EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS' to'./EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMSUBCLASS'.

Ian


Learn more about SQL Server tools