//
you're reading...
BI, SQL Server

Kill a Session, SPID or Connection to Analysis Services V2.0

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:

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<ConnectionID>[CID]</ConnectionID>
<SessionID>[SID]</SessionID>
<SPID>[SPID]</SPID>
<CancelAssociated>[true/false]</CancelAssociated>
</Cancel>

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.

Resources:

      MSDN – Use Dynamic Management Views (DMVs) to Monitor Analysis Services

 

Bonn Appetit

L.Dixon Galler

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: