Monday, November 17, 2008

Cancelling runaway MDX queries on SQL Server 2008

It is now very easy to cancel Analysis Services MDX queries in SQL Server 2008. Here is a command you can execute from an MDX window to see what's running.
By the way, if you have a run away query, you don’t need to restart AS. You can determine what queries are running and consuming resources with

select * from $system.discover_commands

You can then cancel any of the executing queries with the following xmla command. Just put in the appropriate SPID.

It's that easy. You could even use Reporting Services to display the output of the Discover_Commands and link it to the Cancel command. That way, you could cancel queries through a web browser. Just make sure that you have integrated security so that a non administrator couldn't use the report to cancel others commands.

No comments: