I recently started thinking about using extended events to track Analysis Services for a client. They wanted to track their SSAS instances, and with SQL Profiler gone, Extended Events was the best long-term solution.
I have to admit, when I first started researching this topic, I was blown away. There was very little to do other than a few blog posts, which I'll list below. I believe that, overall, SQL Server Books Online (msdn, technet, etc.) have pretty solid content. but for useAnalysis Services Extended Events, I have to agree with Chris Webb (the blog|Twitter) that the BOL offers little value. Note: While the examples I've seen in the wild, as well as my example below, used SSAS Multidimensional, I've implemented this for SSAS Tabular in my client. So it works for both.
I'm not going to advise you on which events to track for different purposes. I'm afraid it's beyond the scope of this post and something I don't have in-depth knowledge of at this point.
When researching this topic, I used the following blog posts:
Chris Webb (the blog|Twitter) –Usando XEvents no SSAS 2012
Bill Anton (the blog|Twitter) –Extended Events for Analysis Services
Andreas Wolter (the blog|Twitter) –Tracing Analysis Services (SSAS) com Extended Events – Sim, funciona e tal
Francesco De Chiricothe blog|Twitter) –Identify storage engine and formula engine bottlenecks with new SSAS XEvents
All of these posts have been helpful in one way or another. In some cases I used a post as a source to base the queries I used on. If that's the case, I'll make it clear where my base code came from. I do this because I am a staunch supporter of recognition where it is due.
Unlike those for the database engine, Extended Events for Analysis Services does not have a graphical user interface. You have to work on the code. Also, the code is XMLA. oops I know there are people who know about XMLA, but I'm not one of them. This was part of what worried me when I started working with extended events for SSAS.
For the CREATE script for my extended event trace, I referred to Bill Anton's blog post above. This script contains not only the base syntax, but also all the events that are commented out (I think that's all anyway). This allowed me to only comment out the events I wanted to track, but leave the others intact for later use. For this script, make sure you are connected to an instance of SSAS in Management Studio and not the Database Engine. Also, you're ideally in an XMLA query window; I could also run this code in an MDX window, but my examples below assume an XMLA window.
Note: In XMLA, lines starting with <!– and ending with –> are comments.
1:<!-- This is a script by Bill Anton http://byobi.com/blog/2013/06/extended-events-for-analysis-services/ -->
2:3:<Crying
4: xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
5: xmlns:xsi="http://www.w3.org/2001/XMLSchema-Instanz"
6: xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
7: xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
8: xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
9: xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
10: xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
11: <object definition>
12: <follow>
13: <I COULD>my track</I COULD>
14: <!--Exemplo: <ID>QueryTuning_20130624</ID>-->
fifteen: <Name>my track</Name>
16: <!--Exemplo: <Name>QueryTuning_20130624</Name>-->
17: <ddl300_300:XEvent>
18: <session_event Name="xeas"
19: DispatchLatency= "1"
20: maxEventSize="4"
21: maxSpeicher="4"
22: memoryPartitionMode="none"
23: eventRetentionMode="permitirSingleEventLoss"
24: trackCausality= "true">
25:26: <!-- ### COMMAND EVENTS ### -->
27: <!--<event package="AS" name="CommandBegin" />-->
28: <!--<event package="AS" name="CommandEnd" />-->
29:30: <!-- ### DISCOVER EVENTS ### -->
31: <!--<event package="AS" name="DiscoverBegin" />-->
32: <!--<event package="AS" name="DiscoverEnd" />-->
33:34: <!-- ### DETECT SERVER STATUS EVENTS ### -->
35: <!--<event package="AS" name="ServerStateDiscoverBegin" />-->
36: <!--<event package="AS" name="ServerStateDiscoverEnd" />-->
37:38: <!-- ### ERROR AND WARNING ### -->
39: <!--<Event Pack="AS" name="Erro" />-->
40:41: <!-- ### LOAD AND SAVE THE FILE ### -->
42: <!--<event package="AS" name="FileLoadBegin" />-->
43: <!--<event package="AS" name="FileLoadEnd" />-->
44: <!--<event package="AS" name="FileSaveBegin" />-->
45: <!--<event package="AS" name="FileSaveEnd" />-->
46: <!--<event package="AS" name="PageInBegin" />-->
47: <!--<event package="AS" name="PageInEnd" />-->
48: <!--<event package="AS" name="PageOutBegin" />-->
49: <!--<event package="AS" name="PageOutEnd" />-->
50:51: <!-- ### BLOCKS ### -->
52: <!--<event package="AS" name="Impasse" />-->
53: <!--<event package="AS" name="LockAcquired" />-->
54: <!--<event package="AS" name="LockReleased" />-->
55: <!--<event package="AS" name="LockTimeout" />-->
56: <!--<event package="AS" name="LockWaiting" />-->
57:58: <!-- ### NOTIFICATION EVENTS ### -->
59: <!--<event package="AS" name="Notification" />-->
60: <!--<event package="AS" name="UserDefined" />-->
61:62: <!-- ### PROGRESS REPORTS ### -->
63: <!--<event package="AS" name="ProgressReportBegin" />-->
64: <!--<event package="AS" name="ProgressReportCurrent" />-->
65: <!--<event package="AS" name="ProgressReportEnd" />-->
66: <!--<event package="AS" name="ProgressReportError" />-->
67:68: <!-- ### REQUEST EVENTS ### -->
69: <!--<event package="AS" name="QueryBegin" />-->
70: <event Package="HOW" Name="ConsultaFim" />
71:72: <!-- ### QUERY PROCESSING ### -->
73: <!--<event package="AS" name="CalculateNonEmptyBegin" />-->
74: <!--<event package="AS" name="CalculateNonEmptyCurrent" />-->
75: <!--<event package="AS" name="CalculateNonEmptyEnd" />-->
76: <!--<event package="AS" name="CalculationEvaluation" />-->
77: <!--<event package="AS" name="CalculationEvaluationDetailedInformation" />-->
78: <!--<event package="AS" name="DaxQueryPlan" />-->
79: <!--<event package="AS" name="DirectQueryBegin" />-->
80: <!--<event package="AS" name="DirectQueryEnd" />-->
81: <!--<event package="AS" name="ExecuteMDXScriptBegin" />-->
82: <!--<event package="AS" name="ExecuteMDXScriptCurrent" />-->
83: <!--<event package="AS" name="ExecuteMDXScriptEnd" />-->
84: <!--<event package="AS" name="GetDataFromAggregation" />-->
85: <!--<event package="AS" name="GetDataFromCache" />-->
86: <!--<event package="AS" name="QueryCubeBegin" />-->
87: <!--<event package="AS" name="QueryCubeEnd" />-->
88: <!--<event package="AS" name="QueryDimension" />-->
89: <!--<event package="AS" name="QuerySubcube" />-->
90: <!--<event package="AS" name="ResourceUsage" />-->
91: <!--<event package="AS" name="QuerySubcubeVerbose" />-->
92: <!--<event package="AS" name="SerializeResultsBegin" />-->
93: <!--<event package="AS" name="SerializeResultsCurrent" />-->
94: <!--<event package="AS" name="SerializeResultsEnd" />-->
95: <!--<event package="AS" name="VertiPaqSEQueryBegin" />-->
96: <!--<event package="AS" name="VertiPaqSEQueryCacheMatch" />-->
97: <!--<event package="AS" name="VertiPaqSEQueryEnd" />-->
98:99: <!-- ### SECURITY CHECK ### -->
100: <!--<event package="AS" name="AuditAdminOperationsEvent" />-->
101: <event Package="HOW" Name="AuditoriaLogin" />
102: <!--<event package="AS" name="AuditLogout" />-->
103: <!--<event package="AS" name="AuditObjectPermissionEvent" />-->
104: <!--<event package="AS" name="AuditServerStartsAndStops" />-->
105:106: <!-- ### SESSION EVENTS ### -->
107: <!--<event package="AS" name="ExistingConnection" />-->
108: <!--<event package="AS" name="ExistingSession" />-->
109: <!--<event package="AS" name="SessionInitialize" />-->
110:111:112: <Meta Package="package0" Name="event_file">
113: <!-- Make sure the SSAS instance service account can write to this location -->
114: <Parameter Name="file name" Wert="C:\SSASExtendedEvents\MyTrace.xel" />
115: <!--Beispiel: <parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS11.SSAS_MD\OLAP\Log\trace_results.xel" />-->
116: </Meta>
117: </session_event>
118: </ddl300_300:XEvent>
119: </follow>
120: </object definition>
121:</Crying>
You can download a version of this script without line numberson here.
I modified Bill's original script in a few places for my purposes.
I used my own tracking ID and tracking name on lines 13 and 15 respectively.
12: <follow>
13: <I COULD>my track</I COULD>
14: <!--Exemplo: <ID>QueryTuning_20130624</ID>-->
fifteen: <Name>my track</Name>
16: <!--Example: <Name>QueryTuning_20130624</Name>—>
I commented out the Query End event on line 70 as well as the AuditLogin event on line 101 because those were the events I wanted to track to keep things simple.70:<eventPackage="WHAT IF"name="ConsultaFim"/> 101:<eventPackage="WHAT IF"name="Audit Login"/>I added my own output file path on line 114.114:<Parameter name="file name" Wert=„C:\SSASExtendedEvents\MyTrace.xel“/>I also added a comment on line 113.113:<!– Make sure the service account can write to the SSAS instancethisOrt –>I did this because I came across this myself. When running the above script, I initially got an "Access Denied" message. After giving my SSAS instance service account rights to change the C:\SSASExtendedEvents folder, I was good to go and tracing started without a hitch. When you run the query, the results pane should look like the screenshot below. This indicates success. You must love XMLA, right?
You can verify that Extended Events tracing is running by running the following query in an MDX query window connected to the same instance where you started tracing. The following query can be found in all of the above blog posts.
SELECT*VON$system.discover_traces
My results for this query looked like this:
Notice that the highlighted line in the red rectangle says "MyTrace" and the type is XEvent. Hazza! You can also see the specified destination folder for the output file. In my case it is C:\SSASExtendedEvents, see below.
There are two files here because I kept the output file from a previous test run. I did this to show you that the function I'm going to use to import this information into the database engine in tabular form can easily iterate over multiple files. You'll notice that the engine has added a lot of numbers to the filename. I haven't run this long enough to get into multiple files, but I assume that _0_ refers to the first file in a trace session. As in , the next file would have the same name but with _1_, the next file _2_, and so on. But that's just a guess. The long string of numbers afterwards seems to be there to ensure the trace file name is unique.
OK. Therefore, we perform extended event tracing. And now? Well, let's ask some questions. In my case, I just ran some of the MDX queries from myMDX Trek: first contactPresentation. The queries themselves don't matter. Just query a database on your SSAS instance anyway.
Reading the xel file in code (as opposed to manually reading it in Management Studio) involves one of two processes that I know of.
1. diesys.fn_xe_file_target_read_fileFunction followed by XML destruction. This feature was mentioned by Bill Anton and Francesco De Chirico in their posts.
2. Jonathan Kehayias (the blog|Twitter) mentioned me on Twitter using theQueryableXEventDataclass over .Net code. He emphasized that this is his preferred method as it is much faster than using the sys.fn_xe_file_target_read_file function and then shredding the XML.
It's a good idea to trust Jonathan in extended events, among other things. However, since I'm not a .Net user and want to post this while it's fresh in my mind, I'll demonstrate the first method. I've found that method 1 isn't very fast to be sure. But for now, event on my client will do. I intend to get interested in .Net and maybe blog about it when I do. 🇧🇷
In Francesco De Chirico's post, he not only discusses using the sys.fn_xe_file_target_read_file function to read xel files, but also provides great examples of XML destruction. XML and I agree: we both understand that I'm bad at XML. 🙂 So the fact that Francesco provided the XML shredder syntax was a huge discovery for me.
1:/****2:Basic consultation providedvonFrancesco De Chirico
3:http://francescodechirico.wordpress.com/2012/08/03/identify-storage-engine-e-formula-engine-bottlenecks-com-Neu-ssas-xevents-5/
4:5:****/6:7:SELECT
8:xe.TraceFileName9:, xe.TraceEvent10:, xe.EventDataXML.Wert('(/event/data[@name="EventSubclass"]/value)[1]','int')WHAT IFEventSubclasse
11:, xe.EventDataXML.Wert('(/event/data[@name="ServerName"]/value)[1]','varchar(50)')WHAT IFserver name
12:, xe.EventDataXML.Wert('(/event/data[@name="database name"]/value)[1]','varchar(50)')WHAT IFdatabase name
13:, xe.EventDataXML.Wert('(/event/data[@name="NTUserName"]/value)[1]','varchar(50)')WHAT IFNTBenutzername
14:, xe.EventDataXML.Wert('(/event/data[@name="ConnectionID"]/value)[1]','int')WHAT IFconnection id
fifteen:, xe.EventDataXML.Wert('(/event/data[@name="StartTime"]/value)[1]','appointment time')WHAT IFstart time
16:, xe.EventDataXML.Wert('(/event/data[@name="EndTime"]/value)[1]','appointment time')WHAT IFEnd of Times
17:, xe.EventDataXML.Wert('(/event/data[@name="duration"]/value)[1]','Great')WHAT IFduration
18:, xe.EventDataXML.Wert('(/event/data[@name="TextData"]/value)[1]','varchar(max)')WHAT IFTextData
19:VON
20:(21:SELECT
22:[FILE NAME]WHAT IFTraceDatename
23:, OBJECTNAMEWHAT IFTraceEvent
24:,CONVERTER(XML, event data)WHAT IFEventDataXML
25:VONsys.fn_xe_file_target_read_file ('C:\SSASExtendedEvents\MyTrace*.xel',Null,Null,Null)
26:) Car27:Notice on line 25 that the destination of the file specifies MyTrace*.xel. This is because the last part of the filename is not necessarily known. MyTrace*.xel instructs the function to traverse all files that conform to this specification. So when I run this query, it pulls data from both of the files shown earlier in my C:\SSASExtendedEvents folder.
On line 24, we convert the Event_Data column, which the function returns as nvarchar(max), to XML to allow the use ofmethod value().
Please note that I am not extracting all available information in the xel file. I only pull fields that are important to me for my purposes. There's more there. And it depends on the events you select when creating the trace.
When I run this query I get the following:
I can use this, as I did on my client, to insert a table into a SQL Server database for further analysis. We are currently planning a tabular model for this data to track usage of your BI offerings across the organization. It will be fun to play with it.
When you're ready to stop tracing, run the following XMLA:
1:<extinguish xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
2: <Object>
3: <TraceID>my track</TraceID>
4: </Object>
5:</extinguish>
That is all. I hope this is a useful addition to what is available for working with extended events in Analysis Services. It was definitely a great learning experience for me.
Categories:Advanced Events,SSAS
Tagged as:Advanced Events,SSAS