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].&[00:00 (1:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[01:00 (2:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[02:00 (3:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[03:00 (4:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[04:00 (5:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[05:00 (6:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[06:00 (7:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[07:00 (8:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[08:00 (9:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[09:00 (10:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[10:00 (11:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[11:00 (12:00 a.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[12:00 (1:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[13:00 (2:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[14:00 (3:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[15:00 (4:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[16:00 (5:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[17:00 (6:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[18:00 (7:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[19:00 (8:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[20:00 (9:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[21:00 (10:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[22:00 (11:00 p.m.)], [Quarter Hour].[Time - Time-Half Hour-Hour].[Hour].&[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].&[1000001], [Date].[Hierarchy - Calendar Week].[Date - Calendar - Week].&[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 |