Query tuning help needed (Hash Match and Table Scans) I need help optimizing the following query (returning ~8k rows):

SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
                                FROM (
                                SELECT A.sys_id, AppSvr.name
                                FROM GTS_DataStage.SN.cmdb_ci_appl A
                                LEFT JOIN GTS_DataStage.SN.cmdb_rel_ci X 
                                    ON A.sys_id = X.parent
                                LEFT JOIN GTS_DataStage.SN.cmdb_ci AppSvr 
                                    ON X.child = AppSvr.sys_id 
                                    AND AppSvr.dv_sys_class_name 
                                      IN ('UNIX Server', 'Windows Server', 
                                      'IBM Mainframe', 'Application Server')
                                WHERE A.dv_sys_class_name = 'Application' 
                                 AND A.dv_u_used_for = 'Production' 
                                 AND A.dv_operational_status = 'Deployed') T
                                WHERE A.sys_id = T.sys_id
                                FOR XML PATH('')), 3, 4000) AS NVARCHAR(4000))
FROM GTS_DataStage.SN.cmdb_ci_appl A
WHERE A.dv_sys_class_name = 'Application' AND A.dv_u_used_for = 'Production' 
    AND A.dv_operational_status = 'Deployed'


These tables are on the DataStage and I am not allowed to use indexes. Total rowcounts are: 
cmdb_ci_appl: ~20k; cmdb_rel_ci: ~1200k; cmdb_ci: ~800k

The following image shows a section of the execution plan (I can upload the sqlplan file if needed but there's not much else to see):



Any help is appreciated.
Show the DBA those Eager Index Spools in the query plan. SQL Server is so desperate for supporting indexes here, it is creating its own temporary ones on every execution!

There is always more to see in the sqlplan file, so please do upload it. A statistics-only copy of the tables in question (or table and index definitions at the very least) would also be useful if you want more than general advice).

This answer was originally a comment on your question. I'll update it if a query plan or DDL is provided.