//
you're reading...
BI, SQL Server, SSAS

Finding and killing SSAS Sessions

If you have a reasonably large Analysis Services implementation 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. I recently ran into this very issue trying to apply a simply xmla script to one of my development environment. Which subsequently could not be applied due to resource contention issues. As Analysis Services does not have a native tool in SQL Management Studio for monitoring session activity and/or killing connections. Below is some sample code I came up with, obviously there is code can get from Microsoft but I wanted to keep it simple. After digging around a bit I found some pieces of code to get and kill SSAS sessions\spids.

Find Sessions

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

Command 1 (Session Monitoring/Find it!):

XMLA:

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

<RequestType>DISCOVER_SESSIONS</RequestType>

<Restrictions>

<RestrictionList />

</Restrictions>

<Properties>

<PropertyList />

</Properties>

</Discover>

Using SSAS Stored Procedures(ASSP):

Call ASSP.DiscoverSessions

SQL 2008 Using DMV:

Select * from $system.DiscoverSessions

Kill Sessions:

XMLA:

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

<SPID>121672</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

Using SSAS Stored Procedures:

Call ASSP.CancelSpid(121672)

Using ASCMD:

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

 

 

A lot of good info is in the output of the Discover Seessions 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”&gt;

<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 the cancel command <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.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: