SSAS 2019 has a very useful new memory setting called QueryMemoryLimit that can be found under Advanced Prperties. While the online documentation says this setting is only available in Azure AS, I can see it in SSAS 2019 CTP.
The purpose of this setting is limit the amount of memory any single query can take. This setting is extremely useful when you want to limit the amount of memory consumption per query for queries across the board. Before this setting, it was possible to have an extremely poorly written query eat up all of a server’s memory and bring all other queries down to a halt. You can see an example of a such a query and SSAS memory settings in my previous post here.
Testing QueryMemoryLimit
To test how this setting , I ran the same poorly written query I had used in my previous post against a server that has 16GB of RAM and collected some stats from SSAS Permon counters in a CSV file.
The default value of QueryMemoryLimit for SSAS 2019 is 50. Since this setting is stated in percentage (if under 100), this means QueryMemoryLimit is 8GB on this server.
This is what happened when I ran the query:
In summary, the query’s memory consumption reached 8GB and got killed with the following error message:
Executing the query …
Resource Governing: The query exceeded the maximum memory allowed for queries executed in the current workload group (Requested 8316096KB, Limit 8316094KB)
This is a significant improvement over previous versions of SSAS. Without this setting, you would have seen the following graph instead. Here the query pushed SSAS’s total memory consumption past the Memory Limit Hard KB and caused all connections (all queries vs. just itself) to close with an out of memory error.
More Details
Unlike other SSAS memory limits, the QueryMemoryLimit is not available directly from a Perfmon Counter. Perhaps a counter will be added in future. For now, it’s value can either be calculated based on total amount of memory on the server or by running a query that passes the limit and looking at the error message. (or if you set it to a value over 100 which will be an exact amount in bytes from SSMS)
Looking at the previous example, since this query ran in an isolated environment, I can use the data reported in Perfmon to estimate the query memory consumption and compare it to the limit set in the properties to see if the query got killed exactly or close to the limit.
Here is how memory counters in Perfmon looked like before I ran the query. Note that Memory Usage KB is 898,884 KB. Since there are no other queries running, you can assume that all of this is SSAS’s base memory consumption (VertiPaq data structures, my session, …)
Here is a snapshot of the same counters a few seconds before the query gets an error. Notice how Memory Usage KB is almost at 8GB. A few seconds later the query is killed.
Finally the query passes 8GB and is killed by SSAS and memory usage is back down.
Using the data recorded by Perfmon, I can estimate the query memory consumption as following. Keep in mind that these are estimates as the base SSAS memory consumption can change slightly over time without any queries.
A = Estimated Base Memory Usage KB (SSAS) memory = 898,884 KB
B = Estimated Memory Usage KB when query errored out = 9,231,448 KB
EM = Estimated max Query Memory Usage = A – B = 8,332,564 KB
L1 = QueryMemoryLimit as set in propeties= 8 GB = 8,388,608 KB
L2 = Limit reported in the error message = 8,316,094 KB
AM = Actual max Query Memory Usage from error message = 8,316,096 KB
You can see the actual QueryMemoryLimit in the message which is a little bit (512KB) less than the setting’s value of 8GB.
Resource Governing: The query exceeded the maximum memory allowed for queries executed in the current workload group (Requested 8316096KB, Limit 8316094KB)
Summary
QueryMemoryUsage is an extremely valuable addition to SSAS 2019 and Azure AS. While it’s default value is set to 50, you should look into changing that to a fixed value if the server has a large amount of memory. If a server has over 100 GB of RAM, the default value allows a query to take as much as 50 GB of RAM before it is killed. This is way too much memory for most queries. If you do have a query that is taking this much memory, chances are that either the query has a mistake in it or can be rewritten better.
Whatever QueryMemoryUsage is set to, it can help you identify the heavy queries that go past the limit assuming the users will complain about their queries getting killed. This is valuable from an admin point of view if you manage a server with a large number of users.
One thought on “New QueryMemoryLimit setting in SSAS 2019”