In this particular case, as suggested by several comments, the issue was the NVARCHAR(MAX) columns. After shortening many of them and pulling a couple out to other tables, the basic query is running orders of magnitude faster.

Also helping, there's another table that needs to have an NVARCHAR(MAX) column for most purposes, but there are a couple for which it's not needed; in those couple of cases, projecting the object down to not include that data is also helping tremendously.