Orphan data in the TotalAgility Reporting Staging database

Question / Problem: 

Is it possible to determine if there are orphan messages stuck in the wsa_messages table?

Answer / Solution: 

One of the elements that can contribute to a backlog of data in the TotalAgility_Reporting_Staging, or slower processing of a backlog, is orphan data in the wsa_messages table.  In KTA 7.5.0.20 and earlier orphan data may prevent valid data from processing, or prevent it from processing at a normal throughput. 

Orphan records may still exist in KTA 7.5.0.21+ and KTA 7.6+, but improvements allow continued processing to occur even when orphan data is present. 

As part of troubleshooting a reporting backlog issue, it may be useful to quantify any orphan data present.  To do so, run the following diagnostic query on the TotalAgility_Reporting_Staging database to show the number of orphan messages, of which there are a few different types.

WITH MatchedMessages 
AS (
    SELECT ISNULL(ba.batch_action_id,be.batch_action_id) AS batch_action_id, ExpectedTotalMsg,
        ISNULL(EditMsgCount,0) AS EditMsgCount, ISNULL(ActionMsgCount,0) AS ActionMsgCount
    FROM (
        SELECT batch_action_id, COUNT(*) AS ActionMsgCount, SUM(messages_count) AS ExpectedTotalMsg
        FROM wsa_messages
        WHERE function_name='KCBatchAction'
        GROUP BY batch_action_id
    ) AS ba
    FULL OUTER JOIN (
        SELECT batch_action_id, COUNT(*) AS EditMsgCount
        FROM wsa_messages
        WHERE function_name='BatchEdit'
        GROUP BY batch_action_id
    ) AS be ON be.batch_action_id=ba.batch_action_id
)
SELECT MatchStatus, COUNT(*) AS MatchTypeCount, SUM(TotalMessages) AS TotalMessages
FROM (
    SELECT batch_action_id, EditMsgCount+ActionMsgCount AS TotalMessages,
    CASE 
        WHEN ActionMsgCount>1 THEN 'NotExpected-MultipleActionSameId'
        WHEN ActionMsgCount=1 AND (ExpectedTotalMsg IS NULL OR ExpectedTotalMsg <2) THEN 'NotExpected-ActionNotExpectingEditMessages'
        WHEN ActionMsgCount=0 THEN 'Orphan-ActionMissing'
        WHEN ExpectedTotalMsg>EditMsgCount+ActionMsgCount THEN 'Orphan-LessEditMessagesThanExpected'
        WHEN ExpectedTotalMsg=EditMsgCount+ActionMsgCount THEN 'OK-ExpectedMatch'
        ELSE 'Unknown'
    END AS MatchStatus
    FROM MatchedMessages
) AS ms
GROUP BY MatchStatus

Note that this query will run more quickly if the reporting services are stopped first.  Otherwise the normal processing of the reporting services will result in locking on the wsa_messages table and cause the query to take much longer.  Additionally the amount of time the query takes depends on the total number of records in the wsa_messages table.