…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:
- 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 - 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:
- 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:
- 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.
Chris – is there a 2005 version of this?Sean.
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.
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
Yes, take a look at DiscoverSessions and DiscoverConnections here:http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Discover&referringTitle=XmlaDiscover
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
I\’m fairly sure it\’s there somewhere, but you\’d have to check.
Thank you Chris, this is very useful indeed, along with your other post re issuing cancel command to a particular spid using SSRS.
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.
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.
ok thanks for ur reply will try doing that
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.
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.
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.
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
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!
Killing a specific spid would be a broken result for the end user, killing a connection also be a broken result. So why not killing a connection it self ? please explain…
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.
Hi Chris, Im guessing resource governor has never made it into any version of SSAS or AAS ?
No, although some helpful server options have been released: https://blogs.msdn.microsoft.com/analysisservices/2018/06/08/new-memory-options-for-analysis-services/
Hello Chris Webb, is have found your article as we have still cases of long running queries which have a lot of memory consumption. And would like to test your example. But your Link to the Package is broken. So i would like to ask if you could update the Link again. Thx
Done!