how to find costly CUBE query and kill it

 

often times, you will find that SSAS server gets super busy because of weird request from CUBE client (proclarity, excel, report builider ), as the SSAS Administrator,

you need to find the culprit and kill . Here is how to do so

1. Find it

 

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_SESSIONS</RequestType>
  <Restrictions>
    <RestrictionList />
  </Restrictions>
  <Properties>
    <PropertyList />
  </Properties>
</Discover>

 

2. Kill it

 

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

<SPID>121672</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

 

Now the challenge is to find out the culprit as step 1 will return a list of SPIDs, how do you sift through???