Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Killing sessions automatically with SSIS

with 17 comments

…or, what to do while you’re waiting for Analysis Services to get a proper resource governor (fingers crossed for the next version).

We all know about the query from hell, that one query that the idiot from Finance insists on running to try to pull the entire contents of the cube down into Excel even though you’ve told him a hundred times not to do it, that one query that brings your server to its knees just at the worst possible moment. What can you do about it? Well, you can set a general timeout on all all queries that are run against Analysis Services using the ServerTimeout property in msmdsrv.ini, but this is something of a blunt instrument – while I don’t mind everyone else’s queries timing out after two minutes, I certainly don’t want mine to do that and I definitely don’t want that to happen to the CEO’s queries either. On the other hand, you can wait for someone to phone you up and complain that the server’s really slow, then take a look at what’s running and kill sessions manually using something like the technique described here:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1652.entry

But to me neither option seems satisfactory. I want to be able to kill sessions automatically but at the same time apply some rules of my own: if the query has run for more than 30 seconds and the user is that idiot from Finance then kill it; if the query has run for more than 5 minutes and the user is the CEO send me an email so I can gently remind him not to drag the 6-million-member Products hierarchy onto rows in Excel; if I’m running the query, do nothing. What better way to implement this logic than in an SSIS package? I put together a proof of concept and this is how I did it:

  1. First of all, I needed a way of finding the sessions that I want to kill. As we’ve already seen, you can find a list of currently executing commands by using the following AS2008 DMV:
    select * from $system.discover_commands
    But you may need more information in order to make the decision on whether to kill or not so it could also be useful to run the two following DMV queries to find out more about sessions and connections:
    select * from $system.discover_sessions
    select * from $system.discover_connections
  2. I then took each of these three queries and ran them in an OLEDB Source in my SSIS dataflow, and joined the resultsets. I could then implement the logic I wanted to use to decide whether a session should be killed or not in a Conditional Split, for example using an expression something like this:
    (COMMAND_ELAPSED_TIME_MS > 30000)  &&  ([SESSION_USER_NAME] != "MyPC\\ChrisWebb")
    I could then store the SPIDs of the sessions I wanted to kill in a Recordset destination. The dataflow looked like this:
    killqueriesdf
  3. With the resulting recordset stored in a variable, I could then loop over the recordset in a ForEach task in my control flow and use an Analysis Services Execute DDL task to run the XMLA Cancel command to kill each query:
    killqueriescf 
  4. The last step is to schedule the package to run frequently, perhaps every 30-60 seconds, using SQL Server Agent.

Very easy. Of course you could add loads more functionality to this basic package – as I said, you may want to send an email to someone after you’ve killed their session explaining what has happened, or you may only want to kill a session if there are other users running queries at the same time.

You can download the VS2008 from here:

http://cid-7b84b0f2c239489a.skydrive.live.com/embedrowdetail.aspx/Public/KillQueries.zip

Written by Chris Webb

December 4, 2008 at 9:03 pm

Posted in Analysis Services

17 Responses

Subscribe to comments with RSS.

  1. Chris – is there a 2005 version of this?Sean.

    Unknown

    December 12, 2008 at 2:53 pm

  2. Not that I\’ve actually done, but it would be easy to build. Since you don\’t have DMVs in 2005 you would have to use Darren Gosbell\’s stored procs that do the same thing, available here:http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=HomeEverything else would be exactly the same I think.

    Chris

    December 12, 2008 at 4:40 pm

  3. Hi Chris,I looked the asstoredprocedures page and i found the CancelSession(sessionGuid) method. This method as the sessionGuid argument, how i can get the list of all the sessions of a ssas solution? is there another method in the asstoredprocedures that returns that list?Regards, Rodrigo

    Rodrigo

    December 16, 2008 at 12:02 pm

  4. Yes, take a look at DiscoverSessions and DiscoverConnections here:http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Discover&referringTitle=XmlaDiscover

    Chris

    December 16, 2008 at 10:48 pm

  5. Hi Chris, I want to know if is possible to get the parameter COMMAND_ELAPSED_TIME for each session using AASP assembly?TksRegards Carlos Rodrigues

    Unknown

    December 17, 2008 at 4:15 pm

  6. I\’m fairly sure it\’s there somewhere, but you\’d have to check.

    Chris

    December 18, 2008 at 11:55 am

  7. Thank you Chris, this is very useful indeed, along with your other post re issuing cancel command to a particular spid using SSRS.

    Vincent

    December 10, 2009 at 10:37 am

  8. Hi Chris,

    I really like ur blogs which helps Daytoday tasks for the SQL DBA’s and SQL BI Developers one question i had from this how can we pass a variable in XMLA script under Analysis Services Execute DDL task which we loop from For each Loop container.

    soumya samprathi

    February 4, 2011 at 2:07 pm

  9. You’ll need to dynamically generate the whole XMLA command, probably in a script task. You can’t parameterise XMLA as far as I know.

    Chris Webb

    February 4, 2011 at 2:32 pm

    • ok thanks for ur reply will try doing that

      soumya samprathi

      February 4, 2011 at 3:23 pm

  10. [...] things fist – the original thought for this came form Chris Webb's article, Killing sessions automatically with SSIS. Now on to my solution – which is basically identical in theory, just implemented in PowerShell [...]

  11. Chris, is there a similar command to kill connections? I noticed that after killing the sessions, connections seem to remain open.

    I want to kill active connections before we copy a cube from our build server to the user server as part of our daily processing.

    Ernie

    September 28, 2011 at 5:47 pm

    • Hmm, not sure – the cancel command might work with connections too. I suspect the connections would be cleaned up automatically in any case, but I’d need to check.

      Chris Webb

      September 29, 2011 at 11:51 pm

  12. […] Services doesn't have that type of functionality built into the product. Chris Webb's article on Killing session automatically with SSIS describes one approach to the problem using DMV queries in an SSIS Package. Tim Laqua's […]

  13. […] Killing sessions automatically with SSIS […]

  14. Hi Chris,
    I implemented your solution and it’s working but I want to capture COMMAND_TEXT and I am getting truncation error on COMMAND_TEXT at the OLE DB source. I have set up all the variables and email tasks and everything seems to be working as expected but COMMAND_TEXT is getting truncated at 255. I played with the “Input and Output Properties” using the advanced editor for OLE DB source. SSIS won’t let me change the length of COMMAND_TEXT in Error Output and because of this I am unable to get past the truncation issue. I can ignore truncation and run the package but I want to capture the COMMAND_TEXT. Any suggestions?
    ~Ankit

    Ankit Sharma

    August 12, 2013 at 7:22 pm

    • I just changed the OLE DB source to ADO net source and I am able to capture the complete COMMAND_TEXT. Still not sure why SSIS OLE DB source won’t let me change that column’s length from 255 to anything higher than 255. But changing the source provides the solution I was looking for. Thanks for blogging on this topic. This was great help!

      Ankit Sharma

      August 12, 2013 at 10:25 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: