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

Tornikoski, Veli-Matti 0

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'

ShaikMaheer-MSFT 37,971 Reputation points Microsoft Employee

2023-11-30T16:53:33.92+00:00

Hi Tornikoski, Veli-Matti,

Thank you for posting query in Microsoft Q&A Platform.

It is possible to use multiple windows in Azure Stream Analytics, but it is important to be aware of the memory usage and processing requirements of each window.

In your case, it sounds like using both the 3-hour and 1-day windows is causing high memory usage in the Stream Analytics job. This is likely because the job is processing a large amount of data over multiple time periods, which requires a significant amount of memory.

To reduce the memory usage of the job, you can try reducing the size of the windows or increasing the frequency of the output. For example, you can try using a 1-hour window instead of a 3-hour window, or outputting the results every 5 minutes instead of every hour.

Another option is to split the job into multiple smaller jobs, each processing a subset of the data. This can help reduce the memory usage and processing requirements of each job.

It is also important to ensure that the Stream Analytics job is running on a sufficiently powerful machine with enough memory and processing power to handle the data. You can check the resource utilization of the job in the Azure portal to ensure that it is not exceeding the limits of the machine.

I hope this helps! Let me know if you have any further questions.
Tornikoski, Veli-Matti 0 Reputation points

2023-12-08T10:59:55.67+00:00
Thank You for these considerations.

you said that:

"This is likely because the job is processing a large amount of data over multiple time periods, which requires a significant amount of memory."

I get daily 72 events matching the below where clause. Is that a large amount of data to Stream analytics, really?

Or does the windows grouping ignore where clause, and first group by all incoming events, and afterwards apply where, which does not make sense.

Anyhow, it still is a mystery what is happening.

packet.type LIKE 'digmat/sensor%'