Я бы начал с того, что убрал все подзапросы, джойнил таблицы напрямую. Скорее всего у этого не будет никакого результата, но читать будет проще.
Второе, что нужно сделать - это избавиться от этого:
join (select DocumentRef, DocumentNodeStaticGUIDRef, DocumentNodeValue from DocumentNode) dn2 on dn2.DocumentRef = (select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value]))
and dn1.DocumentNodeStaticGUIDRef = dn2.DocumentNodeStaticGUIDRef
where
(select T.[value].value ('.', 'int') from DocumentSettingXML.nodes('/DocumentSetting/PreviousDocumentRef') as T([value])) is not null
Попробуйте выкинуть это в нормальную временную таблицу (не табличную переменную) и делать джойн с ней
Nested Loops далеко не всегда плохо, проблема с ним в этом месте в том, что sql server думает, что вернётся 70 тыщ строк (Estimated Number of Rows), а возвращается (Actual Number of Rows) на несколько порядков больше. Дайте ему временную таблицу по которой он сможет построить статистику и он, скорее всего, сам построит приемлемый план.
Если @StartDate и @EndDate - это переменные, а не параметры SP, попробуйте выполнить запрос в том виде, в котором он есть, но явно подсунув туда даты, без переменных. Если это хранимая процедура и это её параметры, попробуйте выполнить её после sp_recompile. Этот абзац актуален только в том случае, если запрос не всегда работает медленно.
Ну и от distinct надо избавляться, да.
