We all know about the query from hell - the one executed by a departmental user that pulls the entire contents of the cube down into Excel and 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 queries that are run against Analysis Services using the ServerTimeout property in msmdsrv.ini. However you should be mindful that all queries could possibly time out after two minutes. On the other hand, you can wait for users to provide feedback that the server is really slow, and then take a look at what's running and kill sessions manually.

Neither option seems satisfactory. It would be ideal to kill sessions automatically but at the same time apply some rules such as:

• If the query has run for more than 30 seconds and the user is a departmental user then kill it
• If the query has run for more than five minutes and the user is the CEO, send the DBA an email
• If the DBA role is running a long query, do nothing.

What better way to implement this logic than in an SSIS package? Here is a proof of concept:

1. First of all, you will need a way of finding the sessions that you 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. Then take each of these three queries and run them in an OLEDB Source in your SSIS dataflow, and join the resultsets. You could then implement logic you want 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") You could then store the SPIDs of the sessions you want to kill in a Recordset destination.

The dataflow looked like this:

3. With the resulting recordset stored in a variable, you can then loop over the recordset in a For each task in your 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. It is very easy to do. Of course you could add loads more functionality to this basic package – including sending 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.

Author Credits

Chris Webb

This wiki article was adapted from a blog post by Chris Webb.

Chris Webb is an independent consultant specialising in SQL Server Analysis Services cube design, tuning and troubleshooting and the MDX query language. He’s a co-author of “MDX Solutions with Microsoft SQL Server 2005 Analysis Services and Hyperion Essbase” and a regular speaker at user groups and conferences in the UK and Europe. He can be contacted via his company website, Crossjoin.co.uk.

His online presences include:

• Blog - http://cwebbbi.spaces.live.com/

Related Reading

For more information about session management, check out these articles:

• Chris Webb's Blog Post on Killing Sessions with SSIS
• Current Activity Query - wiki article by Jason Massie with an sp_who2 replacement that keeps refreshing every minute.