Killing sessions automatically with SSIS

…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:!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:
  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:
  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:

20 thoughts on “Killing sessions automatically with SSIS

  1. 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

  2. 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

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

  4. 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.

      • Hi Soumya,

        Did you find out how to pass a variable in XMLA script under Analysis Services Execute DDL task which we loop from For each Loop container.

        I need it urgently. Would be great if you could help me out.

        Thanks in advance.

  5. 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.

  6. 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?

    • 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!

    • No particular reason, though I’m pretty sure that if you kill the connection the session can hang around in memory for some time afterwards, so it may be better to kill the session and release that memory.

Leave a Reply

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

You are commenting using your 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