topic Large deviation in CU consumption - same sql query in Data Warehouse

Large deviation in CU consumption - same sql query

JDT — Tue, 10 Sep 2024 13:01:51 GMT

Hi,

we are currently observing that the same SQL queries (against the same data) show strong fluctuations in CU consumption.

Same behavior with the Lakehouse SQL Endpoint.
These spikes occur about every 3rd query.

Nothing else was executed while the query was running.

Do I have any way to see why ? Or is it just “bad luck” with the query plan or something?

Re: Large deviation in CU consumption - same sql query

Anonymous — Wed, 11 Sep 2024 01:41:55 GMT

HI JDT,

According to your description, I think they may related to the capacity policy and throttling smooth.

Fabric is designed to deliver lightning-fast performance to its customers by allowing operations to access more capacity unit (CU) resources than are allocated to the capacity. Tasks that might take several minutes to complete on other platforms can be finished in mere seconds on Fabric. To avoid penalizing users when operational loads surge, Fabric smooths or averages the CU usage of an operation over a minimum of five minutes, and even longer for high CU usage but short runtime requests. This behavior ensures you can enjoy consistently fast performance without experiencing throttling.

You can take a look at the following document to know more about these:

Understand your Fabric capacity throttling - Microsoft Fabric | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Large deviation in CU consumption - same sql query

JDT — Wed, 11 Sep 2024 11:53:01 GMT

Our capacity was utilized to around 35% and the queries consumed between 0.15% and 0.45% of the base capacity.

I dont think throttling is the issue here.