sorry too busy at work.
Regarding plan - can you get an ACTUAL explain plan? the one supplied is just an estimated one which does not necessarily reflect the actual execution.
but assuming it does - if this is the query the users are complaining about then, on my opinion, the reason for it is the volume of data - 600k rows (estimated 26 GB data) being returned by that query which in any situation will be a slow process.
but 18 tables being processed with 6 sort operation s (5 are from select distinct - should be looked at) and to the string_agg (4) with a nvarchar(max) output and all of it is contributing to the lack of parallel processing as well as the "slowness".
all of this may also be leading to high memory requests that can't be granted immediately causing some queries to have to wait until there is memory available - the following SQL will give you those details at that point in time - I normally have this inserting into a table running on a SQL Agent job every 10 seconds (on some of my badly behaved servers - MS CRM mainly) so I have both a history of it and so I can then look at the bad queries at a later stage.
main fields on this is the memory requested, memory used and wait_order/wait_time
, query_text = t.text
, input_buffer = ib.event_info
, query_plan_xml = qp.query_plan
, request_row_count = r.row_count
, session_row_count = s.row_count
--Session history and status
, session_status = s.[status]
, request_status = r.status
--Session connection information
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests AS r
ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg
ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id]
, NULL) AS ib
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
WHERE mg.granted_memory_kb > 10000 -- greater than 10 MB requests
ORDER BY mg.granted_memory_kb desc
, mg.requested_memory_kb desc;
for the parallel there is a trick that may or not work - and may or not improve performance so TEST TEST TEST.
This consists in adding a table with a columnstore index to the main query - with a left outer join so it won't affect the results.
for this particular query you would change the view being used and add the table to the mix.
create table dbo.dummy
create clustered columnstore index cci_dummy on dbo.dummy
no records on the table
then the joins
from main tables
left outer join dbo.dummy dmm
on dmm.cfield = main_table.varcharfield
what the above trick may do is to give a push to the QE to use batch mode processing for some tables/joins and a bit of parallel processing.
doesn't always work neither does it always improve performance (both query and overall server) but its easy to test and see impact on both.