Operator causing tempdb to spill data

I have a below query, which is causing Sort operator to spill data in tempdb and most possibly the reason of causing performance issue I'm facing.

SELECT 
    P_All.Status, P_All.Date, P_All.Year, P_All.Amount, P_All.ID,
    P.ID, 
    A.ID, A.Type_ID, A.Date, A.Amount,
    R.Year, R.Amount, R.Balance, R.Title, R.TOTAL, R.TYPE, R.Date, R.ID,
    O.TYPE, O.Name, O.CITY, 
    D.City, D.Code, D.Zip, D.Address,
    S.Description,
 FROM   
    Pmnt P INNER JOIN Rqst R ON P.Rqst_ID=R.ID
    INNER JOIN Org O ON R.Org_ID=O.ID
    INNER JOIN Pmnt P_All ON R.ID=P_All.Rqst_ID
    LEFT OUTER JOIN Actvty A ON R.ID=A.Rqst_ID
    INNER JOIN Addrs D ON O.Addrs_ID=D.ID
    INNER JOIN Sts S ON O.Sts_ID=Sts.ID
 WHERE
    R.TYPE=N'INITIAL' AND R.Date<{ts '2021-08-13 00:00:00'}
 ORDER BY
    O.TYPE, R.ID

I have tried a few things which I found on google but none of them worked in getting rid of this warning.

Below things I've tried so far:

Updated statistics using Exec sp_updatestats as well as UPDATE STATISTICS Org with fullscan (for all the 6 tables above).
Created indexes involving all the columns involved in this query for all the 6 tables. (I'm very new to indexes)

Can anyone help with what I can do to get rid of this warning? Please feel free to let me know if you need something else from me.

Note: I've found one probable solution to update the compatibility level of the db to 150 in this article but I'm afraid if changing it might mess up something. So, for now I'm looking for some solution related to indexes or optimization of the query.

Actual SQL query execution plan - https://www.brentozar.com/pastetheplan/?id=HJxdkN4et

Solution

you really don't need all those parentheses in your FROM

That's actually a clue. It suggests that the original author was a noob using a graphical query designer, so you should be suspicious that this query has issues, and it turns out it does.

Here are the query wait and execution stats from the plan:

    <WaitStats>
      <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="18" WaitCount="7171" />
      <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="61" WaitCount="64279" />
      <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="358" WaitCount="3705" />
      <Wait WaitType="LATCH_EX" WaitTimeMs="810" WaitCount="149" />
      <Wait WaitType="IO_COMPLETION" WaitTimeMs="38588" WaitCount="10990" />
      <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="117148" WaitCount="1530" />
      <Wait WaitType="CXPACKET" WaitTimeMs="558490" WaitCount="39296" />
    </WaitStats>
    <QueryTimeStats CpuTime="54137" ElapsedTime="156188" />

Of the 156sec of elapsed time 117sec is waiting to send 4.2 million rows to the client. Activity has only 200K rows and the rest of the tables are small, under 100k rows.

But it's joining across multiple 1-many relationships which blows up the result size, and makes the final result size difficult to predict (causing the spill).

So this is a case for rethinking this query. Perhaps you can aggregate it on the server instead of sending all the results to the client, or break it up into multiple queries so you don't have to sort and send the cartesian product of Actvty and Pmnt for each Rqst. If a Rqst has 50 Actvtys and 4 Pmnts, the query will return 200 rows for that Rqst.

Also it's joining Pmnt multiple times for no apparent reason, which doesn't help.