Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3
Extended Events were introduced into Analysis Services in SQL Server 2012. This made use of a common XE module that was shared with SQL Server Database Engine. The challenge with Extended Events is that it can be complicated to configure going on scripting alone. You need to know what events are available, and how you want to output the data.
The SQL Server 2012 Management Studio included a graphical user interface to configure and manage Extended Events within SQL Server Database Engine. Unfortunately, this was not available for Analysis Services. Starting with the CTP 2.3 release of SQL Server 2016, Analysis Services now benefits from this as well.
You can access the GUI for Extended Events by expanding Management under the server object within Object Explorer.
I will look at how to create an Extended Events session with the GUI, and then look at some examples of some sessions that may be helpful to you.
NOTE: This only works against SQL Server 2016. It will not work against an Analysis Services instances that are prior to SQL Server 2016.
Why use Extended Events?
The biggest reason to use Extended Events is because of how light weight it is. It won’t have the same impact on performance that a traditional Profiler Trace has. For example, it is reported that 20,000 events/sec on a 2ghz CPU with 1GB of RAM takes less than 2% of the CPU. This means that, on a modern server, you should see very little, if any, impact on performance. As with all things, be sure to validate this for your environment and test the impact it will have.
Another reason is that you can configure Extended Events to do many things that are outside the scope of this blog post. For example, you can trigger a memory dump to occur on certain conditions. You can also gather call stack information which is useful when debugging what is going on.
Creating an Extended Events Session
You can right click on Sessions to create a new Extended Events session.
On the General tab, we need to start with giving it a Session Name. Currently, there are no Templates available to choose from.
On the Events tab, you can select the events that you want to capture. You can also search for specific events you want to capture. It will highlight the items that matched what you typed in. When you select an event, you can get a description as well as see the Event fields that are available.
Clicking on Configure, within the Events tab, will allow you to select the fields that you want to capture. These are broken up into Global fields that occur on every event, as well as fields that are specific to that event. Here you can include Global fields on a specific event, such as call stack.
On the Data Storage tab, you can specify the target of where you want the data to go by click on Add. You can set it to event_file, event_stream or ring_buffer. You can include multiple targets. While the UI only displays three items, you can still use ETW (etw_classic_sync_target) as a target.
Event_file will give you properties to indicate the path of where the XEL file will be located. You can also indicate how big each file will be, if you want to enable file rollover, and how many files to retain. This will effectively ring the data through the files on the disk instead of in a buffer in memory.
Event_stream allows for live data steaming. When this target is selected, you can right click on the session and select Watch Live Data.
Ring_buffer keeps the event data in a buffer in memory. It will only be available as long as the server is up and running. Data will be lost when the service is restarted.
The Advanced tab allows for settings related to event loss, dispatch latency and buffering. Dispatch latency refers to the amount of time events will stay in buffers before being served to targets for processing. Buffering policies specify how much memory to use for event data.
When you are done configuring the event, you can click OK. Optionally, you can click on Script to script out the XMLA to create the Extended Events session. Using this tool is a great way to get started with the script side of configuring Extended Events and can speed up the process of making a script.
<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<ObjectDefinition>
<Trace>
<ID>My XEvent Session</ID>
<Name>My XEvent Session</Name>
<XEvent xmlns="https://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<event_session name="My XEvent Session" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<event package="AS" name="AuditLogin" />
<event package="AS" name="AuditLogout">
<action package="package0" name="callstack" />
</event>
<target package="package0" name="event_file">
<parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Log\My XEvent Session.xel" />
<parameter name="max_file_size" value="4096" />
<parameter name="max_rollover_files" value="10" />
<parameter name="increment" value="1024" />
</target>
</event_session>
</XEvent>
</Trace>
</ObjectDefinition>
</Create>
Stop/Delete a session
To delete a session, you can just right click on it, and select delete. Also, the only way to currently stop the trace, is to delete the session.
Or, you can issue an XMLA command.
<Delete xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<TraceID>My XEvent Session</TraceID>
</Object>
</Delete>
Examples
Here are some examples of XMLA scripts for certain types of captures.
Profiler Trace
<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<ObjectDefinition>
<Trace>
<ID>Profiler Trace</ID>
<Name>Profiler Trace</Name>
<XEvent xmlns="https://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<event_session name="Profiler Trace" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<event package="AS" name="AuditAdminOperationsEvent" />
<event package="AS" name="AuditLogin" />
<event package="AS" name="AuditLogout" />
<event package="AS" name="AuditObjectPermissionEvent" />
<event package="AS" name="AuditServerStartsAndStops" />
<event package="AS" name="CalculateNonEmptyBegin" />
<event package="AS" name="CalculateNonEmptyCurrent" />
<event package="AS" name="CalculateNonEmptyEnd" />
<event package="AS" name="CalculationEvaluation" />
<event package="AS" name="CalculationEvaluationDetailedInformation" />
<event package="AS" name="CommandBegin" />
<event package="AS" name="CommandEnd" />
<event package="AS" name="DAXQueryPlan" />
<event package="AS" name="Deadlock" />
<event package="AS" name="DirectQueryBegin" />
<event package="AS" name="DirectQueryEnd" />
<event package="AS" name="DiscoverBegin" />
<event package="AS" name="DiscoverEnd" />
<event package="AS" name="Error" />
<event package="AS" name="ExecuteMDXScriptBegin" />
<event package="AS" name="ExecuteMDXScriptCurrent" />
<event package="AS" name="ExecuteMDXScriptEnd" />
<event package="AS" name="ExecuteMDXScriptError" />
<event package="AS" name="ExistingConnection" />
<event package="AS" name="ExistingSession" />
<event package="AS" name="FileLoadBegin" />
<event package="AS" name="FileLoadEnd" />
<event package="AS" name="FileSaveBegin" />
<event package="AS" name="FileSaveEnd" />
<event package="AS" name="GetDataFromAggregation" />
<event package="AS" name="GetDataFromCache" />
<event package="AS" name="LockAcquired" />
<event package="AS" name="LockReleased" />
<event package="AS" name="LockTimeout" />
<event package="AS" name="LockWaiting" />
<event package="AS" name="Notification" />
<event package="AS" name="PageInBegin" />
<event package="AS" name="PageInEnd" />
<event package="AS" name="PageOutBegin" />
<event package="AS" name="PageOutEnd" />
<event package="AS" name="ProgressReportBegin" />
<event package="AS" name="ProgressReportCurrent" />
<event package="AS" name="ProgressReportEnd" />
<event package="AS" name="ProgressReportError" />
<event package="AS" name="QueryBegin" />
<event package="AS" name="QueryCubeBegin" />
<event package="AS" name="QueryCubeEnd" />
<event package="AS" name="QueryDimension" />
<event package="AS" name="QueryEnd" />
<event package="AS" name="QuerySubcube" />
<event package="AS" name="QuerySubcubeVerbose" />
<event package="AS" name="ResourceUsage" />
<event package="AS" name="SerializeResultsBegin" />
<event package="AS" name="SerializeResultsCurrent" />
<event package="AS" name="SerializeResultsEnd" />
<event package="AS" name="ServerStateDiscoverBegin" />
<event package="AS" name="ServerStateDiscoverData" />
<event package="AS" name="ServerStateDiscoverEnd" />
<event package="AS" name="SessionInitialize" />
<event package="AS" name="UserDefined" />
<event package="AS" name="VertiPaqSEQueryBegin" />
<event package="AS" name="VertiPaqSEQueryCacheMatch" />
<event package="AS" name="VertiPaqSEQueryCacheMiss" />
<event package="AS" name="VertiPaqSEQueryEnd" />
<target package="package0" name="event_file">
<parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Log\Profiler Trace.xel" />
<parameter name="max_file_size" value="4096" />
<parameter name="max_rollover_files" value="10" />
<parameter name="increment" value="1024" />
</target>
</event_session>
</XEvent>
</Trace>
</ObjectDefinition>
</Create>
Connection Tracking
<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<ObjectDefinition>
<Trace>
<ID>Connection Tracking</ID>
<Name>Connection Tracking</Name>
<XEvent xmlns="https://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<event_session name="Connection Tracking" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<event package="AS" name="AuditLogin" />
<event package="AS" name="AuditLogout" />
<event package="AS" name="ExistingConnection" />
<event package="AS" name="Error" />
<target package="package0" name="event_file">
<parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Log\Connection Tracking.xel" />
<parameter name="max_file_size" value="4096" />
<parameter name="max_rollover_files" value="10" />
<parameter name="increment" value="1024" />
</target>
</event_session>
</XEvent>
</Trace>
</ObjectDefinition>
</Create>
Query Trace
<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<ObjectDefinition>
<Trace>
<ID>Query Trace</ID>
<Name>Query Trace</Name>
<XEvent xmlns="https://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<event_session name="Query Trace" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<event package="AS" name="DirectQueryBegin" />
<event package="AS" name="DirectQueryEnd" />
<event package="AS" name="DAXQueryPlan" />
<event package="AS" name="QueryCubeEnd" />
<event package="AS" name="QueryCubeBegin" />
<event package="AS" name="QuerySubcube" />
<event package="AS" name="VertiPaqSEQueryBegin" />
<event package="AS" name="VertiPaqSEQueryEnd" />
<target package="package0" name="event_file">
<parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Log\Query Trace.xel" />
<parameter name="max_file_size" value="4096" />
<parameter name="max_rollover_files" value="10" />
<parameter name="increment" value="1024" />
</target>
</event_session>
</XEvent>
</Trace>
</ObjectDefinition>
</Create>
Adam W. Saxton | Microsoft Business Intelligence
@GuyInACube | YouTube | Facebook.com\guyinacube
Comments
Anonymous
November 16, 2015
What options are available for SSAS 2012 multidimensional?Anonymous
April 12, 2016
The comment has been removed