There are multiple ways to do this. We will explore using hybrid approach as mentioned in this Microsoft article.
- In SQL Server Management Studio, connect to an Analysis Services instance.
- Paste any one of the following DMV queries in an MDX query window to get a list of all sessions, connections, and commands that are currently executing:
Select * from $System.Discover_Sessions
Select * from $System.Discover_Connections
Select * from $System.Discover_Commands
Press F5 to execute the query.
The DMV query returns session and connection information in a tabular result set that is easier read and copy from.
Keep the query window open. In the next step, you will want to return to this page to copy the SPIDs of the session you want to disconnect.
To end a session, open a second XMLA query window.
- Paste the following syntax into an MDX query window, replacing the ConnectionID, SessionID, or SPID placeholder with a valid value copied from the previous step.
Thus you can terminate the long running queries or memory hogging sessions.