...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:
Source Click Here.
Post a Comment