Continuing my series on Power BI model memory errors (see part 1 and part 2), in this post I will look at the Command Memory Limit which restricts the amount of memory that XMLA commands like Create, Alter and most importantly Refresh can use.

If you’ve ever been told that your semantic model should consume less than half the amount of memory available to it because memory consumption can double during a full refresh, then that is because of the Command Memory Limit. Every time a model is refreshed in Power BI, that refresh is initiated by running a Refresh command. During the refresh a copy of the model is created in the background and it is the copy that is refreshed; when the refresh is completed, Power BI deletes the original version of the model and replaces it with the copy. While the refresh is in progress, the memory consumed by this copy of the model and all the operations needed to load data into it (including any Power Query queries used to get data from your data sources and to transform that data) is associated with the Refresh command. The Command Memory Limit specifies how much memory the Refresh command is allowed to use.

The good news is that there is an excellent, detailed explanation of the Command Memory Limit in the docs here which I recommend you read before continuing:

https://learn.microsoft.com/en-gb/power-bi/enterprise/troubleshoot-xmla-endpoint#resource-governing-command-memory-limit-in-premium

What it says is that the amount of memory that XMLA commands like Refresh can use is the maximum allowed size for a semantic model for the capacity you’re using (as documented in the table here in the Max Memory column) minus the amount of memory the semantic model is using when the command starts.

Let’s look at an example of an error caused by exceeding the Command Memory Limit.

I created an Import mode semantic model whose total size was 3.3GB, and which consisted of a single partitioned table with 20 columns, each of which contained random decimal numbers. I refreshed this model on an F64 capacity where the maximum allowed memory per model is 25GB and the refresh succeeded.

How much memory did the Refresh command use? There are two ways to find out. For a while now you have been able to get the approximate peak memory usage during a refresh (along with the approximate peak memory usage just for Power Query queries, which is a subset of this figure) from the Command End event associated with that refresh in Profiler and Log Analytics; I blogged about this here. However the new Execution Metrics event in Profiler and Log Analytics makes the same information even easier to extract. Here’s what the Execution Metrics event for the refresh looked like in this case:

{
	"timeStart": "2024-05-26T17:18:29.984Z",
	"timeEnd": "2024-05-26T17:26:06.577Z",

	"durationMs": 456593,
	"directQueryExecutionTimeMs": 6068,
	"vertipaqJobCpuTimeMs": 448938,
	"mEngineCpuTimeMs": 362094,
	"totalCpuTimeMs": 1565781,
	"executionDelayMs": 370,

	"approximatePeakMemConsumptionKB": 6074967,
	"mEnginePeakMemoryKB": 530688,

	"tabularConnectionTimeoutMs": 18000000,

	"commandType": 2,
	"refreshParallelism": 6,
	"vertipaqTotalRows": 15000000,
	"qsoReplicaVersion": 133612179663049757,
	"intendedUsage": 2
}

[Note: if you’re looking in Profiler or Log Analytics you’ll see a lot of Execution Metrics events. Typically the Execution Metrics events for a refresh will be generated immediately after the Command End event for that refresh, but to be sure you should look for matching values in the RequestId column (in a Profiler trace) or the XmlaRequestId column (in Log Analytics) to associate an Execution Metrics event with a Command End event.]

The metric to look at above is approximatePeakMemConsumptionKB and it shows the refresh used about 6,074,967KB or 5.8GB at its peak – a lot more than double the size of the model before or after the refresh. I designed the model specifically for this to happen (20 columns of random decimal numbers is not easy to compress) and in most cases the memory usage will be lower relative to the size of the model.

I then scaled the capacity down to an F16 which only has a memory limit of 5GB for semantic models and refreshed again. As you would expect, the refresh failed with the following error:

Resource Governing: This operation was canceled because there wasn’t enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory 1780 MB, memory limit 1779 MB, database size before command execution 3340 MB. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more. The current operation was cancelled because another operation in the transaction failed.

This is the error message associated with hitting the Command Memory Limit (the associated error number is -1052901373). What this message is saying is that the model was consuming 3340MB (3.3GB) before the refresh started, then the refresh itself reached a maximum of 1780MB (1.7GB) but at that point it was cancelled because 5GB-3.3GB=1.7GB. Here’s the Execution Metrics data for the refresh Command:

{
	"timeStart": "2024-05-27T10:11:53.985Z",
	"timeEnd": "2024-05-27T10:12:56.834Z",

	"durationMs": 62849,
	"directQueryExecutionTimeMs": 3167,
	"mEngineCpuTimeMs": 29813,
	"totalCpuTimeMs": 80375,
	"executionDelayMs": 0,

	"approximatePeakMemConsumptionKB": 1821523,
	"mEnginePeakMemoryKB": 518648,

	"tabularConnectionTimeoutMs": 18000000,

	"commandType": 2,
	"errorCount": 2,
	"refreshParallelism": 4,
	"vertipaqTotalRows": 933137,
	"intendedUsage": 2
}

