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
##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
- 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:
Biography
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. He is currently working on his Microsoft SQL Server 2008 Master Certification (MCM). When he’s not busy working or studying, his hobby is photography.
SQL Server Interests
Peter enjoys helping developers understand how to use SQL Server efficiently. In his experience, most performance issues are due to database ignorance.