…brew your own Business Intelligence

Investigating xEvents in SSAS 2016 CTP2

Last week I finally found some time to explore SQL Server 2016 CTP2 and considering I’ve spent quite a bit of time over the past 12 months performance tuning SSAS queries, I figured why not start with extended events. Plus, you can usually infer quite a bit from looking at the tracing capabilities Winking smile

Note: everything in this post is relevant only to SQL Server 2016 CTP2. Changes in the product can (and likely will) occur before the final product is released. There is also a good bit of speculation in this post.

Discovering New Trace Events

The details (e.g. ID, name, description) of all traceable “events” can be found in a trace-definition (xml) file on the server…which in this case, since we are referring to SQL Server 2016 (aka version 13.0) is appropriately named tracedefinition130.xml.

Note: For an installation of SQL Server 2012 or 2014, the file would be named tracedefinition110.xml and tracedefinition120.xml respectively.

image

This xml file can be imported into a variable, flattened out using xml queries, and inserted into tables for easier exploration. These tables will also come in very handy when you need to analyze xEvent traces and present your findings.

Note: Another option is to use PowerQuery…which is probably better suited for those who want to keep their sanity and therefore avoid hacking together XML queries (though arguably these are fairly simple xml queries compared to a lot of what is seen in the field)

To save you some time, I’ve included the script below which can be used to shred and import the information into tables on your dev/lab system. It works (I think) for any version of Analysis Services – as you’ll see shortly. All you need to do is change the path as needed (as seen below it is set for the default installation path).

USE tempdb;
GO

/* ============================================================
    Drop/Create Tables 
   ============================================================
*/
IF OBJECT_ID('dbo.ProfilerEventClass_SSAS2016','U') IS NOT NULL
    DROP TABLE dbo.ProfilerEventClass_SSAS2016;
CREATE TABLE dbo.ProfilerEventClass_SSAS2016 (
     EventClassId INT NOT NULL
    ,EventClassName NVARCHAR(50) NULL
    ,EventClassDescription NVARCHAR(500) NULL
    ,CONSTRAINT PK_dbo_ProfilerEventClass_SSAS2016 PRIMARY KEY CLUSTERED (EventClassId)
)
;
IF OBJECT_ID('dbo.ProfilerEventSubClass_SSAS2016','U') IS NOT NULL
    DROP TABLE dbo.ProfilerEventSubClass_SSAS2016;
CREATE TABLE dbo.ProfilerEventSubClass_SSAS2016 (
     EventClassId INT NOT NULL
    ,EventSubClassId INT NOT NULL
    ,EventSubClassName NVARCHAR(50) NULL
    ,CONSTRAINT PK_dbo_ProfilerEventSubClass_SSAS2016 PRIMARY KEY CLUSTERED (EventClassId, EventSubClassId)
)
;

/* ============================================================
    Read Contents of TraceDefinition file into Variable  
   ============================================================
*/
DECLARE @cmd NVARCHAR(MAX);
DECLARE    @rc INT;
DECLARE @xmlSSAS130_TraceDefinition XML;
DECLARE @newline NVARCHAR(25) = CHAR(13) + CHAR(10);
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\MSAS13.ASMD\OLAP\bin\Resources\1033\tracedefinition130.xml';

