Overview:  Quick tip for cancelling a long-running SQL Server Analysis Services query.  Screen shots & statements are from SQL Server 2008 R2.

We’ve all been there…waiting in BIDS for a report to render.  You begin to think uh-oh.  After a bit longer you know it’s uh-oh. 

  

There’s multiple ways to accomplish cancelling the session; here’s one way to cancel a long-running SSAS query.  One caveat: this technique does require elevated privileges.

Step 1:  Use a DMV to find the SPID

First, you need to find the SPID associated to your long-running report query.  We’ll use a Dynamic Management View (DMV) for that.  First let’s launch an MDX window in SQL Server Management Studio:

  

Obviously you can modify the select statement here to whatever columns you wish to see. 

Query:

SELECT

session_spid

, session_user_name

, session_last_command

, session_current_database

, session_cpu_time_ms

, session_elapsed_time_ms

, session_start_time

, session_last_command_start_time

, session_last_command_end_time

FROM $system.discover_sessions

WHERE session_status = 1

AND session_user_name = 'DomainName\MCoates'

ORDER BY session_start_time desc

Results:

  

In the above query, I restricted the results to just my user name.  Using the results, find the query associated to your long-running report.  Note the Session_Spid (i.e., the Server Process ID).

Step 2:  Use XMLA Command to Cancel the SPID

Now that we have the SPID, we need to pop over to an XMLA window (rather than the MDX window we were in for Step 1 above).

  

Command:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<SPID>185325</SPID>

</Cancel>

After executing the Cancel Command, you should immediately see the message in BIDS that the operation has been cancelled.  Mission accomplished.

  

A Final Word on Permissions

I’m lucky enough in the current environment to be able to do this on my own.

DMV Select Permissions:  Requires VIEW SERVER STATE or VIEW DATABASE STATE permissions.

XMLA Cancel Command Permissions:  Requires administrative permissions.

Finding More Information

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

MSDN – Discover Sessions

MSDN – Cancel Element (XMLA)