Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in Excel and were trying to get everything in a single query and we were able to educate these users to use many smaller pivot tables rather than one massive one. One of the biggest single queries during this time ran for 53 minutes and returned somewhere in the vicinity of 190,000 rows!
At the time this was occurring we needed to find some way of stopping these queries from hogging all the resources. I was able to see these queries by running a “SELECT * FROM $SYSTEM.DISCOVER_COMMANDS” query and if they had been running for longer than 10 minutes (that’s right, over 10 minutes for a tabular query) I could manually execute a Cancel command using XMLA, but this soon becomes tedious and potentially error prone.
So I developed a little PowerShell script which shows all the active queries with their elapsed time, the query that was executed and the user who is running it. It also shows the elapsed as a formatted string in hh:mm:ss.nn format and sorts the results to that the longest running queries are at the top.
But this script is not display only. If you select one or more rows (you can select multiple rows with the usual Crtl / Shift click mechanisms) and then click the OK button the script will issue cancel commands for each of the selected sessions.
I’ve attached a link to the full script below, in a future post I’ll show how I took the information that I learned from this script and built it into a completely automated solution.
There are 2 variables at the top of the script that you will need to change in order to run this in your environment. One is the name of the SSAS instance to monitor. The other is the minimum elapsed time threshold (which is currently set to 0 so that it shows all queries). If there are no active queries over the threshold time the script ends quietly.
You can download the full script from my OneDrive here
Update: Note that although the blog post talks about an issue with a Tabular instance this script will workagainst both Tabular and Multidimensional instances