SET @cmd = N'';
SET @cmd = @cmd + N'SELECT @Content = BulkColumn ' + @newline;
SET @cmd = @cmd + N'FROM OPENROWSET(BULK ' + QUOTENAME(@fnTraceDefinitinionFileName, '''') + ', SINGLE_CLOB) AS f' + @newline;
SET @cmd = @cmd + N';';
PRINT @cmd

EXEC @rc = sp_executesql @cmd
    ,N'@Content XML OUTPUT'
    ,@Content = @xmlSSAS130_TraceDefinition OUTPUT
;

/* ============================================================
    shred the XML variable and insert values into tables 
    created at beginning of script
   ============================================================
*/
INSERT INTO dbo.ProfilerEventClass_SSAS2016 (
             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    @xmlSSAS130_TraceDefinition.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT') AS t(c)
    ;

INSERT INTO dbo.ProfilerEventSubClass_SSAS2016 (
             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    @xmlSSAS130_TraceDefinition.nodes('/TRACEDEFINITION/EVENTCATEGORYLIST/EVENTCATEGORY/EVENTLIST/EVENT/EVENTCOLUMNLIST/EVENTCOLUMN/EVENTCOLUMNSUBCLASSLIST/EVENTCOLUMNSUBCLASS') AS t(c)
    ;

SELECT * FROM dbo.ProfilerEventClass_SSAS2016
SELECT * FROM dbo.ProfilerEventSubClass_SSAS2016

The result of running this script is 2 tables that can be easily joined together in a query to see all traceable events…

SELECT     pec.EventClassName
        ,pec.EventClassDescription
        ,pec.EventClassId
        ,pesc.EventSubClassName
        ,pesc.EventSubClassId
FROM    tempdb.dbo.ProfilerEventClass_SSAS2016 pec
        LEFT OUTER JOIN tempdb.dbo.ProfilerEventSubClass_SSAS2016 pesc
            ON    pesc.EventClassId = pec.EventClassId
ORDER BY 1,4

Note: the LEFT OUTER JOIN is used because there are several Event Classes without any corresponding Event SubClasses. This was true for previous versions as well as 2016 CTP2.

Below is a screenshot of the tables on my lab system where I’ve captured the contents of trace definition files from SQL Server 2012, 2014, and 2016 CTP2.

image

The query below returns 125 rows showing all the differences between the shredded contents of the xml tracedefinition files from SQL Server 2014 and 2016 CTP2. However, if you look closely, 4 events were renamed and 2 events were removed (though, technically, they were broken down into a more specific subset of events)…which results in 119 *new* events.

WITH 
    CTE_2016 AS (
        SELECT     pec.EventClassName
                ,pec.EventClassDescription
                ,pec.EventClassId
                ,EventSubClassName = ISNULL(pesc.EventSubClassName,'none')
                ,EventSubClassId = ISNULL(pesc.EventSubClassId,-1)
        FROM    tempdb.dbo.ProfilerEventClass_SSAS2016 pec
                LEFT OUTER JOIN tempdb.dbo.ProfilerEventSubClass_SSAS2016 pesc
                    ON    pesc.EventClassId = pec.EventClassId
    )
    ,CTE_2014 AS (
        SELECT     pec.EventClassName
                ,pec.EventClassDescription
                ,pec.EventClassId
                ,EventSubClassName = ISNULL(pesc.EventSubClassName,'none')
                ,EventSubClassId = ISNULL(pesc.EventSubClassId,-1)
        FROM    tempdb.dbo.ProfilerEventClass_SSAS2014 pec
                LEFT OUTER JOIN tempdb.dbo.ProfilerEventSubClass_SSAS2014 pesc
                    ON    pesc.EventClassId = pec.EventClassId
    )
SELECT    *
FROM    CTE_2016 c16
        FULL OUTER JOIN CTE_2014 c14
            ON    c14.EventClassName = c16.EventClassName AND
                c14.EventSubClassName = c16.EventSubClassName
WHERE    c16.eventclassname is null OR 
        c14.eventclassname is null
ORDER BY COALESCE(c16.EventClassName,c14.EventClassName)
        ,COALESCE(c16.EventSubClassName,c14.EventSubClassName)

Note: running a similar query will confirm that nothing changed between 2012/2014.

I’ve put together the following spreadsheet to help you review the new, renamed, removed events (download TraceableEvents2016vs2014.xlsx). The first column (red) contains the status (existing, new, renamed, removed) of the event. The blue columns represent SQL2016. The purple columns represent SQL2014.

image

It should be no surprise by now that most of the new events are related to Tabular…

image

Instead of boring you by going through each new event, I’d like instead simply point out some interesting observations.

TMSCHEMA* Events

Quite a few “Discover” events have been added that appear to be related to the new Tabular object model that was mentioned back in March during the MSIgnite conference.

Of particular note are the ones highlighted below which lead me to believe Power BI Q&A with Tabular is just around the corner. And if that is the case, it’s not a moment too soon. If you’ve spent any reasonable amount of time playing with Power BI Q&A against a PowerPivot model, you’ve probably come to the same conclusion that it’s a little janky without using synonyms. So it only makes sense that similar functionality would need to be added to Tabular before it becomes a viable source for Q&A (not even going to hold my breath for similar capabilities to be added to Multidimensional). The problem there is that the development cycle for Tabular is a bit longer than PowerPivot (when using the streaming install version used with o365 accounts).

image

Unfortunately, I was unsuccessful in generating any activity in an xEvent trace for these events. As a last ditch effort, I took a shot to see if there were any TMSCHEMA-related DMVs…the naming just looked so similar to the MDSCHEMA-counterparts I thought there might a be chance.

image

This error message is quite revealing. On one hand, it further confirms the new Tabular object model (New Tabular mode ™). On the other, I don’t (yet) have a way to create a model in New Tabular mode. The latest SSDT-BI templates are for SQL 2014 and released well in advance of SQL 2016 preview…so Visual Studio isn’t an option. Fingers crossed, I went so far as to install Excel 2016 preview and created a PowerPivot model – hoping it was based on the new tabular object model. I then attempted to use the “restore from PowerPivot” option in SSMS, but unfortunately that didn’t work out any better.

Finally, I created a basic C# application in Visual Studio 2013, imported the Microsoft.AnalysisServices.dll to see if the new tabular object model had been exposed that way…but alas it appears not.

Guess we’ll just have to wait for the latest SSDT-BI templates to be released.

Predictive Analytics Events

Predictive Analytics is an entirely new Event Category and includes the following events. Unfortunately there aren’t any related EventSubClass row (hence the LEFT JOIN) so this doesn’t appear to be fully implemented.

image

That said, I suspect these items could be related to the built-in time-series forecasting features for o365 PowerView we saw at the PASS BA conference back in 2014. That’s a complete shot in the dark though.

Extended Events GUI

Yep, that’s right. It looks like we’ll finally be getting a GUI for Analysis Services Extended Events tracing!

Update 09/28/2015: alas, it appears my suspicions were proven true. See this post for details including a link to a video demonstration by @GuyInACube.

image

Not having a GUI has been one of the biggest deterrents to using xEvents in previous versions of Analysis Services. Several folks (myself included) blogged about it in an attempt to provide as much relief as possible via scripts/templates/etc…

…but even then I still found the process (configuring the captured events, starting the trace, stopping the trace, using XML to shred the results file) to be insanely clunky and a huge gumption trap for the one-off traces. For most recurring needs (e.g. fixed load-testing) powershell scripts could be used to automate xEvent trace collections.

That said, this feature is currently not working in SQL 2016 CTP2. For example, when I right-click Sessions and choose New Session, I get the following error:

image

If I start the trace manually (via XMLA command), I can see it listed in the object explorer, but there isn’t any functionality. There is an option to “delete” the trace, but that also returns an error.

image

Hopefully, this gets patched up and fully implemented prior to release as it is dearly needed.

Wrapping Up

Looking through trace events can provide a lot of clues as to what’s (potentially) coming down the pipe in this next release. That certainly doesn’t mean it will happen…but it is a fun exercise none the less.

For me, the most interesting item seen here is the xEvents GUI in SSMS. There are also some underlying trace events and related DMVs (not discussed here) that expose a much more complete implementation of xEvents in SSAS. So I have little doubt we will see much more progress prior to the release date.

What about you?

One thought on “Investigating xEvents in SSAS 2016 CTP2

Leave a Reply