Lately, I’ve been spending quite a bit of time troubleshooting some performance issues related to PowerView reports running against a SQL Server Analysis Services 2012 Tabular data source. One of the first steps in the troubleshooting process involved turning up a SQL Profiler trace on the SSAS Tabular instance and having the users reproduce the issue by opening up the problematic PowerView reports and doing whatever it was that resulted in the slow response times. Once the users confirm that they’ve reproduced the issue, the trace can be stopped and saved to a SQL Server table for investigation.
Note: it’s also a good idea to capture a Perfmon trace at the same time the SQL Profiler trace is running to provide some perspective in terms of system resource utilization.
There’s just one small problem
While reviewing the trace in SQL Profiler the EventClass and EventSubClass columns are provided as descriptive text:
However, when the trace is saved to a table in SQL Server, these descriptive columns are replaced by numeric codes:
This is no big deal when you’re reviewing a profiler trace of a SQL Server engine because there are system tables available to look up the descriptive values that correspond to the codes.
Note: these system tables are a deprecated feature in SQL Server 2012 and are being replaced by Extended Events.
However, when the profiler trace is of a SSAS instance, those same system tables are of no help at all. Instead, the best solution I’ve found was to simply create your own lookup tables for EventClass and EventSubClass codes for SSAS profiler traces. Dan Hardan provided a nice script for creating and populating these lookup tables in an old blog post and this is exactly what I’ve used in the past. There’s just one more little problem…the script Dan provided in 2008 doesn’t include the new tabular-based events in SSAS 2012.
Solution for SSAS 2012
In Dan’s post, he mentions that the translation of EventClass and EventSubClass ids into text names/descriptions is in an XML file (tracedefinition.xml) stored in the SSAS installation directory:
This file contains details of all the new tabular-based events in SSAS 2012 such as:
- DAX Query Plan
- VertiPaq SE Query Begin
- VertiPaq SE Query Begin
- VertiPaq SE Query Cache Match
- VertiPaq SE Query End
…just to name a few.
And here’s a script I hacked together that will read the contents of this file into an XML variable, shred it, and and store the contents in 2 tables:
USE tempdb;
GO
/*
Based on information from:
http://blogs.msdn.com/b/danhardan/archive/2008/09/10/sql-profiler-eventclass-eventsubclass-column-descriptions.aspx
*/
IF OBJECT_ID('dbo.ProfilerEventClass_SSAS2012', 'U') IS NOT NULL
DROP TABLE dbo.ProfilerEventClass_SSAS2012;
CREATE TABLE dbo.ProfilerEventClass_SSAS2012
(
EventClassId INT NOT NULL,
EventClassName NVARCHAR(50) NULL,
EventClassDescription NVARCHAR(500) NULL,
CONSTRAINT PK_dbo_ProfilerEventClass_SSAS2012
PRIMARY KEY CLUSTERED (EventClassId)
);
IF OBJECT_ID('dbo.ProfilerEventSubClass_SSAS2012', 'U') IS NOT NULL
DROP TABLE dbo.ProfilerEventSubClass_SSAS2012;
CREATE TABLE dbo.ProfilerEventSubClass_SSAS2012
(
EventClassId INT NOT NULL,
EventSubClassId INT NOT NULL,
EventSubClassName NVARCHAR(50) NULL,
CONSTRAINT PK_dbo_ProfilerEventSubClass_SSAS2012
PRIMARY KEY CLUSTERED
(
EventClassId,
EventSubClassId
)
);
/*
Altered code from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130743
to read in file contents of Trace Definition
*/
DECLARE @fnTraceDefinitinionFileName NVARCHAR(300);
/* Note: this file needs to be in a directory that the SQL Server service account has read permissions */
SET @fnTraceDefinitinionFileName
= N'C:\Program Files\Microsoft SQL Server\MSAS14.TAB2017\OLAP\bin\Resources\1033\tracedefinition140.xml';
-- Initialize command string, return code and file content
DECLARE @cmd NVARCHAR(MAX),
@rc INT,
@xmlSSAS110_TraceDefinition XML;
BEGIN;
-- Make sure accents are preserved if encoding is missing by adding encoding information UTF-8
SET @cmd
= 'SELECT @Content = BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@fnTraceDefinitinionFileName,'''') + ', SINGLE_CLOB) AS f';
-- Read the file and get the content in a XML variable
EXEC @rc = sp_executesql @cmd,
N'@Content XML OUTPUT',
@Content = @xmlSSAS110_TraceDefinition OUTPUT;
END;
/* shred the XML variable and insert values into tables created at beginning of script */
INSERT INTO dbo.ProfilerEventClass_SSAS2012
(
EventClassId,
EventClassName,
EventClassDescription
)
SELECT EventClassId = t.c.value('./ID[1]', 'INT'),
EventClassName = t.c.value('./NAME[1]', 'VARCHAR(50)'),
EventClassDescription = t.c.value('./DESCRIPTION[1]', 'VARCHAR(500)')
FROM @xmlSSAS110_TraceDefinition.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT') AS t(c);
INSERT INTO dbo.ProfilerEventSubClass_SSAS2012
(
EventClassId,
EventSubClassId,
EventSubClassName
)
SELECT EventClassId = t.c.value('../../../../ID[1]', 'INT'),
EventSubClassId = t.c.value('./ID[1]', 'INT'),
EventSubClassName = t.c.value('./NAME[1]', 'VARCHAR(50)')
FROM @xmlSSAS110_TraceDefinition.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT/EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS') AS t(c);
SELECT *
FROM dbo.ProfilerEventClass_SSAS2012;
SELECT *
FROM dbo.ProfilerEventSubClass_SSAS2012;
Now, we can review a SSAS profiler trace table and have the EventClass and EventSubClass names to help us with our analysis.
Hope this helps!
byoBI.com 