Need help to optimize this slow performance Query
we need to know more information as Justin mentioned in the comment to answer you better, however meanwhile try this method, it improve performance a little bit better:
with cte_ as (
select ResourceId
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Subfields else null end) as sub1
    , max (case when rfvAUT.Tag IN ('100', '110', '700', '800', '920')  then Normalized else null end)  as Norm1
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Subfields else null end) as sub2
    , max (case when rfvAUT.Tag IN ('130', '222', '242', '245', '246', '730', '830') then Normalized else null end)  as Norm2
FROM 
    ResourceField rfAUT 
    INNER JOIN ResourceFieldValue rfvAUT 
        ON rfAUT.ResourceFieldValueId = rfvAUT.Id 
    group by 
        rfvAUT.ResourceId 
)


SELECT DISTINCT
    h.ResourceId, 
    h.Id AS HoldingId, 
    h.Barcode AS [BAR], 
    h.Call AS [CAL], 
    h.ResourceCategoryId as CategoryId,
    cte_.norm1 fAUT,
    cte_.sub1 sf_AUT,
    cte_.sub2 sf_TIT
FROM ResourceHolding h
left join cte_ 
 on cte_.ResourceId = h.ResourceId
WHERE h.ResourceStatusId != 7
ORDER BY fAUT asc
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY