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.

1307.image 5F00 thumb 5F00 186EB651 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

8686.image 5F00 thumb 5F00 410D2258 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

On the General tab, we need to start with giving it a Session Name. Currently, there are no Templates available to choose from.

8750.image 5F00 thumb 5F00 52EC46D6 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

5315.image 5F00 thumb 5F00 7DC73B99 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

2068.image 5F00 thumb 5F00 13B0ADEA Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

7367.image 5F00 thumb 5F00 4CCA1B9D Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

Event_stream allows for live data steaming. When this target is selected, you can right click on the session and select Watch Live Data.

8863.image 5F00 thumb 5F00 35326E2A Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

1256.image 5F00 thumb 5F00 24B9FD2F Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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.

8688.image 5F00 thumb 5F00 28C6DBB2 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

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=http://schemas.microsoft.com/analysisservices/2003/engine>

  <ObjectDefinition>

    <Trace>

      <ID>My XEvent Session</ID>

      <Name>My XEvent Session</Name>

      <XEvent xmlns=http://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=http://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.

4130.image 5F00 thumb 5F00 0605A3F5 Using Extended Events with SQL Server Analysis Services 2016 CTP 2.3

Or, you can issue an XMLA command.

<Delete xmlns=http://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=http://schemas.microsoft.com/analysisservices/2003/engine>

  <ObjectDefinition>

    <Trace>

      <ID>Profiler Trace</ID>

      <Name>Profiler Trace</Name>

      <XEvent xmlns=http://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=http://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=http://schemas.microsoft.com/analysisservices/2003/engine>

  <ObjectDefinition>

    <Trace>

      <ID>Connection Tracking</ID>

      <Name>Connection Tracking</Name>

      <XEvent xmlns=http://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=http://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=http://schemas.microsoft.com/analysisservices/2003/engine>

  <ObjectDefinition>

    <Trace>

      <ID>Query Trace</ID>

      <Name>Query Trace</Name>

      <XEvent xmlns=http://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=http://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

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Analysis Services Team Blog