Using multiple, mixed length Windows in Stream analytics query results in high (100%) SU memory utlization

Tornikoski, Veli-Matti 0 Reputation points
2023-11-29T11:07:26.6166667+00:00

I want to use Windows to collect both 3-hour and 1 day max, min, average and count of certain device messages arriving to iot hub. If I use both windows, as in code snippet, SU memory usage goes to 100%. If I use only one Window (does not matter which one), SU memory usage is about 20%.

Why is that, can't we use multiple Windows like that? For additional information, there are only about 10 messages hourly arriving at IoT Hub, so should not be heavy usage.

TumblingWindowResults AS (SELECT

    System.Timestamp() as date,

    packet.DevEUI as SensorID,

    AVG(packet.value) AS temp_water,

    MAX(packet.value) AS temp_water_max,

    MIN(packet.value) AS temp_water_min,

    COUNT(*) AS samples_count,

    AVG(packet.LrrRSSI) as rssi,

    System.Window().Id

FROM

    Telemetry

WHERE

    packet IS NOT NULL

    AND packet.type LIKE 'digmat/sensor%'

    AND packet.payload_hex IS NOT NULL    

GROUP BY 

    packet.DevEUI,

    Windows(     

        Window('1 day',  TumblingWindow(day, 1)),

        Window('3 hour', TumblingWindow(hour, 3)))
SELECT 
    DATEADD(DAY, -1,date) as date, SensorID, temp_water, temp_water_max, temp_water_min, samples_count, rssi
INTO 
    averagesday
FROM 
    TumblingWindowResults
WHERE
    TumblingWindowResults.Id = '1 day'

SELECT 
    date, SensorID, temp_water, temp_water_max, temp_water_min, samples_count, rssi
INTO 
    averageshour
FROM 
    TumblingWindowResults
WHERE
    TumblingWindowResults.Id = '3 h'
Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
343 questions
{count} votes