Finding and killing SSAS Sessions

27/04/2011

תגובה אחת

I have found a great BLOG at http://geekswithblogs.net/ManicArchitect/archive/2008/01/22/118801.aspx 

it discuss the way to find and Kill an SSAS session:

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)”

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

תגובה אחת

  1. Clemons12/11/2012 ב 05:04

    This is my first time pay a visit at here
    and i am actually impressed to read all at alone place.

    Tristan

    הגב