none
Setting to cancel long running MDX queries RRS feed

  • Question

  • Is there a server/DB level setting where I can reduce the time an MDX query will be allowed to run.

    We have several users leveraging the model via Excel and creating ad-hoc reports. Off late we are seeing performance issues (CPU spikes to 99% and stays there for long periods) that could be because users are using the model incorrectly. 

    Would like to know if there is a setting that we can control so that a query cancels if a result is not returned in say 10-15 secs.

    Thursday, January 11, 2018 6:07 PM

Answers

All replies

  • Connect to Analysis Service instance in SQL Server Management Studio.

    Right-click the instance and go to Properties, then to the General menu choice on the uppler left.

    At the bottom of the screen, click Show Advanced (All) Properties

    Scroll down the list to ExternalCommandTimeout - default is 3600 Seconds (60 minutes)

    Changed to desired timeout.

    I do not believe a restart is necessary


    Thomas LeBlanc twitter ( @TheSmilingDBA )

    Thursday, January 11, 2018 8:16 PM
  • So the ExternalCommandTimeout is the timeout for processing operations,when SSAS queries an external data source to get the data to populate the cube. There is another property simply called ServerTimeout which is the timeout for client queries (MDX/DAX/DMX) (see https://docs.microsoft.com/en-us/sql/analysis-services/server-properties/general-properties ).

    Although in my experience this property is not 100% reliable/effective. I'm actually using a technique similar to the one I blogged about here http://geekswithblogs.net/darrengosbell/archive/2014/03/31/how-to-build-your-own-ssas-resource-governor-with-powershell.aspx

    The advantage of the manual script approach is that we only run it during business hours. We found there there were a small number of people trying to do "bad things" like extract millions of rows from our cubes by doing massive crossjoin queries that would take up all the CPU resources during the middle of the day. These people often then contact the support team and they either organize access to the raw data directly or they start up a project to build a dedicated cube or they schedule a report to run outside of business hours.

     

    http://darren.gosbell.com - please mark correct answers

    Thursday, January 11, 2018 9:49 PM
    Moderator
  • Hi VivDev,

    Thanks for your question.

    The most common approach to cancel long running MDX queries is to use the SSAS server properties. ServerTimeout is the property, towards the end of the Advanced Properties list, that is relevant for us.  Any MDX query on any OLAP database on the server that does not complete within the time set by this property will be terminated.There's no flexibility with ServerTimeout - one setting affects all MDX queries on the server regardless of origin, destination or anything else.  99% of the time this is a sufficient and effective solution for terminating long running queries with minimal fuss.

    You can follow the solution provided by Darren, if you want to cancel queries that running more than 10 secs. Below is similar blog talking about this with a whole solution,  you might also want to refer to:
    http://timlaqua.com/2011/04/dealing-with-long-running-ssas-queries-using-powershell/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 12, 2018 2:09 AM
    Moderator