One of the useful features of the SSAS Activity Viewer 2012 is the ability to cancel sessions and queries.

Though there is no obvious way to do this through SSMS you can in fact write an xmla query to kill either the session, connection or command:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ConnectionID>...</ConnectionID>
  <SessionID>...</SessionID>
  <SPID>...</SPID>
  <CancelAssociated>true/false</CancelAssociated>
 </Cancel>

If you specify the connection ID it will kill all sessions, if you kill a session it will cancel all SPIDs that pertain to that session, and if you kill the SPID it will kill that one command. Cancel associated will kill all subsequent commands form the connection/session/SPID. You do need to be an administrator to run cancel commands.

By using MDX you can query the DMV’s to find the appropriate ID you wish to cancel:

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

SELECT * FROM $SYSTEM.DISCOVER_COMMANDS

Within SSAS Activity Viewer 2012 the same MDX DMV’s are used, but we use the query below for active sessions:

SELECT SESSION_SPID, COMMAND_CPU_TIME_MS, COMMAND_ELAPSED_TIME_MS, COMMAND_READ_KB, COMMAND_WRITE_KB, COMMAND_TEXT FROM $system.DISCOVER_COMMANDS WHERE COMMAND_ELAPSED_TIME_MS > 0 ORDER BY COMMAND_CPU_TIME_MS DESC

and this is the query we use to determine dormant sessions:

select TOP 25 SESSION_SPID, SESSION_USER_NAME,SESSION_IDLE_TIME_MS, SESSION_USED_MEMORY, SESSION_LAST_COMMAND, SESSION_LAST_COMMAND_END_TIME, SESSION_ID from $system.DISCOVER_SESSIONS WHERE SESSION_STATUS = 0 ORDER BY SESSION_IDLE_TIME_MS DESC

and this is the query used to find blocked sessions:

SELECT SESSION_SPID,SESSION_USER_NAME,SESSION_START_TIME, SESSION_ELAPSED_TIME_MS, SESSION_CPU_TIME_MS,SESSION_ID FROM $SYSTEM.DISCOVER_SESSIONS WHERE SESSION_STATUS = 2 ORDER BY SESSION_USER_NAME DESC

Use these queries to help find the sessions that may be causing you issues.

There is also a version of the SSAS Activity Viewer 2012 available for SQL Server 2014 CTP2, called Analysis Services Server Activity Monitor (ASSAM) 2014.

If you want more info about SSASAV 2012/ASSAM 2014 read my blog posts below. SQL Server Analysis Services Activity Viewer 2012 1.1 Stable Released Analysis Services Server Activity Monitor 2014 0.0.0.1 Alpha Released