Geeks With Blogs
James Rogers BI from the trenches...

 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!):

Old School

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

 <RequestType>DISCOVER_SESSIONS</RequestType>

 <Restrictions>

    <RestrictionList />

 </Restrictions>

 <Properties>

    <PropertyList />

 </Properties>

</Discover> 

 

Using ASSP:

 

Call ASSP.DiscoverSessions

 

 

SQL 2008 Using DMV:

 

Select * from $system.DiscoverSessions

 

 

Command 2 (Kill it!):

Old School (Through XMLA Window)

 

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

 <SPID>121672</SPID>

 <CancelAssociated>1</CancelAssociated>

</Cancel>

 

Using ASSP:

 

Call ASSP.CancelSpid(121672)

 

 

Using ASCMD:

 

ASCMD.exe –S localhost –Q “Call ASSP.CancelSPID(121672)”

 

 

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 (from XMLA window output – using DMV or ASSP is much easier to read and is my preference):

 

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

 <root xmlns="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.

Posted on Tuesday, January 22, 2008 6:47 PM Analysis Services | Back to top


Comments on this post: Finding and killing SSAS Sessions

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
James,
using SSAS stored procedure project (http://www.codeplex.com/ASStoredProcedures/) you can use different methods discovering sessions, metadata and so on. As example, you can use CALL ASSP.Discover("DISCOVER_SESSIONS") as well as CALL ASSP.CancelSPID(457703).
Another approach is to use windows form application ActivityViewer from Analysis Services samples.

Ramunas
Left by Ramunas Balukonis on Feb 14, 2008 9:09 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Thanks for this info James, it's very helpful. I work at different client sites and rarely need to perform this task, but when you do, there is no time to setup new apps etc for what should be built in functionality.

I think there may be typo's in your Commands: should there be a space before xmlns=?

One trick to improve readability is to copy the XML output, paste it into a text file then open that text file using Excel 2007+ Data / Get External Data / From XML Data Import. This does a reasonable job of flattening out the XML into a table some initial junk for the XSD, but then a row for each user session. You can then use Excel to sort/filter, compare different snapshots etc, etc.
Left by Mike Honey on Nov 07, 2010 3:35 PM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Mike,

You are correct. The cut-and-paste seemed to remove those spaces. I will add them in. Thanks for the tip on copying XML - will use it in the future.

Thanks!
Left by James Rogers on Nov 08, 2010 8:38 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Here are some other articles on Killing SSAS Sessions: http://ssas-wiki.com/w/Articles#Killing_SSAS_Sessions
Left by Sam Kane on Feb 23, 2011 4:05 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
I tried to kill session using XMLA script, but session remains. What can be the problem?
Left by Tatyana on Feb 08, 2012 2:11 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Tatyana,

We have experienced this problem as well, especially in SSAS 2005. Microsoft informed me that sometimes a query can get into an internal process and does not come out to check for cancellation until it completes. This was supposedly improved in 2008 and beyond, in that it "surfaces" more often from these processes to check for cancellation, etc... I have found in 2008 and beyond this to be true as our cancel commands tend to get better responses, even from the worst of queries.

Thanks,

James
Left by James Rogers on Feb 08, 2012 2:17 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Hi, James! Thank you for your prompt answer.
I' on SSAS 2008 R2, I have some problems with my cube, running dimension process that stucks all the time, then I try to kill session with no effect. Only restarting Analysis Services helps, and it's not a good solution. Do you have other ideas?
Left by Tatyana on Feb 08, 2012 2:40 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Tatyana,

It could be hanging while processing for a variety of reasons. I would definitely be running SQL Profiler while the processing is taking place to try to figure out where it is hanging. It could be a query against a relational source. Another problem, if you are doing a ProcessUpdate on the dimension and you have a lot of partitions, Analysis Services tends to single-thread the Aggregation/Index clearing for ProcessUpdate. In that case, you might want to do a ProcessClearIndexes on your measure groups before doing the ProcessUpdate on the Dimension. After the ProcessUpdate completes you will want to do a ProcessIndex on those same measure groups to rebuild the aggregations and indexes.

Thanks,

James
Left by James Rogers on Feb 08, 2012 2:46 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
Another thing to consider is that if you are cancelling a Processing command, SSAS may have to rollback any changes it has started to commit at that point and that could take a while, depending on the size and scope of those changes.
Left by James Rogers on Feb 08, 2012 2:54 AM

# re: Finding and killing SSAS Sessions
Requesting Gravatar...
SQL profiler shows no-thing-at-all!! I know that rollback takes time, but an hour for one dimension? Dim_Country? 287 rows? No changes except translations for hierarchy? It looks like I'm missing something...
Can it be connected to the fact that I deployed existing cube to new Database as "deploy changes only" and "no process" and after deployment I'm trying to process the cube as "process default".
Left by Tatyana on Feb 08, 2012 3:06 AM

Your comment:
 (will show your gravatar)


Copyright © James Rogers | Powered by: GeeksWithBlogs.net