Extended Events with Azure Analysis Services

Its almost standard fare to be using Azure Analysis Services with our customer deployments these days.  As our customers evolve the value of their data.  SSIS integration runtimes were pivotal to this and now that there is Azure Analysis Services, it’s even easier to get started with just a few clicks in the portal interface, (or for me, a simple step in a script… :)) and migrate runtimes to the cloud.

One of the biggest challenges for any cloud migration project is performance issues, not due to cloud, but due to technologists attempting to perform tasks as they have on-premises and not taking into consideration how they’ve changed their environment.  Any time I’m brought into one of these scenarios, I’m often asked to make an educated guess at what is causing latency and I’ve never found it beneficial to anyone.  I want to see some data to understand what is causing the latency and make an educated recommendation.  This is where extended events can come in handy and you can do this with Analysis Services in Azur

If Analysis Services is experiencing latency, first go over best practices to ensure those are implemented.

• Ensure all transformations run inside memory.
• Use views for the data model, but avoid nested views.
• Ensure that foreign keys are enforced.
• Consider turning off automatic update on tabular models.
• Ensure enough memory is allocated to the workstation accessing the environment.
• Use lock optimization whenever possible.
• Use the correct data types to match the data you’re working with!

Next, connect to your Analysis Services Management Server by gathering the name of the server from the portal:

Using this information, you can then log into SQL Server Management Studio, preferably using your Azure Active Directory login with MFA.  MFA, or Multi-Factor Authentication ensures a simple and secure method of connecting to the server.  You can check access and add an individual if they are missing via the Access Control blade, (IAM).  There is an option to view the list of those that have access, as well as add a new role assignment by the list from Azure Active Directory.

Once you log into your Analysis Services, you can expand to access extended events setup:

As an integration runtime is executing, you’re able to trace the process using Extended Events by right clicking on Sessions and click on New Session.

As this is a new system, I’m going to name my session, (identify it as unique as you require, often using a timestamp is helpful if you’re working on many collections) and if you’re new to this, the sample template for AAS is quite helpful to start.  Choose it and if you wish to configure to review and add any additional events to capture, do so by clicking Configure.

Choose Data Storage and you have two basic options.  I chose live stream, as I plan on monitoring the process and am good with it doing a standard live stream for it.  In the Advanced blade, you can add any additional options you’d like, but if you’re new and just starting to trace to find out what’s going on, I’d click on Run and let it begin.  

You’ll then note that you have an active trace that you can right click on and either run reports against or just click on Live Data to view the trace in the right pane of the SSMS window.

Each event will have a detail section in the bottom of the SSMS interface:

There is a lot of valuable data, but you’ll begin by noting the Client Process ID, the Process ID and the event class.  Note that you now have the User Object ID that correlates to the Object ID inside the catalog and the current time.  Each step will be tracked with the time on each step, so you can track what is happening and how long it takes.  The Event Class tells us that a lock was acquired and even the call stack it came from.  Individually they may not mean much, but we can take each of the steps and details to give us a clear picture what is consuming time and as I say, “Tune for time or you’re wasting time.”

I’ll dig deeper into this soon, but it’s important to start to collect data and to stop guessing on what is causing latency issues in the cloud.  Azure and more so, Microsoft offers us the tools to collect real data and to create a full picture of what is happening with our environments.  We should take advantage of this information.