Sometime in your SSAS career you will hit a bad query that will proc your SSAS server. Restarting the service will resolve the issue temporarily but depending on how often that query hits the server, you might not have enough time to run a trace or diagnose the issue. So to keep the process down, you can run the following commands to find the bad query and kill it so you have more time to fix the actual problem.
- In the cube that you're experience issues with, open up a MDX session and run the following query:
select * from $system.discover_sessions
where session_status = 1 - That will give you a list of the sessions that are currently running active queries.
- Open up a XMLA session in the cube you are working on and run the following command:
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SPID>insert the SPID from step 1 here</SPID>
</Cancel> - Monitor the CPU drop in your SSAS box and enjoy!
