I had a recent post regarding finding and killing SPIDS in SSAS. This post just provides some additional MDX & XMLA queries to identify and kill processes at various levels. What I have found is that sometimes it is not enough to kill the SPID, you must go higher and kill the Connection (CID) or Session (SID).
This post goes through the XMLA required for killing a SSAS command, as well as the Analysis Service DMVs that can be utilized to identify the required IDs.
In order to cancel a command on an SSAS database, you need to issue an XMLA command on that database, this can be done by starting a new query on the database with type XMLA.
The XMLA for killing a command looks like this:
The XML layout above shows that you can cancel a command on different levels, below is a walk-through of each element:
- ConnectionID: Highest level to cancel a command at, this will cancel all SPIDs executed by this connection ID to the SSAS database.
- SessionID: A Session belongs to a Connection ID and can have many SPIDs executed within one Session.
- SPID: This is the specific Server Process ID that has been assigned to this Session.
- CancelAssociated: This is a True/False flag to indicate if the Cancel command is propagated to all commands associated with the supplied ID, if omitted “False” is assumed.
Below is an image that visualizes how the hierarchy of commands look in SSAS, this should clarify what level you need to cancel a particular command, and how each level connects to the one below.
If you are an administrator, then you can issue the SSAS Cancel command on any level shown above by supplying the ID for that particular connection, session or SPID. In order to identify these IDs, you could look into the following SSAS Dynamic Management Views:
SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS
This will return all active connections to a particular SSAS Instance Database, along with useful information such as the ConnectionID as well as user, time and data based statistics
SELECT * FROM $SYSTEM.DISCOVER_SESSIONS
This DMV will return all active sessions on a particular SSAS database, this DMV can be linked to the connections DMV using the SESSION_CONNECTION_ID column.
SELECT * FROM $SYSTEM.DISCOVER_COMMANDS
Currently executing commands, this can be linked to the Sessions DMV using the SPID column.
That should give you all you need to start killing those commands!
A Final Word on Permissions
I can run these command in Dev but no where else. These are the permissions you need to run these commands.
DMV Select Permissions: Requires VIEW SERVER STATE or VIEW DATABASE STATE permissions.
XMLA Cancel Command Permissions: Requires administrative permissions.