Can you show us what the query/execution plan from this looks like:

SELECT pat2.TIUDocumentSID, icd.ICDCode, icd.DiagnosisText

into #d

FROM [VHA19\VHASLCOlearT].PatientMap_1K_patients AS pat2

INNER JOIN Src.Visit_IDs AS vis ON pat2.Sta3n = vis.Sta3n AND pat2.VisitIEN = vis.VisitIEN

INNER JOIN Src.VDiagnosis AS diag ON vis.VisitSID = diag.VisitSID

INNER JOIN [VHA20\VHAPUGHAMMOK].ICD AS icd

ON diag.ICDSID = icd.ICDSID

WITH main AS (

SELECT pat1.TIUDocumentSID, pat1.ScrSSN, pat1.Sta3n, doc.ReferenceDate, doc.VHAEnterpriseStandardTitle, doc.ReportText, doc.TIUDocumentIEN

FROM [VHA19\VHASLCOlearT].PatientMap_1K_patients AS pat1

JOIN [Src].TIUDocument_8925_New AS doc ON doc.TIUDocumentSID = pat1.TIUDocumentSID

),

d AS (

Select * From #d

)

SELECT main.TIUDocumentSID, main.ScrSSN, main.Sta3n, main.ReferenceDate, main.VHAEnterpriseStandardTitle, main.ReportText,

ad.TIUDocumentSID AS TIUAddendumSID, ad.ReferenceDate AS AddendumReferenceDate, ad.ReportText AS AddendumReportText,

d.ICDCode, d.DiagnosisText

FROM main

LEFT OUTER JOIN Src.TIUDocument_8925_New AS ad ON main.Sta3n = ad.Sta3n AND main.TIUDocumentIEN = ad.ParentIEN

LEFT OUTER JOIN d ON main.TIUDocumentSID = d.TIUDocumentSID

WHERE main.ScrSSN >= 0

AND main.ScrSSN < 1000000000

ORDER BY main.TIUDocumentSID, TIUAddendumSID, d.ICDCode

Thanks,