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 Warehouse we are unable to identify which query is causing this consumption. How can we determine this? The Query Activity page doesn’t allow us to specify a time range.

In summary, how can I identify which query, dataflow, pipeline, or other process is causing the Warehouse to consume CU?

 

 

 

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 ✔️.

3 Replies

  • nielsvdc's avatar
    nielsvdc
    Super User
    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;