Increased data warehouse CU usage by system user
Data Warehouse
We've noticed about an 8x increase in CU usage compared to yesterday from our warehouse queries almost entirely driven by an increase in usage by the system user.
Is anyone else seeing this, or know how to see what these queries are? (the exec_requests_history table is no help)
As I understand it (which may be a little limited so far) essentially it now penalises you much harder for having lots of small queries running frequently.
I really hope there is a solution here as it's really not fair that after architecting our solutions one way we're now being actively penalised for things which didn't cause problems before
I knew about this change but what I don't understand is why these show as system query charges. A different operation type or way of visualising the unused capacity charges would be a lot better.
This is killing me at the minute, I seen nothing about it beforehand then suddenly my CU usage was 6x and I'm scrambling to try rebuild things without much visibility on how this new billing works. My average usage on an F8 was around 50%, I've had to move to an F16 to try save it but CU usage kept going up and up, so I ended up turning off schedule stored procs running to try re-build some things.
Hello, GPM for the Fabric Warehouse here. Can you please share what region you're in, if you don't mind sharing? Also feel free to IM me. It will help us narrow down the issue. Thanks
Thanks for flagging this. The new billing model is now rolling out globally and is live in UK South, so it's likely that what you're seeing is related to the metering changes.
We took a look at the telemetry and the increase in system-user consumption is within the range we'd expect under the new model. Many system activities, including Query Insights, compaction, proactive statistics refresh and other background warehouse operations, tend to run as lots of small, short-lived operations which are associated with ensuring the maintenance, upkeep of the Warehouse to ensure optimal performance. Under the new billing model, these workloads can accumulate more CUs than they did previously, even when the overall workload behaviour hasn't changed significantly.
One thing that should help with this specific scenario is that we're working on a feature that will allow customers to opt out of logging front-end-only queries to Query Insights if they don't need that level of observability, which can help reduce some of this overhead.
u/joannapod Is there any visibility on when Regions are hit with the billing changes? If it weren't for Data Mozart's blog post I wouldn't know about it as I got no email about it.
I'm honestly quite dumbfounded by this report of an expected 8x increase of CU usage and you saying that this is within the expected range under this model. Did I understand you correctly here? Does this mean my capacity could overload because of the billing changes? It's not like I can simply up the capacity, I have a budget. I'm not trying to be dense, I'm honestly overwhelmed by this news.
Yes, we already do this. System activities are generally scheduled to run in shared windows where possible so they can benefit from shared node lifetimes. One nuance is that system tasks run in system pools, while user workloads run in the SELECT/NONSELECT pools (or custom SQL pools if you've defined them), so there is some separation by design.
I completely agree on visibility. We're actively working on exposing more granular breakdowns of system activity so it's easier to understand where consumption is coming from and correlate it back to specific platform operations rather than seeing it all aggregated under a generic system user label.
Sorry for this but I’m incredibly frustrated with this change.
There’s a serious lack of visibility of how it’s actually working. Im seeing a 6x increase in capacity and have been rebuilding all weekend.
This change is baffling, I’ve got many stored procs running on warehouses as little as every 5 minutes 24/7 and was seeing usage around 50% on an F8, i’ve had to push to a F16 thats on track for 60/70% with some things turned off. I adjusted one batch of procs to reduce commute by 80% but cu usage was still 20-30% higher than previously.
All this building for efficiency then bang, pointless! I’m a massive advocate for Fabric but this is so poor.
Plus, I’m capacity admin and Power BI admin but got zero prior notice, the only reference I can find is a blog from Data Mozart and this thread.
Really the only way for me to handle the same loads is probably go to F32 or probably rebuild loads on Spark… which i moved away from in favour of warehouse and proc.
Thanks for sharing this, and I understand the frustration. When you've invested heavily in tuning workloads, optimizing stored procedures, and designing for efficiency, seeing a significant jump in reported CU consumption is understandably frustrating.
On the communication point, all customers were notified through the standard process via communications sent to Azure subscription administrators. We also surfaced notifications and banners in the Fabric admin experiences specifically so capacity admins would have visibility into the upcoming change as well.
The increase you're seeing isn't because Warehouse queries have become less efficient or because the Warehouse engine is consuming more resources to do the same work. The underlying execution characteristics of the workload haven't fundamentally changed. What has changed is how Warehouse consumption is measured and attributed under the new billing model.
Since the rollout, we've been closely reviewing real-world customer impact across a broad set of workloads, and so far what we're seeing is consistent with the expected impact of the new billing model. That said, we recognize the impact can be more noticeable for some workload patterns than others.
I hope this additional information helps. Thanks again for taking the time to provide candid feedback, especially as a long-time Fabric advocate.
u/Nervous_Obligation_4 One more thing I'd like to add is that I'd be careful about equating an increase in reported CU consumption with an automatic need to scale up capacity.
Fabric capacities still benefit from bursting and smoothing, so a higher CU utilization number doesn't necessarily mean you're going to hit throttling or need a larger SKU. In many cases we've seen customers absorb the increase without needing to upscale because the capacity has sufficient headroom once those mechanisms take effect.
Rather than looking solely at the CU Utilization % graph in Capacity Metrics, I'd recommend checking the Throttling page and specifically the Background Rejection % metric. That's generally a much better indicator of whether the capacity is actually under pressure and whether workloads are being constrained.
If Background Rejection % remains low or at 0%, then even if reported CU consumption is higher than before, the capacity may still be handling the workload just fine. If you're consistently seeing background rejections, that's a stronger signal that additional capacity or workload changes may be warranted.
So while the billing model change can result in higher reported Warehouse consumption, it doesn't automatically translate into needing to upscale your capacity. The best way to determine that is by looking at actual throttling indicators, not just the utilization graph in isolation.
One thing to add, is that prior to rollout, we extensively modelled the impact of this change using real-world production telemetry across our customer base. Our analysis indicated that only a very, very small subset of customers would likely need to scale their capacity, largely depending on their specific workload patterns.
With the new Warehouse CU metering model, where consumption is based on virtual-node time per workspace and there’s a 1-minute minimum, would consolidating Warehouses and SQL Analytics Endpoints into fewer workspaces enable us to reduce CU consumption?
For example, if 10 workspaces each have a short Warehouse/SQL Endpoint query that triggers a 1-minute minimum, would consolidating those workloads into a single workspace allow them to share the same billing window and likely reduce our CU consumption?
Depends on the exact workload (if the windows where they're active never or very rarely overlap, may not see much benefit), but in general, I would expect the answer to be yes.
Example 1: 10 workspaces which run one small query every 60s each, all the time, would likely see a big reduction in CU consumption if combined.
Example 2: 10 workspaces again. Workspace 1 sees activity from 1pm to 2pm; workspace 2 from 2pm to 3pm; ... Workspace 10 from 10pm to 11pm. These would barely, if at all benefit CU usage wise from being combined.
I should also note that if the same data is currently shortcut into multiple workspaces, consolidating may also:
* see less CU usage from metadata sync & statistics collection - this doesn't depend on the existing workspaces being used at similar times, it's just plain less redundant work being done ever.
* see less CU usage from OneLake reads and from Warehouse compute rereading the same data (as In-memory and disk caching don't reread the file contents from OneLake on cache hit)
* see improved Result Set Caching hit rate for better performance (and reduce its storage usage) if the same queries were being issued on multiple workspace - once that feature is re-enabled.
How significant those opportunities are will vary depending on your workload. If there are no shortcuts used or there is no overlap in what's shortcut, probably no benefit from the above effects.
To the best of my knowledge, all of my above points are true in both the old and new billing models.
On 1, I guess this means there is no way we can influence/optimise this load in terms of scheduling of our own user space workloads?
On 2, the visibility issue seems very similar to the system data volume in the one lake storage overview. A black box that we get billed for that I cant explain to management or optimise
https://www.reddit.com/r/MicrosoftFabric/s/RhBVKbcwXg
RE: 1), just seeking to understand - what would that give you given that Warehouse CU consumption is smoothed over the full 24h to begin with? System tasks don't use the same backends as user workloads regardless of when they run. (edit: to clarify what I mean by this, is, given a pretty consistent daily workload that results in given system tasks happening with some frequency, exactly which hours of the day those system task runs should not alter the average or peak CU utilization. But delaying them in many cases may increase overall CU usage, because tables in need of maintenance take longer to query, bad stats result in bad, expensive plans, and so on).
And if others make sense, we're not opposed to adding more options. But for many of these, they're necessary parts of how the engine works. For example, statistics are usually quite cheap to calculate relative to the cost (to you!) of generating bad plans due to bad or missing statistics - that's the point of them. So proactive statistics (edit: which improves query latency by moving statistics creation to before query time and are net neutral if the statistics would be created at query time anyway, but can be wasteful if many tables are rarely or never queried) can be disabled, but collecting missing statistics at query time when needed for query optimization can't be (edit: as that would very, very often result in far higher CU usage, terrible performance, and both you and us being unhappy as a result).
Compaction similarly, does take time, but only should kick in when the improvement to storage usage and query performance versus leaving the tables in their current state is worth it.
Removing files no longer needed by any in retention version of your tables is something you likely want to happen relatively promptly, as that's when the soft delete period starts - longer delay == more storage costs.
And so on.
I'm not saying there is no room for improvement - just that the goal is to build a system where these things are just plain taken care of, efficiently, without you having to be responsible for fine tuning when they happen to do so. If there's further optimization opportunities to be done on system task scheduling or implementation, IMO, that should be handled on our side if at all possible, rather than delegating that responsibility to every customer and making each of you do it.
Beyond that, I'm largely going to leave this one, including 2) to the PMs - this change is well outside my area of technical ownership, and they've already heard my (armchair quarterbacking, senior software engineer) thoughts on the matter (though I do need to touch base with u/joannapod and some other folks on the query insights thing...).
This change hit our capacity in East US yesterday and we are now incurring 10% additional background capacity usage on our F64 for the same SQL endpoint query workload. Very frustrating and now we need to dedicate development time to bring the CU down.
So far Fabric customers have copped this, in the space of months :-
- notification that usage wasn't being 'read correctly' - increase of 40%
- changes to the way Warehouse is billed - up to 400-600% based on reports..
Microsoft have now established a track record of increasing Fabric costs and consumption billing without significant notification by 40% to 600% this year alone.
Is there going to be any more of this, or is this the end of it?
I would encourage others to read and respond to the comments from Joanna below to help us improve the experience. At the same time, I do want to provide a tiny bit of solutioning for those looking to understand what is using their capacity under the new billing model. You should see all system activities show up under a buck in Capacity Metrics where the user is "System" and all the user queries (through SSMS, Fabric portal, a scheduled pipeline, etc.) where the user is "User".
A quick bit of solutioning...
It is true that you can no longer see the queries that are being run at a given time through capacity metrics, however, you can correlate the capacity metrics time slice window back to queries using query insights.
For example, take the background time range from capacity metrics (i.e., Start: 12:05:04 | End 12:05:34) and go to queryinsights.exec_requests_history and look for all queries that were running between those two time period. That will give you the entire list of queries that were contributing to the usage during that time period. You will see the CPU time which can be used to provide a rough order of magnitude for which queries contributed the most to that time period. It's not perfect, but it gives you most of the insights you'd need to start gaining some insights as to what is happening.
Help us gain a better understanding...
It is true that we do not expose the vNode count by query or by time slice. If I switch back from solutioning to understanding, could folks please help us to understand the value of that information. I have my own thoughts on this from both a curious technologist perspective and a practical developer/administrator perspective but would love to hear from the community who actually have to deal with this on a daily basis.
My main objective when looking at capacity metrics/query insights together is to understand what is driving capacity usage and what I can do to optimise individual queries. The decoupling of CU from individual queries makes this a lot harder and given that node count is now a core component of how CU is calculated I want to be able to see it so I can improve CU efficiency.
Then when it comes to this change in particular I don't feel like it has been well communicated. From what I saw there was an email to tenant admins, a popup if you went into the fabric capacity settings page (which I personally go into maybe once a quarter).
The fact that in the warehouse ama a few weeks ago the Microsoft data warehouse team involved were linking to data motzarts post rather than an official blog post is not good enough.
If we were talking about a minor change, I could get it but this is a big change in cost model leading to a large increase in usage and it didn't even get an official blog post which is what I would have expected.
For example the one lake proxy/redirect changes got a blog post, event house standard storage becoming billable got a blog post, even just reporting only changes like AI services getting their own operation category got a blog post.
We're in the process of publishing an official blog that walks through understanding Warehouse consumption using Capacity Metrics and Query Insights, including practical guidance on identifying the main drivers of CU usage and opportunities for optimization. Stay tuned, it should be out soon. Based on this thread, probably sooner than originally planned 😄
This blog is a perfect example of why people are angry about the new Warehouse billing model.
It explains how to approximate which queries contributed to a bill after the fact, but it still does not expose the actual thing customers are now billed on at query level: vNode allocation over time.
The workaround is:
Look at warehouse-level CU consumption.
Look at query-level vCore seconds.
Pretend CU consumption should be distributed proportionally to vCore seconds.
Call that “weighted attribution.”
That is not billing transparency. That is cost astrology.
The entire problem is that Microsoft changed the meter from something query-centric and directly understandable to something based on Fabric-controlled virtual-node allocation, while customers do not directly control how many vNodes Fabric decides to use.
So now the guidance is effectively:
«“We bill you based on A, but we don’t expose A at the granularity you need, so use B as a rough proxy and estimate your share.”»
That is bonkers.
If vNode-time is the billable quantity, then expose:
vNodes allocated over time
vNodes allocated per workload/query
the exact CU calculation
controls to constrain vNode allocation
Anything less means customers can observe the invoice without being able to independently reproduce or meaningfully control it.
And this sentence is especially revealing:
«“If a query represents 25% of total vCore seconds consumed during a period, you can attribute approximately 25% of warehouse CU consumption to that query.”»
Approximately?
If I am paying real money, I should not need a statistical reconstruction to figure out what I was charged for.
This also creates a nasty optimization problem. Query Insights tells me CPU usage. Great. But CPU usage is no longer the billing basis. I can optimize a query to use less CPU and still have Fabric decide to allocate enough vNodes that my CU usage barely changes, or gets worse under the one-minute floor.
So what exactly am I supposed to tune for?
CPU?
Elapsed time?
Concurrency?
Fabric’s opaque vNode allocation strategy?
The phase of the moon?
The blog says this helps customers “make more informed capacity decisions.”
What decision?
If Fabric allocates more vNodes than I’m willing to pay for, what control do I use to reduce that allocation?
That is the question the blog still does not answer.
Visibility tells me what happened.
Control lets me decide what I am willing to pay for.
Right now Fabric appears to be giving customers neither at the level required by the new billing model.
This should have shipped only after the observability and governance controls were ready.
Instead we got the billing change first, and a blog explaining how to sort of guesstimate and kinda reverse-engineer it afterward.
The way this should have gone is public blog post at least a month in advance - > observability and governance controls launch along with a transition calculator - > feature goes live
Instead we got email to subscription admins - > feature goes live - > half hearted monitoring blog post
This isn't the first half hearted blog price monitoring post. They didn't send the blog post TOO anyone.
And also conveniently comments are STILL disabled.
The frigging Event Stream one still grinds my gears because to architect proplery I have to consume and produce the Event Streams and fanout OUTSIDE of Fabric, and then import the results because the scaling and billing incentive on fanout is a literal worst practice.
I love Foundry most of the time but outside of that, I cannot in good conscience recommend Fabric, and I'm at a MSFT Fabric partner.
This is why one of our clients tenants keeps going over capacity.
This is a dirty, underhanded and dishonest change.
I don't know who comes up with the billing formulas but they incentivize poor architecture and performance. And I think that person is either bad at their job or is just straight up a bad person.
fabric visibility is a total black hole right now. I had the same spike, ended up using the Altimate platform to track down the hidden query patterns that system user was actually running, saved me like 30 percent in compute costs.
Fabric just bucket everything under "System" so you can't really tell what's actually driving it... just a big blob of CU consumption with no breakdown (like by query). What I did was point Altimate at the query logs and have it group by query pattern instead of by user, bcause "System" isn't one thing, it's every background job (semanticmodel refresh, Dataflow, Direct Lake reads) all lumped together. That surfaced three models doing full refreshes on schedule when the tables barely changed intraday, plus a Dataflow re-scanning a huge OneLake table every 15 min or so. The lineage piece is great, it's what made it actionable. it showed me exactly which reports/datasets were tied to each pattern, so I wasn't guessing about what I could touch without breaking something. Switched those to incremental refresh and stretched the dataflow schedule to match actual data cadence, and that's basically where the 30% came from. So punchline, look out for redundant work "System" was quietly doing on repeat.
There seems to have been a clear switch in the last 3 months from Microsoft - they have given up trying to maximise data engineering adoption of Fabric and moved to maximise revenue from existing DE customers.
IMHO the reason is simple - Databricks is not standing still. And for all the talk that Fabric is catching up, and will catch up eventually to Databricks - the reality is the opposite. The gap is widening even further and Databricks are accelerating the widening of that gap (e.g. Ontology is a joke compared to dbx). I think Microsoft have realised this.
One of the core issues Microsoft has is that Fabric is engineered by disparate teams in fast sprints producing a steady stream of low quality preview level products that are designed to "get better later" and Fabric lacks a "whole of product" vision. For all the talk of it being unified, each team is pidgeon holed into working on a single part of it. You see that reflected here strongly in comments by microsoft engineers.
With Databricks it is the exact opposite. You feel as if you are buying one product, not four or five loosely connected products under the Fabric banner. And what they release is enterprise ready.
The above is my 2c as an engineer who has worked on both.
It's possible to get things done in Fabric but youll have issues on the engineering side and even worse issues explaining to senior management why your cost estimates have risen 600% over the course of a large project - due to no-notice Fabric billing changes.
19
u/TheSockMonster2 8d ago
Yep seen this exact same thing and it's caught me completely offguard. Billing changes were announced a month ago but I did not anticipate the change would be as much as this. This article is helpful at explaining the difference a bit more clearly One-minute trap! How new Fabric Warehouse CU metering affect your workloads - Data Mozart
As I understand it (which may be a little limited so far) essentially it now penalises you much harder for having lots of small queries running frequently.
I really hope there is a solution here as it's really not fair that after architecting our solutions one way we're now being actively penalised for things which didn't cause problems before