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">
DISCOVER_SESSIONS</RequestType>
<RestrictionList />
<PropertyList />
Using ASSP:
Call ASSP.DiscoverSessions
SQL 2008 Using DMV:
Select * from $system.DiscoverSessions
Command 2 (Kill it!):
Old School (Through XMLA Window)
<Cancel xmlns="">
121672</SPID>
1</CancelAssociated>
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:sequenceminOccurs="0"maxOccurs\="unbounded"\
xsd:elementname="row"type\="row" /
</xsd:sequence>
</xsd:complexType>
</xsd:element>
xsd:restrictionbase="xsd:string"\
</xsd:restriction>
</xsd:simpleType>
xsd:complexTypename="xmlDocument"\
</xsd:sequence>
</xsd:complexType>
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:sequence>
</xsd:complexType>
</xsd:schema>
<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>
Each 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 value. Notice I use the 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.