Sort spills to tempdb due to varchar(max)
There are going to be several possible workarounds here.

You can manually adjust the memory grant, though I probably wouldn't go that route.

You can also use a CTE and TOP to push the sort lower, before grabbing the max length column. It will look something like below. 

WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID


Proof-of-concept dbfiddle here.
Sample data would still be appreciated!

If you want to read an excellent analysis by Paul White, read here.