Search
Close this search box.

Finding and killing SSAS Sessions

If you have a reasonably large Analysis Services implementation with users running adhoc queries via multiple tools (ProClarity, Excel, etc…) then you have probably run into problems with long-running queries.  While longer-running queries may be acceptable in many data warehousing implementations, there will occasionally be a query (maybe even more than one 😉 ) that causes resource issues and runs beyond the established limits of reason for your environment. Analysis Services does not have a native tool in SQL Management Studio for monitoring session activity and/or killing connections (hint: please add one). There is some sample code you can get from Microsoft along these lines but I wanted something simpler. In digging around for info on how to do this I found pieces of how to do it but nothing comprehensive so this is what I have put together so that I can do this through the SQL Management Studio.

Open a XMLA query window connected to the Analysis Services server in question.

Command 1 (Session Monitoring/Find it!):

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

Command 2 (Kill it!):

Cancelxmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <SPID>121672</SPID>
 <CancelAssociated>1</CancelAssociated>
</Cancel>

Command 1 uses the Discover method to list the sessions. A lot of good info is in the output of this command: See the following example:

 
<returnxmlns="urn:schemas-microsoft-com:xml-analysis">
 <rootxmlns="urn:schemas-microsoft-com:xml-analysis:rowset"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:schematargetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset"xmlns:sql="urn:schemas-microsoft-com:xml-sql"elementFormDefault="qualified">
      <xsd:elementname="root">
        <xsd:complexType>
          <xsd:sequenceminOccurs="0"maxOccurs="unbounded">
            <xsd:elementname="row"type="row" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:simpleTypename="uuid">
        <xsd:restrictionbase="xsd:string">
          <xsd:patternvalue="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
        </xsd:restriction>
      </xsd:simpleType>
      <xsd:complexTypename="xmlDocument">
        <xsd:sequence>
          <xsd:any />
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexTypename="row">
        <xsd:sequence>
          <xsd:elementsql:field="SESSION_ID"name="SESSION_ID"type="xsd:string"minOccurs="0" />
          <xsd:elementsql:field="SESSION_SPID"name="SESSION_SPID"type="xsd:int"minOccurs="0" />
          <xsd:elementsql:field="SESSION_CONNECTION_ID"name="SESSION_CONNECTION_ID"type="xsd:int"minOccurs="0" />
          <xsd:elementsql:field="SESSION_USER_NAME"name="SESSION_USER_NAME"type="xsd:string"minOccurs="0" />
          <xsd:elementsql:field="SESSION_CURRENT_DATABASE"name="SESSION_CURRENT_DATABASE"type="xsd:string"minOccurs="0" />
          <xsd:elementsql:field="SESSION_USED_MEMORY"name="SESSION_USED_MEMORY"type="xsd:int"minOccurs="0" />
          <xsd:elementsql:field="SESSION_PROPERTIES"name="SESSION_PROPERTIES"type="xsd:string"minOccurs="0" />
          <xsd:elementsql:field="SESSION_START_TIME"name="SESSION_START_TIME"type="xsd:dateTime"minOccurs="0" />
          <xsd:elementsql:field="SESSION_ELAPSED_TIME_MS"name="SESSION_ELAPSED_TIME_MS"type="xsd:unsignedLong"minOccurs="0" />
          <xsd:elementsql:field="SESSION_LAST_COMMAND_START_TIME"name="SESSION_LAST_COMMAND_START_TIME"type="xsd:dateTime"minOccurs="0" />
          <xsd:elementsql:field="SESSION_LAST_COMMAND_END_TIME"name="SESSION_LAST_COMMAND_END_TIME"type="xsd:dateTime"minOccurs="0" />
          <xsd:elementsql:field="SESSION_LAST_COMMAND_ELAPSED_TIME_MS"name="SESSION_LAST_COMMAND_ELAPSED_TIME_MS"type="xsd:unsignedLong"minOccurs="0" />
          <xsd:elementsql:field="SESSION_IDLE_TIME_MS"name="SESSION_IDLE_TIME_MS"type="xsd:unsignedLong"minOccurs="0" />
          <xsd:elementsql:field="SESSION_CPU_TIME_MS"name="SESSION_CPU_TIME_MS"type="xsd:unsignedLong"minOccurs="0" />
          <xsd:elementsql:field="SESSION_LAST_COMMAND"name="SESSION_LAST_COMMAND"type="xsd:string"minOccurs="0" />
          <xsd:elementsql:field="SESSION_LAST_COMMAND_CPU_TIME_MS"name="SESSION_LAST_COMMAND_CPU_TIME_MS"type="xsd:unsignedLong"minOccurs="0" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <SESSION_ID>9EBAB2DE-A23E-4183-8508-7A729BF41C85</SESSION_ID>
      <SESSION_SPID>126449</SESSION_SPID>
      <SESSION_CONNECTION_ID>233</SESSION_CONNECTION_ID>
      <SESSION_USER_NAME>DOMAIN\User</SESSION_USER_NAME>
      <SESSION_CURRENT_DATABASE>Cubes</SESSION_CURRENT_DATABASE>
      <SESSION_START_TIME>2007-10-19T12:54:58</SESSION_START_TIME>
      <SESSION_ELAPSED_TIME_MS>34141</SESSION_ELAPSED_TIME_MS>
      <SESSION_LAST_COMMAND_START_TIME>2007-10-19T12:55:09</SESSION_LAST_COMMAND_START_TIME>
      <SESSION_LAST_COMMAND_END_TIME>2007-10-19T12:55:07</SESSION_LAST_COMMAND_END_TIME>
      <SESSION_LAST_COMMAND_ELAPSED_TIME_MS>23438</SESSION_LAST_COMMAND_ELAPSED_TIME_MS>
      <SESSION_IDLE_TIME_MS>0</SESSION_IDLE_TIME_MS>
      <SESSION_CPU_TIME_MS>390</SESSION_CPU_TIME_MS>
      <SESSION_LAST_COMMAND>SELECT
