Cancelling A Long Running Query in SSAS

Cancelling a query in SSAS is not as straight forward it should be.  There’s not an activity monitor for SSAS like the one built into SSMS for the relational engine.  To cancel a query you need to end a session or connection.  This is documented by Microsoft here: Disconnect Users and Sessions on Analysis Services Server .  Basically, you run one of the following DMV queries to get the ConnectionID, SessionID, or SPID that’s executing the query to cancel.

Select * from $System.Discover_Sessions

Select * from $System.Discover_Connections

Select * from $System.Discover_Commands

Then, you pass the Connection, SessionID, or SPID to the respective tag in the following XML/A.  This code executes in an MDX query window.

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
     <ConnectionID>111</ConnectionID> 
     <SessionID>222</SessionID> <SPID>333</SPID> 
     <CancelAssociated>1</CancelAssociated> 
</Cancel>

I tend to cancel the SPID rather than the Connection or Session.  To get the SPID I run the first DMV ; Select * from $System.Discover_Sessions   Also, I can see the query text and the user that executed the query with this DMV.  I will also note that the cancelling the Session by SessionID throws an error.  I’m sure there’s a good solution, but I didn’t troubleshoot it as the cancelling by SPID or ConnectionID works fine.

As you can see, it’s not too difficult to cancel a query.  It’s easy to built SSRS reports using these DMVs.  Chris Webb wrote a blog post about Killing Queries From Reporting Services.  I will probably implement something similar to this.  I also found another good post where he writes about Killing sessions automatically with SSIS.  I might do this too.