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

By: Peter Tran   |   Comments (5)   |   Related: > 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

 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

• If your Analysis Services trace contains only a small number of trace events and you don't anticipate analyzing a lot of trace data, then use solution 1.
• Solution 2 is a better long term approach. It only has to be done once and the table containing the trace definition names can be stored in a DBA database to allow all databases to reference it.
• Check out these related MSSQLTips.com tips:
  • Creating a Trace Template in SQL Server Profiler
  • Replacing OPENXML with the XML nodes() Function in SQL Server 2005

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips