Managing Analysis Services Activity - 14 Feb 2010

SQL Server Analysis Services (SSAS) provides both OLAP & Data Mining functionality. However, after you have developed and deployed solutions you will find the need to begin managing the server's activity. In this post I will provide you with an overview of the various techniques & tips you can use to monitor and in some cases manage SSAS.

SSAS Connection Architecture

There are three layers to connections in SSAS: Connections, Sessions, and Commands. Connections are just that, they represent a connection from a client to the server. Sessions provide statefulness for the connections such as transactions and command context. You are then left with Commands. Commands are just that, they represent the individual queries sent to the SSAS server. As you can imagine these three layers are tied together: Connections have Sessions and Sessions have Commands.

Viewing SSAS Activity 

Now that you understand the basics of SSAS connection architecture how do you start working with this data? Well, as with most things in SSAS, its not AS EASY when compared to the server's relational sibling. I begin with the easiest approach: tools.There are a few available tools that one can use to view and manage SSAS activity. First and foremost is that of SQLSentry's Performance Advisor for Analysis Services. This tool is hands down the best SSAS monitoring tool available. SQLSentry clearly filled a large void in the SSAS world. Moving on, there is a sample application called Activity Viewer found on CodePlex. This tool provides a basic, stripped-down activity monitor application that you can use. The tools I mentioned above provide this ability but perhaps you are in one of those shops that don't spend much money on 3rd party tools, now what? Analysis Services ships with what is called Schema Rowsets. There are similar to the concept of Dynamic Management Views (DMVs) (in fact some people refer to SSAS Schema Rowsets as 'DMVs') found in the relational engine as they provide us with the dynamic activity of the SSAS server. To find out currently running Connections, Sessions, and Commands you simply need to query the following three Schema Rowsets:

Note: If you would like to see every SSAS Schema Rowset that is available simply run the following MDX:

SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS

Run the following MDX queries to return all current connection activity:

SELECT * FROM $SYSTEM.DISCOVER_COMMANDS

GO

SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

GO

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONSGO

Killing SSAS Activity

Most importantly, not only does one need the ability to view SSAS activity but to control various activities. Usually the reason behind controlling activity is simple, there is a long-running MDX command that is affecting concurrent users on the server. Again, what about those of you who don't have access to 3rd party tools? XML for Analysis (XMLA), which was introduced with SSAS 2005 provides a Cancel command. Using the Cancel command you can kill any of the three objects we have discussed today. As an example below is a XMLA script that I used to terminate a sample connection:

21

Killing SSAS Activity Automatically

Last but not least, what if you want to kill SSAS activity on a regular basis? Well, you can use SQL Server Agent and another BI tool called SQL Server Integration Services (SSIS)! You can build a SSAS Activity Monitoring package from scratch or you can leverage Chris Webb's solution. Either way, these solutions provide a decent work around for the problem of having long running MDX queries bogging down your server.

Happy Monitoring!

DC