So is it even going to be possible to refresh this model on an F16 capacity given that refreshing it requires 5.9GB? Well the most memory efficient way of refreshing a semantic model without changing it is to clear all the data out from it first, then refresh just the data in each partition in each table one at a time, then do a recalc, all in separate transactions – which is exactly what the partialBatch commit mode of the Enhanced Refresh API (which I blogged about recently here) does if you set its max_parallelism property to 1. So I tried this – and it failed again ☹️. Unsurprisingly the first refresh operation of type ClearValues ran successfully and peaked at 0.01GB of memory, after which the size of the model in memory would have been negligible. Then the refresh of type DataOnly for first partition in the table was successful but the refresh operation peaked at 3.4GB. The second partition refresh of type DataOnly then failed; I assume it would have also peaked at 3.4GB but the error message told me that the model was already 2.4GB in size when the refresh started so the memory limit for this refresh was 2.6GB.

If you’re hitting the Command Memory Limit and you don’t want to increase the size of your capacity but you are willing to make changes to your model, then there could be several ways to reduce the amount of memory used during a refresh – but in order to understand which method will work you will need to know what is using memory during the refresh and that isn’t easy. Using the partialBatch commit option with the Enhanced Refresh API does at least split the refresh out into its constituent parts and makes it easier to see at which stage the refresh fails. In this case refresh failed at the DataOnly stage so creating more, smaller partitions could help. Following the steps for reducing model size given in blog posts like this one by Nikola Illich will also help a lot to reduce memory consumption during a refresh. Other common culprits for high memory consumption during a refresh include calculated columns and tables, or Power Query queries that buffer large amounts of data in memory (for example because of transformations that sort or aggregate data and which do not fold). Therefore following Roche’s maxim and doing all your transformations and creating the equivalent of your calculated columns and tables in your data source, before the data is loaded into Power BI, will help. If you do have to use calculated columns and tables then you should look at tuning your DAX expressions so they use less memory. I’ll look at some examples of how to tune your Power Query queries or DAX expressions to reduce memory in future posts.

In the Power BI Service, Import mode models are stored offline and paged into memory only when they are needed – for example because someone runs a report that uses the model as its source. As discussed in my last post, though, there’s a limit on the amount of memory that a model can use which varies by the size and type of capacity you’re using using. There’s also an extra complication in that Import mode models are stored offline in a compressed format that means the Power BI Service doesn’t know exactly how much memory will be needed if the entire model needs to be held in memory. As a result there is an additional limit enforced on the size of the offline copy of Import mode models to ensure they don’t use too much memory when they are eventually paged in.

This limit can be configured in the Admin Portal by setting the Max Offline Semantic Model Size property (still shown as the Max Offline Dataset Size in the UI at the time of writing):

The default value of this property is 0, which means that the limit will be set to the maximum allowed value for the capacity SKU you’re using. Different maximum limits are enforced if you have turned on the Large semantic model storage format option for your model and if you haven’t. The maximum limits for the Large model format are the same as the limits on the maximum amount of memory that a model can use, as listed in the table here; for the Small model format they are published here. You can also set the property to a value that is lower than the allowed limit if you want to control the size of the models that your developers can publish – smaller models generally use fewer CUs during refresh or querying, which could help reduce the load on your capacity.

To test what happens when you exceed this limit I created a new F16 capacity (which has a maximum offline model size of 5GB), and then created a workspace on it containing an Import mode Power BI semantic model using the Large model format that was 3.3GB in size. I then scaled my capacity down to an F2 and then tried running a DAX query against the model. The Max Offline Semantic Model Size limit for an F2 capacity for models with the Large model format enabled is 3GB, which is less than the size of the model I had created. As a result the query returned the following error message:

Database ‘xyz’ exceeds the maximum size limit on disk; the size of the database to be loaded or committed is 3500853108 bytes, and the valid size limit is 3221225472 bytes. If using Power BI Premium, the maximum DB size is defined by the customer SKU size (hard limit) and the max dataset size from the Capacity Settings page in the Power BI Portal.

If you’re getting this error then you need to reduce the size of your model, for example by reducing the number of rows in your tables, removing unused columns, or reducing the number of distinct values in the columns you keep. You can find more suggestions on how to reduce model size here.

[Thanks to Akshai Mirchandani for answering my questions on this topic]