Memory Usage Over Capacity error in Stream Analytics

Hi @h85824

Azure Stream Analytics query is encountering memory overcapacity issues, likely due to inefficient query design, large window sizes, and redundant joins.

key issues and practical solutions to optimize performance and reduce memory usage.

A 60-minute window accumulates too much data, increasing memory usage. Reduce it to 15 or 30 minutes to improve efficiency

The condition ON OneEvent.EndOfWindow = OneEvent.EndOfWindow is always true, making the join ineffective.Use an actual timestamp column from prodBlobReference.

Checking missing events over 60 hours (DATEDIFF(hh, ..., 60)) leads to high memory consumption. Reduce it to 12 hours to optimize memory and processing the query.

prodBlobReference is joined three times in different queries, increasing redundancy. Filter first using WHERE instead of filtering inside the JOIN.

Improved Query:

WITH OneEvent AS 
(
    SELECT COUNT(*) AS eventCount, System.Timestamp AS EndOfWindow
    FROM [inEventHub] TIMESTAMP BY EventEnqueuedUtcTime
    GROUP BY TumblingWindow(mi, 15)  -- Efficient 15-min window
),
AllReferenceDevices AS 
(
    -- CROSS JOIN to ensure all devices are considered
    SELECT b.machine_id AS mac_address, OneEvent.EndOfWindow
    FROM OneEvent 
    CROSS JOIN [prodBlobReference] b
),
DeviceConnectivityErrorDetection AS
(
    SELECT 'DeviceConnectivityErrorDetected' AS EventType, 
           AllReferenceDevices.mac_address AS mac_address, 
           AllReferenceDevices.EndOfWindow AS LastEventEnqueuedUtcTime
    FROM AllReferenceDevices
    WHERE NOT EXISTS 
    (
        SELECT 1 
        FROM [inEventHub] EH TIMESTAMP BY EventEnqueuedUtcTime
        WHERE EH.mac_address = AllReferenceDevices.mac_address
        AND ABS(DATEDIFF(hh, EH.EventEnqueuedUtcTime, AllReferenceDevices.EndOfWindow)) <= 12
    )
)
-- Consolidated joins for efficiency
SELECT EH.mac_address, EH.res, EH.parms
INTO [queueoutput1]
FROM [inEventHub] EH TIMESTAMP BY EventEnqueuedUtcTime
JOIN [prodBlobReference] b 
ON EH.mac_address = b.machine_id
WHERE b.queue_name = 'prod-queue1'
SELECT EH.mac_address, EH.res, EH.parms
INTO [queueoutput2]
FROM [inEventHub] EH TIMESTAMP BY EventEnqueuedUtcTime
JOIN [prodBlobReference] b 
ON EH.mac_address = b.machine_id
WHERE b.queue_name = 'prod-queue2'
SELECT EH.mac_address, EH.res, EH.parms
INTO [queueoutput3]
FROM [inEventHub] EH TIMESTAMP BY EventEnqueuedUtcTime
JOIN [prodBlobReference] b 
ON EH.mac_address = b.machine_id
WHERE b.queue_name = 'prod-queue3'
-- Store detected connectivity errors
SELECT * 
INTO [queueAnomalyOutput]
FROM DeviceConnectivityErrorDetection

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.