HIERARCHIZE( { [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[00:00 (1:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[01:00 (2:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[02:00 (3:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[03:00 (4:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[04:00 (5:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[05:00 (6:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[06:00 (7:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[07:00 (8:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[08:00 (9:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[09:00 (10:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[10:00 (11:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[11:00 (12:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[12:00 (1:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[13:00 (2:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[14:00 (3:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[15:00 (4:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[16:00 (5:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[17:00 (6:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[18:00 (7:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[19:00 (8:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[20:00 (9:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[21:00 (10:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[22:00 (11:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&amp;[23:00 (12:00 p.m.)] } )
ON COLUMNS,
{ [Measures].[Gross Sales], [Measures].[Gross Sales LY] }
ON ROWS
FROM [Sales]
 WHERE ( [Org Hierarchy].[Hierarchy - Org Hierarchy].&amp;[1000001], [Date].[Hierarchy - Calendar Week].[Date - Calendar - Week].&amp;[2007 - (09/23/07 - 09/29/07)] )</SESSION_LAST_COMMAND>
      <SESSION_LAST_COMMAND_CPU_TIME_MS>109</SESSION_LAST_COMMAND_CPU_TIME_MS>
    </row>
 </root>
</return>

Each <row></row> corresponds to a session. You can see the SPID (<SESSION_SPID>), user (<SESSION_USER_NAME>), the last command, etc. This is info can be the basis of a session monitoring/auto kill process (many of us have written similar applications in SQL Stored Procedures for SQL Server). We could even get more sophisticated and have a .NET application that runs this, applies a transform and outputs something far more readable and extend it with cancel/kill functionality, etc. Most of the information I found while researching this alludes to using the .NET application approach. However, I wanted something simpler. 

We take the <SESSION_SPID> value from the output of the DISCOVER_SESSIONS command and plug it into Command 2’s <SPID> value. Notice I use the <CancelAssociated> options with a value of 1 (true). This cancels the process and any associated processes with the SPID in question. There are a host of options with the Cancel command and I am not going to cover them here. MSDN has fairly comprehensive documentation on it as well as the DISCOVER command.

Print | posted on Tuesday, January 22, 2008 6:47 PM |

This article is part of the GWB Archives. Original Author: James Rogers

Related Posts