Improved resource governance for Azure Analysis Services
Published date: March 06, 2020
Today, we are announcing the release of two enhancements concerning the memory governance options of Azure Analysis Services. Specifically, the enhancements improve the accuracy of the Memory\QueryMemoryLimit server property and the DbpropMsmdRequestMemoryLimit connection string property. We introduced these properties in June 2018 to limit the memory spools built by DAX queries. The enhancements now cover MDX queries as well, so they effectively cover all queries, and they are no longer limited to memory spools. With full query coverage and improved accuracy, you get more control over the memory consumption related to expensive queries so that you can ensure a high quality of service for all users sending queries to your Azure Analysis Services server.
The Memory\QueryMemoryLimit server property enables you to control expensive queries that result in significant materialization. If the query hits the limit specified through this property, Azure Analysis Services cancels the query and returns an error to the caller. This helps to reduce the impact on other concurrent users of the server. In the screenshot below, the Memory\QueryMemoryLimit server property limits memory consumption per query to 20%. Values up 100 refer to percentages, more than 100, it's in bytes, and a value of 0 means no limit. As indicated in the screenshot, make sure you enable the show Advanced (All) Properties checkbox to display this setting. For more information about how Azure Analysis Services allocates and deallocates memory, see the Memory properties article in the product documentation.
An Azure Analysis Services client application can choose to further reduce the memory allowed per query by specifying the DbpropMsmdRequestMemoryLimit command property. As documented under Special-purpose parameters in the Connection String Properties (Analysis Services) reference article in the product documentation, you must specify the DbpropMsmdRequestMemoryLimit value in kilobytes (KB). The memory limit used will be the smaller of the two values when they’re both specified
Note that these enhancements are not available in the SQL Server Analysis Services 2019 release.