Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Extended Events to monitor DAX queries for SSAS Tabular Databases


By:   |   Last Updated: 2016-11-25   |   Comments   |   Related Tips: > Analysis Services Administration

Problem

Monitoring queries that are executed against a database is a common task for administrators as well as developers for debugging and analysis purposes. Consider a scenario where one wishes to track the queries being executed against a SQL Server Analysis Services (SSAS) Tabular 2016 database without using SQL Server Profiler, either due to lack of privileges to access Profiler or due to Profiler being considered a high resource intensive tool. Read this tip to learn more.

Solution

SQL Server Analysis Services (SSAS) as well as SQL Server Management Studio (SSMS) supports the need for monitoring SSAS events using Extended Events, which can be used to intercept queries.

Extended Events (xEvents) is a light-weight tracing and performance monitoring system that uses very few system resources, making it an ideal tool for diagnosing problems. In this tip we will configure an Extended Events session and look at an example of intercepting a DAX query executed using SSMS.

Step 1: Open SSMS and log on to your SSAS Tabular instance, where you should have at least one SSAS Tabular database available. Open the Management folder, right-click the Sessions folder and select the menu item "New Session". Provide an appropriate name for the session as shown below.

New Session for SQL Server Extended Events

Step 2: Click on the events tab and select the QueryBegin and QueryEnd events as shown below. You can select the events of your choice from the events list, and in case you wish to configure the properties of these events, you can do so by clicking the Configure button on the top.

Events to select for SQL Server Extended Events

Step 3: Click on the data storage tab and select the event_stream option as shown below, and click on the Add button. You can set up live data stream to monitor server activity in real time, keep session data loaded in memory for faster analysis, or save data streams to a file for offline analysis by selecting the corresponding options.

Data Storage options for SQL Server Extended Events


Step 4: After the above step, click on the Script button, which should open a script as shown below. Execute this script in SSMS.

Script for SQL Server Extended Events

Step 5: Refresh the Sessions folder and you should be able to find the new session that we created in the above step. Select the session, right-click on it and select the menu item "Watch Live Data" as shown below.

Watch Live Data from SQL Server Extended Events

Step 6: Open a new MDX query window in SSMS, with a connection to the SSAS Tabular database and execute any DAX or MDX query. Here I am executing a simple DAX query as shown below.

Execute a sample DAX Query

Step 7: Switch to the Extended Events session we opened in step 5. You should be able to find the corresponding events we selected earlier. Click on the QueryBegin event, and you should be able to find the relevant details like database name, the query and the details of the user who executed the query.

Captured Data for SQL Server Extended Events

In this way we can use Extended Events in SSMS to intercept and monitor different events and actions for a SSAS Tabular 2016 database.

Next Steps
  • Try to configure more than one session with different data storage targets, and intercept MDX queries executed against SSAS Tabular databases.
  • Read more SSAS tips


Last Updated: 2016-11-25


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools