Forum Discussion

selcuk12's avatar
selcuk12
Frequent Visitor
9 months ago
Solved

Fabric Warehouse CU increase issue

As seen in the image below, our Fabric capacity consumption (CU) has started to increase rapidly. As the chart shows, the component consuming the most CU is the Warehouse. However, inside the Fabric ...
data engineering
  • nielsvdc's avatar
    nielsvdc
    9 months ago

    Hi selcuk12,

     

    When you hover over the green CU bar of your warehouse, you will see a tooltip like below that shows you what is causing the most utilization in your warehouse, just to make sure. In the example below you see someone is using Copilot on the warehouse that causes the most utilization and not queries.

    But when you determined that queries that queries causes the most utilization in you warehouse, you can use one of these queryinsights queries to use in your investigation.

    -- Historical query runs
SELECT TOP 100 *
FROM queryinsights.exec_requests_history
ORDER BY end_time DESC;

-- Frequently used queries
SELECT TOP 100 *
FROM queryinsights.frequently_run_queries
ORDER BY avg_total_elapsed_time_ms DESC;

-- Long running queries
SELECT TOP 100 *
FROM queryinsights.long_running_queries
ORDER BY last_run_total_elapsed_time_ms DESC;

     

    You can also install the Fabric Chargeback app. In the Utilization (CU) details visual, select you warehouse and then click the Item detail button at the top. In the filters pane on the left you can set some filters and see which user is causing the most utilization with warehouse queries over your warehouse.

     

    Hope this helps. If so, please give a Kudos 👍 or mark as Accepted Solution ✔️.

selcuk12's avatar
selcuk12
Frequent Visitor
9 months ago

Thanks.

I actually need the CU value. Which query consumes how much?

I wrote this query. Does it work? Is the CU calculation correct or incorrect?

 

-- yesterday (DATE)
DECLARE @Yesterday DATE = DATEADD(DAY, -1, CAST(GETDATE() AS DATE));

-- DATETIME2
DECLARE @YesterdayDT DATETIME2 = CAST(@Yesterday AS DATETIME2);

-- Time range
DECLARE @StartTime DATETIME2 = DATEADD(HOUR, 15, @YesterdayDT);  -- 05:00
DECLARE @EndTime   DATETIME2 = DATEADD(HOUR, 19, @YesterdayDT);  -- 19:00

SELECT
    distributed_statement_id,
    database_name,
    login_name,
    program_name,
    status,
    session_id,
    submit_time,
    start_time,
    end_time,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    data_scanned_remote_storage_mb,
    data_scanned_memory_mb,
    data_scanned_disk_mb,

    -- Toplam tarama
    (ISNULL(data_scanned_remote_storage_mb,0)
     + ISNULL(data_scanned_memory_mb,0)
     + ISNULL(data_scanned_disk_mb,0)
    ) AS total_data_scanned_mb,

    (ISNULL(data_scanned_remote_storage_mb,0)
     + ISNULL(data_scanned_memory_mb,0)
     + ISNULL(data_scanned_disk_mb,0)
    ) / 1024.0 AS total_data_scanned_gb,

    -- estimated CU
    (
        ISNULL(allocated_cpu_time_ms,0) / 1000.0 * 0.3
        +
        (
            ISNULL(data_scanned_remote_storage_mb,0)
          + ISNULL(data_scanned_memory_mb,0)
          + ISNULL(data_scanned_disk_mb,0)
        ) / 1024.0 * 0.7
    ) AS estimated_cu_cost,

    command

FROM queryinsights.exec_requests_history
WHERE start_time >= @StartTime
  AND start_time <  @EndTime
  AND end_time IS NOT NULL
ORDER BY estimated_cu_cost ASC;
nielsvdc's avatar
nielsvdc
Super User
9 months ago

You cannot accurately calculate the CU cost using exec_requests_history, but your query gives you an indaction about which query weighs the most. When asking my favorite AI, it generated a query simular like yours. After follow-up questions, it generated the query below. But sort of the same result, as it show some estimated weight score and not the exact CU usage.

 

WITH q AS (
    SELECT
        distributed_statement_id,
        statement_type,
        command,
        login_name,
        start_time,
        end_time,
        status,
        total_elapsed_time_ms,
        allocated_cpu_time_ms,
        data_scanned_remote_storage_mb,
        data_scanned_memory_mb,
        data_scanned_disk_mb,
        result_cache_hit,
        is_distributed
    FROM queryinsights.exec_requests_history
    WHERE
        start_time >= DATETRUNC(DAY, DATEADD(DAY, -1, SYSUTCDATETIME()))
        AND end_time < DATETRUNC(DAY, DATEADD(DAY, 0, SYSUTCDATETIME()))
        AND status = 'Succeeded'
),
norm AS (
    SELECT
        *,
        -- CPU converted to “units”
        allocated_cpu_time_ms / 1000.0 AS cpu_units,

        -- elapsed time converted to seconds
        total_elapsed_time_ms / 1000.0 AS time_units,

        -- total amount of scanned data (MB → units)
        (
            ISNULL(data_scanned_remote_storage_mb, 0) +
            ISNULL(data_scanned_memory_mb, 0) +
            ISNULL(data_scanned_disk_mb, 0)
        ) / 100.0 AS scan_units,

        -- distributed queries cost more compute
        CASE WHEN is_distributed = 1 THEN 1.25 ELSE 1.00 END AS dist_factor,

        -- cache hits reduce compute cost significantly
        CASE result_cache_hit
            WHEN 2 THEN 0.15   -- full hit → almost no real compute
            WHEN 1 THEN 0.60   -- cache write → lower cost
            ELSE 1.00          -- no cache
        END AS cache_factor
    FROM q
),
score AS (
    SELECT
        *,
        -- weight model: CPU most important, then scan cost, then elapsed time
        (
            cpu_units  * 0.55 +
            scan_units * 0.30 +
            time_units * 0.15
        )
        * dist_factor
        * cache_factor AS estimated_compute_score
    FROM norm
)
SELECT
    distributed_statement_id,
    login_name,
    statement_type,
    command,
    start_time,
    end_time,
    total_elapsed_time_ms,
    allocated_cpu_time_ms,
    data_scanned_remote_storage_mb,
    data_scanned_memory_mb,
    data_scanned_disk_mb,
    result_cache_hit,
    is_distributed,
    estimated_compute_score
FROM score
ORDER BY estimated_compute_score DESC;